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