2

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?

NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
Amaterasu
  • 21
  • 2
  • 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 '20 at 20:47

1 Answers1

6

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

mustaccio
  • 20,465
  • 17
  • 49
  • 60