Thread: [GENERAL] UPDATE syntax change
Hey all, just getting around to updating my development environment to
Postgres 10, and there was something I found while in testing.
Version info: PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc
(Debian 6.3.0-18) 6.3.0 20170516, 64-bit
I have some queries that were working in 9.6 which suddenly broke when
moving to 10.
Digging in, the error i'm getting is: ERROR: source for a
multiple-column UPDATE item must be a sub-SELECT or ROW() expression
Test script to replicate:
--so we have something to test with
CREATE TEMPORARY TABLE tst_table (a serial primary key, b text, c text);
--works
UPDATE tst_table
SET (b, c) = ('help me', 'please')
WHERE a = 0;
--does not work
UPDATE tst_table
SET (b) = ('help me')
WHERE a = 0;
So there was a change made, and you now cannot use the multi-column
syntax if you're only updating a single column. Was this intentional?
I looked through my codebase, and luckily I have only a couple places
where that syntax was used. Was just an unexpected change for me as I
couldn't find anything in the release notes about it, nor could I find
any mention of it in the docs. It also didn't issue a warning in 9.6,
so there was nothing to tell me that the syntax was incorrect and
would change later.
Thanks,
-Adam
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax failswith single column)
From
Justin Pryzby
Date:
On Mon, Oct 30, 2017 at 12:29:03PM -0400, Adam Brusselback wrote: > I have some queries that were working in 9.6 which suddenly broke when > moving to 10. > > Digging in, the error i'm getting is: ERROR: source for a > multiple-column UPDATE item must be a sub-SELECT or ROW() expression > So there was a change made, and you now cannot use the multi-column > syntax if you're only updating a single column. Was this intentional? I found the same while testing during beta: https://www.postgresql.org/message-id/flat/20170719174507.GA19616%40telsasoft.com#20170719174507.GA19616@telsasoft.com Justin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax failswith single column)
From
Adam Brusselback
Date:
Appreciate the link, didn't come up when I was googling the issue. As you said, a mention in the release notes would have been helpful. Thanks, -Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Adam Brusselback <adambrusselback@gmail.com> writes:
> --works
> UPDATE tst_table
> SET (b, c) = ('help me', 'please')
> WHERE a = 0;
> --does not work
> UPDATE tst_table
> SET (b) = ('help me')
> WHERE a = 0;
> So there was a change made, and you now cannot use the multi-column
> syntax if you're only updating a single column. Was this intentional?
You still can, but you have to write ROW() explicitly. This conforms
to the standard, which our old behavior didn't.
It was probably an oversight not to list this change as a compatibility
issue. I'll go fix that ...
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general