Where is the postgres sql 'cast a tuple' idiom documented?

Question

I found an expression in a postgres.org email, which seems useful, but whose documentation I've not been able to find.

select t.*::text from t

Here, the ::text cast seems to apply to the each of the columns, and the resulting output as displayed by psql is a bit odd. Perhaps someone could educate guys like me and unravel that mystery.

Edit: Curiously, the section 4.2.9. Type Casts makes no reference to this syntax

Edit: Finally! Related documentation found at 4.2.13. Row Constructors

Answer 1

I can describe what's happening. Consider this syntax:

select (1, 2)

This returns a record (or tuple) with two columns. You can convert the tuple to text using either cast() or :::

select (1, 2)::text

The same thing occurs with the t.*. It is interpreted as:

select (t.*)::text

You'll get the same result with:

select cast(t.* as text)

Answer 2

Just use the name (or alias) of the table t directly:

SELECT t::text FROM t

t.* would be used to decompose the row, but since you cast the whole row to text, the step is redundant noise.

The cast is possible because everything can be cast to text in Postgres. (There has to be a text representation for input / output.)

You get a text representation of the row either way. I.e. the output is the valid string literal, which you can cast back to the registered row type, like:

SELECT '(123,"some text",,"2017-01-03 02:27:27.930164+01")'::t

t being the name of a table or (materialized) view (visible in the current search_path or you have to schema-qualify) or any other registered (row) type.

This works with table names out of the box because Postgres registers a row type for every table you create.

Note, neither the t.* notation nor the cast back is possible for anonymous records (like ROW(1,2) or just (1,2) for short). There is no information about their structure in the system catalogs.

Related:

• How to concatenate all results from table row?