CODEWITHSUNDEEP
postgresqlcommon-table-expressionupsert
Madhuri
Madhuri

Reputation: 19

how to update and then insert a record with unique key constraint

i have a table with columns a,b,c and c having the unique key constraint.

a |b |c(uk)
----------
aa 1  z

I want to insert a new row with values (bb,1,z), if z already exists then i first want to update the existing row to

a |b |c(uk)
----------
aa 1  null

and then insert (bb,1,z), so that the final rows looks as shown below.

a |b |c(uk)
----------
aa 1  null
bb 1  z

how can i do this a single sql statement?

Upvotes: 0

Views: 1369

Answers (2)

user330315
user330315

Reputation:

Unfortunately unique constraints are checked per row in Postgres - unless they are created as "deferrable".

So if you want to do the UPDATE and the INSERT in a single statement, you will have to re-create your unique constraint with the attribute deferrable. Then Postgres will check it at the end of the statement.

create table the_table 
(
  a text, 
  b int, 
  c text
);

alter table the_table 
    add constraint unique_c 
    unique(c)
    deferrable --<< this is the "magic"
;

Then you can do it with a data modifying CTE

with input (a,b,c) as (
  values ('bb', 1, 'z')
), change_old as (
  update the_table
  set c = null
  from input
  where input.c = the_table.c
)  
insert into the_table
select a,b,c
from input;

Upvotes: 1

Julius Tuskenis
Julius Tuskenis

Reputation: 1630

I think you should use BEFORE INSERT TRIGGER for this.

In the trigger you would update all the records containing value new.c like:

UPDATE my_table SET
  c = NULL
WHERE 
  c = new.c;

If c is not present - nothing will happen.

You may still run into unique constraint violation error if 2 concurent transactions inserts the same value into c. To avoid that you can use advisory locks providing lock ID from value of c.

Upvotes: 0

Related Questions