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