Postgresql – Why is the ROW keyword needed when constructing a row with one element, but not two

postgresqlrowsyntax

In PostgreSQL 11, the following expression

UPDATE test_table SET ("column_a","column_b") = ('value-a','value-b') WHERE "column_a" = 'value-c'

is accepted and performs the update.

But for a single column / value, like

UPDATE test_table SET ("column_a") = ('value-a') WHERE "column_a" = 'value-c'

it gives an error. ROW must be supplied, like

UPDATE test_table SET ("column_a") = ROW ('value-a') WHERE "column_a" = 'value-c'

Is there an explanation why this behavior was chosen? Is there an ambiguity if ROW is missing?

Best Answer

As is often the case, the answer is to be found in documentation.

The UPDATE reference says, in particular:

According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. PostgreSQL only allows the source value to be a row constructor or a sub-SELECT.

whereas "row constructor" is defined like so:

A row constructor is an expression that builds a row value (also called a composite value) using values for its member fields. A row constructor consists of the key word ROW, a left parenthesis, zero or more expressions (separated by commas) for the row field values, and finally a right parenthesis. [...] The key word ROW is optional when there is more than one expression in the list.

The last sentence kind of makes sense, because a single expression in parentheses is ambiguous by itself and can be confused with a simple parenthesised expression.

This syntax was introduced in version 10 -- HT to ypercube™.

`spi` module: `moddatetime`

moddatetime — Functions for Tracking Last Modification Time

moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

There is an example in moddatetime.example.

Example / Synopsis

From the above referenced file,

DROP TABLE mdt;

CREATE TABLE mdt (
    id      int4,
    idesc       text,
    moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER mdt_moddatetime
    BEFORE UPDATE ON mdt
    FOR EACH ROW
    EXECUTE PROCEDURE moddatetime (moddate);

INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');

SELECT * FROM mdt;

UPDATE mdt SET id = 4
    WHERE id = 1;
UPDATE mdt SET id = 5
    WHERE id = 2;
UPDATE mdt SET id = 6
    WHERE id = 3;

SELECT * FROM mdt;

Your Application

So this is what you would need to.

CREATE EXTENSION moddatetime;

CREATE TRIGGER mdt_table
  BEFORE UPDATE ON table
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (prefix_last_modified);

Best Answer

Related Solutions

Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results

PostgreSQL – How to Create Trigger on All Last_Modified Columns

spi module: moddatetime

Example / Synopsis

Your Application

Related Question

`spi` module: `moddatetime`