PostgreSQL SELECT请求返回的原始数据格式是什么？

Great question—this is one of those under-the-hood details that's easy to overlook since most tools and drivers handle the parsing for you. Let's break it down:

First, Clarification on the "Array" Mention in Docs #

When you see references to "returning arrays" in PostgreSQL docs, that's almost certainly referring to array-type columns (e.g., SELECT ARRAY[1,2,3] or a column defined as integer[]), not the overall result set structure. The raw result of a SELECT isn't a generic "array"—it's structured data sent via PostgreSQL's custom client-server protocols.

The Two Core Raw Formats #

PostgreSQL communicates with clients using two primary protocols, neither of which are JSON/XML (those are output formats you explicitly request or tools generate):

1. Text Protocol #

This is the older, human-readable option. The server converts every value in your result set to a string following PostgreSQL-specific conventions:

• Integers become plain numeric strings (e.g., 123 → "123").
• Booleans use t/f instead of true/false.
• Dates follow ISO 8601 but with PostgreSQL's strict formatting (e.g., 2024-05-20).
• Arrays are sent as string literals like "{1,2,3}" for integer arrays.

Clients receive these strings separated by delimiters (like | in some cases) and then parse them back into native types. Tools like psql use this protocol by default for interactive use, then format the strings into tables for readability.

2. Binary Protocol #

This is the default for most drivers (e.g., psycopg2, JDBC) because it's faster and avoids string conversion overhead. The server sends values in their native binary representation as defined by PostgreSQL:

• Integers are sent as 4-byte or 8-byte binary numbers (depending on type).
• Dates are stored as 4-byte integers representing days since PostgreSQL's epoch (2000-01-01).
• Arrays include metadata (element type, dimensions, length) followed by each element's binary value.

Is This Format PostgreSQL-Exclusive? #

Yes, for the most part:

• The binary protocol is entirely PostgreSQL-specific. Every data type's binary encoding is defined by PostgreSQL, and no other database uses the same format.
• The text protocol uses PostgreSQL-specific string conventions (like t/f for booleans) that don't match SQL standards or other databases (e.g., MySQL uses 1/0 for booleans). While some tools can parse these strings, they're not universal.

Why You Need to Parse to JSON/XML #

When you want JSON or XML output, you're asking PostgreSQL (or your client) to convert its raw protocol data into a standard format. For example:

• Use row_to_json() in your query to get JSON directly from the server: SELECT row_to_json(users) FROM users LIMIT 1;
• Use client-side libraries to convert the parsed native types (e.g., Python lists, Java objects) into JSON/XML.

The raw protocol data isn't meant to be used directly by your application—drivers handle translating it into your programming language's native types, and you can then convert those to whatever format you need.

内容的提问来源于stack exchange，提问作者The Once-ler