Question 246973 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres SQL – use upsert to swap values in 2 rows

I have the following table with a combo key columns

CREATE TABLE IF NOT EXISTS MyTable
(
    ColumnID1 integer NOT NULL,
    ColumnID2 integer NOT NULL,
    ColumnName character varying(1000),
    CONSTRAINT "MyTable_pkey" PRIMARY KEY (ColumnID1, ColumnID2),
    CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" UNIQUE (ColumnID1, ColumnName)    
)

let’s add 2 rows to the table

insert into MyTable
values 
(1, 1, 'a'), (1, 2, 'b')

I can UPSERT the following values to my table using

--======= Update the 2 rows with new values (SUCCESS)
insert into MyTable (ColumnID1, ColumnID2, ColumnName)
values (1, 1, 'c'), (1, 2, 'd')
on conflict (ColumnID1, ColumnID2)
do update
set
    ColumnName = EXCLUDED.ColumnName

But when I try to swap the ColumnName value for the two rows I get an error

insert into MyTable (ColumnID1, ColumnID2, ColumnName)
values (1, 1, 'd'), (1, 2, 'c')
on conflict (ColumnID1, ColumnID2)
do update
set
    ColumnName = EXCLUDED.ColumnName

It complains about ColumnID1, ColumnName uniquenss

ERROR:  Key (columnid1, columnname)=(1, d) already exists.duplicate key value violates unique constraint "MyTable-ColumnID1-ColumnName-Unique-Key" 

ERROR:  duplicate key value violates unique constraint "MyTable-ColumnID1-ColumnName-Unique-Key"
SQL state: 23505
Detail: Key (columnid1, columnname)=(1, d) already exists.

How can I change my upsert query to allow swapping values in existing rows.

I tried appending ColumnName to the ON CONFLICT condition but that doesn’t work either

Tags:

3

Answers


  1. 0

    You can solve issue using DEFERRED UNIQUE KEY:

    CREATE TABLE IF NOT EXISTS MyTable
(
    ColumnID1 integer NOT NULL,
    ColumnID2 integer NOT NULL,
    ColumnName character varying(1000),
    CONSTRAINT "MyTable_pkey" PRIMARY KEY (ColumnID1, ColumnID2),
    CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" UNIQUE (ColumnID1, ColumnName) 
    DEFERRABLE INITIALLY DEFERRED  -- here the solution
);

    Test it here

    The DEFERRED constraints are not checked until transaction commit.

    Login or Signup to reply.
  2. 0

    To be able to swap values from existing rows you need to change the ON CONFLICT condition to include the ColumnName column.

    The following query can help you solve this :

    CREATE TABLE IF NOT EXISTS MyTable
(
    ColumnID1 integer NOT NULL,
    ColumnID2 integer NOT NULL,
    ColumnName character varying(1000),
    CONSTRAINT "MyTable_pkey" PRIMARY KEY (ColumnID1, ColumnID2),
    CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" UNIQUE (ColumnID1, ColumnName)    
);

insert into MyTable (ColumnID1, ColumnID2, ColumnName)
values (1, 1, 'd'), (1, 2, 'c')
on conflict (ColumnID1, ColumnName)
do update
set
    ColumnName = case when Excluded.ColumnName = 'd' then 'c' else 'd' end;


select * from MyTable;
    Login or Signup to reply.
  3. 0

    One approach I can think off the top of my head is you simply remove the constraint and add it back after doing your update. Possibly, like so:

    CREATE TABLE IF NOT EXISTS MyTable
(
    ColumnID1 integer NOT NULL,
    ColumnID2 integer NOT NULL,
    ColumnName character varying(1000),
    CONSTRAINT "MyTable_pkey" PRIMARY KEY (ColumnID1, ColumnID2),
    CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" UNIQUE (ColumnID1, ColumnName)    
);

INSERT INTO MyTable (ColumnID1, ColumnID2, ColumnName)
VALUES 
    (1, 1, 'a'),
    (1, 2, 'b');

-- Drop the unique constraint temporarily
ALTER TABLE MyTable DROP CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key";

-- Swap vals
UPDATE MyTable
SET ColumnName = CASE
    WHEN ColumnName = 'a' THEN 'b'
    WHEN ColumnName = 'b' THEN 'a'
    ELSE ColumnName
END;

-- Recreate it
ALTER TABLE MyTable ADD CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" UNIQUE (ColumnID1, ColumnName);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search