CODEWITHSUNDEEP
databasepostgresql
Pasha Osipyants
Pasha Osipyants

Reputation: 113

Conditional ON CONFLICT

I wonder whether it is possible in postgres to make ON CONFLICT (in INSERT) logic work conditionally. I mean specify some condition, and if it is satisfied ON CONFLICT logic is executed, otherwise conflict error is returned.

That's what i tried:

INSERT INTO "%s" (
    "id",
    "color",
    "is_enabled"
) VALUES ($1, $2, true) 
ON CONFLICT(id) WHERE is_enabled=false
DO UPDATE SET color=$2, is_enabled = true

I want to insert new record if there is no one with such id. Update if there is one, but it is disabled. Get conflict error if there is one and it is enabled.

Current sql doesn't work, i guess because in WHERE clause one can put only clauses related to partial indexes. (i am not sure, docs are confusing for me at this point...)

But is it possible to achieve logic that i want to have with one sql query?

Upvotes: 11

Views: 5109

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28253

User Laurenz Albe's suggestion is very smart. if you defined your schema as:

create table mytable(id int, color text, is_enabled bool,
  exclude (id with =)
);
create unique index on mytable (id) where is_enabled = false;

the following insert statement will exhibit the desired behaviour:

1. insert if id does not exist
2. update if row with id exists but is_enabled set to false
3. raise error if row with id exists but is_enabled set to true


insert into mytable values
(?, ?, ?)
on conflict (id) where is_enabled = false do update
set color = excluded.color, 
    is_enabled = excluded.is_enabled;

Test script:

-- test-pgsql.sql
drop table if exists mytable;
create table mytable(id int, color text, is_enabled bool,
  exclude (id with =)
);
create unique index on mytable (id) where is_enabled = false;

insert into mytable values
(1, 'red', true)
on conflict (id) where is_enabled = false do update
set color = excluded.color, 
    is_enabled = excluded.is_enabled;

insert into mytable values
(1, 'blue', true)
on conflict (id) where is_enabled = false do update
set color = excluded.color, 
    is_enabled = excluded.is_enabled;

insert into mytable values
(2, 'blue', false)
on conflict (id) where is_enabled = false do update
set color = excluded.color, 
    is_enabled = excluded.is_enabled;

insert into mytable values
(2, 'bluer', false)
on conflict (id) where is_enabled = false do update
set color = excluded.color, 
    is_enabled = excluded.is_enabled;

WITH expected (id, expected_color, expected_is_enabled) AS (
VALUES
  (1, 'red', true),
  (2, 'bluer', false)
)
SELECT * 
FROM mytable 
JOIN expected USING (id)

Test Output:

$ psql -f test-pgsql.sql
DROP TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 1
psql:test-pgsql.sql:17: ERROR:  conflicting key value violates exclusion constraint "mytable_id_excl"
DETAIL:  Key (id)=(1) conflicts with existing key (id)=(1).
INSERT 0 1
INSERT 0 1
 id | color | is_enabled | expected_color | expected_is_enabled
----+-------+------------+----------------+---------------------
  1 | red   | t          | red            | t
  2 | bluer | f          | bluer          | f
(2 rows)

For some reason unknown to me, if both the partial unique index on (id) and a primary key / unique index on (id) are both defined, then the insert-on-conflict recognizes the unique constraint instead of the specified (id) where is_enabled = false constraint and performs an update regardless of the value of is_enabled.

I think this is either a bug or an undocumented behaviour and have filed a bug report, which, if you are interested, you may follow here: https://www.postgresql.org/message-id/CAGPLuc5%3DqHg%3DZw_4wauhgee-OQZtNfC9-ab%2BYE3vVbLXfk0m8g%40mail.gmail.com

Upvotes: 5

Related Questions