1

I am unable to create a unique constraint with where clause in Postgres. For example:

CREATE UNIQUE INDEX inbox_a_constraint ON inbox (sender_id, receiver_id, src_type, src_id)
WHERE src_type IN (0, 1) AND src_id IS NOT NULL;

This is created without errors but when I check in adminer it shows that the unique and where properties are left out and only a basic compound index is created.

When I try to use on conflict do update on this table as

INSERT INTO inbox
(sender_id, receiver_id, src_type, src_id, timestamp)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (sender_id, receiver_id, src_type, src_id) DO UPDATE
SET timestamp = $6
RETURNING *

I get an error saying there is no unique or exclusion constraint matching the ON CONFLICT specification

EDIT: With select pg_get_indexdef('inbox_a_constraint'::regclass); I get CREATE UNIQUE INDEX inbox_a_constraint ON public.inbox USING btree (sender_id, receiver_id, src_type, src_id) WHERE ((inbox_type = ANY (ARRAY[0, 1])) AND (src_id IS NOT NULL)). So looks like the index definition is saved correctly. But on conflict error is still an issue.

pewpewlasers
  • 3,025
  • 4
  • 31
  • 58
  • 2
    If that "adminer" thing doesn't show the `where` clause, it's probably a bug in that tool. You can use `select pg_get_indexdef('inbox_a_constraint'::regclass);` to see the definition stored in the database. –  Jul 27 '18 at 06:06
  • @a_horse_with_no_name thanks for that query. I get `CREATE UNIQUE INDEX inbox_a_constraint ON public.inbox USING btree (sender_id, receiver_id, src_type, src_id) WHERE ((inbox_type = ANY (ARRAY[0, 1])) AND (src_id IS NOT NULL))`. So looks like the full definition is stored. But still struggling with the on conflict issue. – pewpewlasers Jul 27 '18 at 06:45

0 Answers0