5

It seems that in Postgres 9.2 this syntax

SELECT <table name>
FROM <table name>

Returns rows with all columns aggregated as pseudo-CSV, like so:

(1,test,48,,48,,"2016-10-29 00:47:09.348986+00","2016-10-29 00:47:09.348986+00",,1,9,1,1,,1,0,"",,"")
(6,subject,0,,,,"2016-10-30 00:54:38.203023+00","2016-10-30 00:54:38.203023+00",,1,10,1,3,,1,0,content,,)

I'm trying to find the relevant documentation for this behavior. So far I've looked in :

But I can't seem to find any references to it.

8

For each table that is created, Postgres creates a corresponding composite type:

Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.

When you use the table name in the select list, you are essentially using a single column of that composite type with several fields as part of the record (this is a good example on the difference between row and record and column and field).

The "CSV style" just happens to be the default text representation of a composite type.

A row constructor is closely related to this.

This is also the reason why select (a,b,c) from some_table is something different than select a,b,c from some_table in Postgres.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.