Documentation for selecting table name returning csv of the row

It seems that in Postgres 9.2 this syntax

SELECT 
FROM 

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 :

• SELECT

• Select Lists

• Value Expressions

• Aggregate Functions

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

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.

StackExchange Database Administrators Q#155713, answer score: 8