Horton

Thread

Commits

  1. Doc: call out UPDATE syntax change as a v10 compatibility issue.

  1. UPDATE syntax change

    Adam Brusselback <adambrusselback@gmail.com> — 2017-10-30T16:29:03Z

    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

  2. Re: UPDATE syntax change (column-list UPDATE syntax fails with single column)

    Justin Pryzby <pryzby@telsasoft.com> — 2017-10-30T16:34:04Z

    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

  3. Re: UPDATE syntax change (column-list UPDATE syntax fails with single column)

    Adam Brusselback <adambrusselback@gmail.com> — 2017-10-30T16:42:31Z

    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

  4. Re: UPDATE syntax change

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-10-30T16:42:46Z

    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