UPDATE syntax change

From: Adam Brusselback <adambrusselback(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: UPDATE syntax change
Date: 2017-10-30 16:29:03
Message-ID: CAMjNa7cDLzPcs0xnRpkvqmJ6Vb6G3EH8CYGp9ZBjXdpFfTz6dg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Geoghegan 2017-10-30 16:32:01 Re: gin index trouble
Previous Message Tom Lane 2017-10-30 16:23:18 Re: gin index trouble