BUG #14132: ON CONFLICT not inferring unique index with bigints
The following bug has been logged on the website:
Bug reference: 14132
Logged by: Reynold Smith
Email address: fiercetuba@protonmail.com
PostgreSQL version: 9.5.2
Operating system: Linux
Description:
This fails, but change bigint to int and it works fine.
```
CREATE TABLE testtable(
a serial PRIMARY KEY,
b bigint,
c bigint
);
CREATE UNIQUE INDEX super_unique_idx ON testtable( COALESCE(b, 0),
COALESCE(c, 0));
INSERT INTO testtable(b, c)
VALUES (1, 2)
ON CONFLICT ((COALESCE(b, 0)), (COALESCE(c, 0))) DO NOTHING;
```
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
fiercetuba@protonmail.com writes:
This fails, but change bigint to int and it works fine.
Looks like somebody forgot to run the ON CONFLICT list through
expression preprocessing. Unfortunately it's too late to get
this fixed in tomorrow's releases :-(
As a workaround for this particular example, you could write either
INSERT INTO testtable(b, c)
VALUES (1, 2)
ON CONFLICT ((COALESCE(b, '0'::int8)), (COALESCE(c, '0'::int8))) DO NOTHING;
INSERT INTO testtable(b, c)
VALUES (1, 2)
ON CONFLICT ((COALESCE(b, '0')), (COALESCE(c, '0'))) DO NOTHING;
but in general there might not be any such easy solution.
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
On Wed, May 11, 2016 at 7:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Looks like somebody forgot to run the ON CONFLICT list through
expression preprocessing. Unfortunately it's too late to get
this fixed in tomorrow's releases :-(
Mea culpa. I'll write a patch to fix this soon, aimed for inclusion in
the next point release.
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, May 11, 2016 at 11:41 AM, Peter Geoghegan <pg@heroku.com> wrote:
Mea culpa. I'll write a patch to fix this soon, aimed for inclusion in
the next point release.
Looks like you've done so already. Thanks.
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs