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?

  • It's not typically helpful to ask why features are or are not in languages or particular versions of languages. It's the current output of a complex social process. Better to ask whether particular research-supported reasoning on your part is sensible re why features would be good or bad. – philipxy Sep 21 at 20:47

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™.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.