What is a row constructor used for?

Question

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);

Answer 1

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.

• Return multiple columns of the same row as JSON array of objects

Answer 2

You're asking about the difference between a value and a type.

It's about the same difference than between an object and a class in an OO language.

In the first case you're building a value which could be used in comparisons, in row writing, or to be passed to functions accepting composite parameters.

In the second case you're defining a type that can be used for example in a function or table definition.

Answer 3

Row constructors can be used to build composite values to be stored in a composite-type table column, or to be passed to a function that accepts a composite parameter. Also, it is possible to compare two row values or test a row with IS NULL or IS NOT NULL.

4.2.13. Row Constructors

Example:

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
CREATE TABLE mytable (ct myrowtype);
INSERT INTO mytable(ct) VALUES (CAST(ROW(11,'this is a test',2.5) AS myrowtype));

Answer 4

ROW(...) is not a function. It's SQL syntax, more like the ARRAY[...] constructor than like a function.

The ROW constructor is primarily used to form anonymous records. This can be useful when you need to keep a collection of fields together, but they don't correspond to an existing table type or composite data type.

These two are equivalent in PostgreSQL:

test=> SELECT t FROM (SELECT 1, 'x', NUMERIC '42.1') AS t;
     t      
------------
 (1,x,42.1)
(1 row)

test=> SELECT ROW(1, 'x', NUMERIC '42.1');
    row     
------------
 (1,x,42.1)
(1 row)

in that both create an anonymous record:

test=> SELECT pg_typeof(t) FROM (SELECT 1, 'x', NUMERIC '42.1') AS t;
 pg_typeof 
-----------
 record
(1 row)

test=> SELECT pg_typeof(ROW(1, 'x', NUMERIC '42.1'));
 pg_typeof 
-----------
 record
(1 row)

The records created by ROW can correspond to existing types, for when you're passing composite types to a function, e.g given:

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

you can create a myrowtype with:

test=> SELECT CAST(ROW(1, 'x', '42.1') AS myrowtype);
    row     
------------
 (1,x,42.1)
(1 row)