PG10b2: column-list UPDATE syntax fails with single column
In testing our application with PG10beta2, I discovered that our use of UPDATE
broke, apparently by this commit
|commit 906bfcad7ba7cb3863fe0e2a7810be8e3cd84fbd
|Author: Tom Lane <tgl@sss.pgh.pa.us>
|Date: Tue Nov 22 15:19:57 2016 -0500
|
| Improve handling of "UPDATE ... SET (column_list) = row_constructor".
ERROR ON QUERY: UPDATE eric_enodeb_cell_metrics SET (pmActiveDrbDlSum)=(%s) WHERE sect_id=%s AND subnetwork_id=%s AND start_time=%s AND site_id=%s AND interval_seconds=%s AND nedn=%s AND mecontext=%s AND EUtranCellxDD=%s AND EUtranCell=%s AND subnetwork=%s AND device_id=%s --
Query Error: ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
This still works for multiple columns but in pg10b2 no longer works for single
column update with "column_list" syntax.
ts=# begin; UPDATE eric_enodeb_201707 SET (pmhdelayvarbest50pct,pmlicconnecteduserstimecong)=(0,0) ;
BEGIN
UPDATE 3
ts=# begin; UPDATE eric_enodeb_201707 SET (pmhdelayvarbest50pct)=(0) ;
BEGIN
ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
It may be that our use was wrong (?) or unintuitive (I'm in the middle of
changing it), but wondered if it was intentional or otherwise if the release
notes should mention that old syntax is no longer accepted.
Either way - thanks in advance.
Justin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Justin Pryzby <pryzby@telsasoft.com> writes:
ts=# begin; UPDATE eric_enodeb_201707 SET (pmhdelayvarbest50pct,pmlicconnecteduserstimecong)=(0,0) ;
BEGIN
UPDATE 3
ts=# begin; UPDATE eric_enodeb_201707 SET (pmhdelayvarbest50pct)=(0) ;
BEGIN
ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
Hm. It's kind of unfortunate that this isn't backwards-compatible, but
it was only accidental that that case was accepted before. It's really
wrong, because the source for a parenthesized UPDATE target list ought
to be a row value even when there's only one item in the list. That
is, the correct standards-compliant spelling would be
UPDATE eric_enodeb_201707 SET (pmhdelayvarbest50pct) = ROW(0);
Now, it's true that "(1,2)" is fully equivalent to "ROW(1,2)", but
"(0)" is *not* equivalent to "ROW(0)"; it's just a scalar 0. So your
existing code is non-spec-compliant and was really being accepted in
error.
We could maybe hack up some weird action-at-a-distance kluge that would
make this case work like before, but I'm afraid it would just introduce
other inconsistencies.
It may be that our use was wrong (?) or unintuitive (I'm in the middle of
changing it), but wondered if it was intentional or otherwise if the release
notes should mention that old syntax is no longer accepted.
Not sure if it's worth getting into in the release notes. Using the
parenthesis notation for single target columns seems rather weird.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers