Search code examples
sqlpostgresqltypesrow

What is a row constructor used for?

In PostgreSQL, what is the ROW() function used for?

Specifically what is the difference between

SELECT ROW(t.f1, t.f2, 42) FROM t;

where f1 is of type int, f2 is of type text

and

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
Solution

  • You are confusing levels of abstraction. As other answers already point out, CREATE TYPE only registers a (composite / row) type in the system. While a ROW constructor actually returns a row.

    A row type created with the ROW constructor does not preserve column names, which becomes evident when you try to convert the row to JSON.

    While being at it, ROW is just a noise word most of the time. The manual:

    The key word ROW is optional when there is more than one expression in the list.

    Demo:

    SELECT t                              AS r1, row_to_json(t)                           AS j1
     , ROW(1, 'x', numeric '42.1')    AS r2, row_to_json(ROW(1, 'x', numeric '42.1')) AS j2
     ,    (1, 'x', numeric '42.1')    AS r3, row_to_json(   (1, 'x', numeric '42.1')) AS j3
     ,    (1, 'x', '42.1')::myrowtype AS r4, row_to_json((1, 'x', '42.1')::myrowtype) AS j4
FROM  (SELECT 1, 'x', numeric '42.1') t;

    db<>fiddle here
    Old sqlfiddle

    r1 and j1 preserve original column names.
    r2 and j2 do not.
    r3 and j3 are the same; to demonstrate how ROW is just noise.
    r4 and j4 carry the column names of the registered type.

    You can cast the row (record) to a registered row type if number and data types of the elements match the row type - names of input fields are ignored.