Обсуждение: 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; ```
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
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
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