Обсуждение: [BUGS] Possible regression in 'UPDATE ... SET () = ' with just one single column/row value since v10

YasonTR <yasontr@protonmail.com> writes:
> The following (odd) construction worked in pgv9.x: "UPDATE my_table SET (my_col) = (some value)". This no longer
worksin v10. It gives the error "source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression". It
lookslike it only breaks when the column list just holds one single column. 

Yeah, IIRC this was an intentional change.  The fact that the previous
coding allowed you to do that was a mistake, because per spec you really
need to supply a row value when you parenthesize the SET column list.
As of v10 it should work to write
UPDATE my_table SET (my_col) = ROW(some value)
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

-------- Original Message --------

Subject: Re: [BUGS] Possible regression in 'UPDATE ... SET () = ' with just one single column/row value since v10

Local Time: October 24, 2017 4:18 PM

UTC Time: October 24, 2017 2:18 PM

From: tgl@sss.pgh.pa.us

To: YasonTR <yasontr@protonmail.com>

pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>

YasonTR yasontr@protonmail.com writes:

The following (odd) construction worked in pgv9.x: "UPDATE my_table SET (my_col) = (some value)". This no longer works in v10. It gives the error "source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression". It looks like it only breaks when the column list just holds one single column.

Yeah, IIRC this was an intentional change. The fact that the previous

coding allowed you to do that was a mistake, because per spec you really

need to supply a row value when you parenthesize the SET column list.

As of v10 it should work to write

UPDATE my_table SET (my_col) = ROW(some value)

regards, tom lane

--

Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-bugs

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Oct 24, 2017 at 7:55 AM, YasonTR <yasontr@protonmail.com> wrote:
>> I get your reference to the spec, but why is it working without ROW() when
>> multiple columns are involved? For example: "UPDATE my_table SET (a, b) =
>> (x, y)" works on v10 (afaik).

> "The key word ROW is optional when there is more than one expression in
> the list."

Right.  This was not one of the SQL committee's better syntax choices,
in my book --- allowing ROW to be optional makes the single-column case
a weird exception, since then and only then ROW is required to make it a
row constructor and not just an expression with useless extra parens.

The spec says that the source value for a parenthesized SET list
is a <contextually typed row value expression>.  Pre-v10, we handled
this with a grammar hack that looked specifically for a parenthesized
list of expressions, and as it happened it would accept a single
parenthesized expression as well.  Now it's expecting a normal
row constructor, which can be one ofROW(one_expr)ROW(an_expr, another_expr [, ...])(an_expr, another_expr [, ...])
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi David, Tom,

Thanks for information. I could have found it in the docs, but in hindsight, I did really think about the necessity to
todistinguish between the case with just one column and the case with multiple columns. It's clear for me now. 

Warm regards,

YasonTR

>-------- Original Message --------
>Subject: Re: [BUGS] Possible regression in 'UPDATE ... SET () = ' with just one single column/row value since v10
>Local Time: October 24, 2017 5:21 PM
>UTC Time: October 24, 2017 3:21 PM
>From: tgl@sss.pgh.pa.us
>To: David G. Johnston <david.g.johnston@gmail.com>
>YasonTR <yasontr@protonmail.com>, pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
>
>"David G. Johnston" david.g.johnston@gmail.com writes:
>>On Tue, Oct 24, 2017 at 7:55 AM, YasonTR yasontr@protonmail.com wrote:
>>>I get your reference to the spec, but why is it working without ROW() when
>>> multiple columns are involved? For example: "UPDATE my_table SET (a, b) =
>>> (x, y)" works on v10 (afaik).
>>>
>>> "The key word ROW is optional when there is more than one expression in
>>> the list."
>>>
>>> Right.  This was not one of the SQL committee's better syntax choices,
>>> in my book --- allowing ROW to be optional makes the single-column case
>>> a weird exception, since then and only then ROW is required to make it a
>>> row constructor and not just an expression with useless extra parens.
>>>
>>> The spec says that the source value for a parenthesized SET list
>>> is a <contextually typed row value expression>.  Pre-v10, we handled
>>> this with a grammar hack that looked specifically for a parenthesized
>>> list of expressions, and as it happened it would accept a single
>>> parenthesized expression as well.  Now it's expecting a normal
>>> row constructor, which can be one of
>>> ROW(one_expr)
>>> ROW(an_expr, another_expr [, ...])
>>> (an_expr, another_expr [, ...])
>>>
>>> regards, tom lane
>>>
>>>
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>>> To make changes to your subscription:
>>>http://www.postgresql.org/mailpref/pgsql-bugs
>>>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs