Insert on duplicate update in PostgreSQL

Question

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax   INSERT INTO table  id  field  field2  VALUES  1  A  X    2  B  Y    3  C  Z  ON DUPLICATE KEY UPDATE field VALUES Col1   field2 VALUES Col2     I ve now switched over to PostgreSQL and apparently this is not correct  It s referring to all the correct tables so I assume it s a matter of different keywords being used but I m not sure where in the PostgreSQL documentation this is covered   To clarify  I want to insert several things and if they already exist to update them

User · Answer

UPDATE will return the number of modified rows. If you use JDBC (Java), you can then check this value against 0 and, if no rows have been affected, fire INSERT instead. If you use some other programming language, maybe the number of the modified rows still can be obtained, check documentation.

This may not be as elegant but you have much simpler SQL that is more trivial to use from the calling code. Differently, if you write the ten line script in PL/PSQL, you probably should have a unit test of one or another kind just for it alone.

User · Answer

Personally  I ve set up a  rule  attached to the insert statement  Say you had a  dns  table that recorded dns hits per customer on a per-time basis   CREATE TABLE dns        time  timestamp without time zone NOT NULL      customer id integer NOT NULL      hits integer      You wanted to be able to re-insert rows with updated values  or create them if they didn t exist already  Keyed on the customer id and the time  Something like this   CREATE RULE replace dns AS      ON INSERT TO dns      WHERE  EXISTS  SELECT 1 FROM dns WHERE   dns  time    new  time                AND  dns customer id   new customer id          DO INSTEAD UPDATE dns          SET hits   new hits          WHERE   dns  time    new  time   AND  dns customer id   new customer id      Update  This has the potential to fail if simultaneous inserts are happening  as it will generate unique violation exceptions  However  the non-terminated transaction will continue and succeed  and you just need to repeat the terminated transaction   However  if there are tons of inserts happening all the time  you will want to put a table lock around the insert statements  SHARE ROW EXCLUSIVE locking will prevent any operations that could insert  delete or update rows in your target table  However  updates that do not update the unique key are safe  so if you no operation will do this  use advisory locks instead   Also  the COPY command does not use RULES  so if you re inserting with COPY  you ll need to use triggers instead

User · Answer

According the PostgreSQL documentation of the INSERT statement  handling the ON DUPLICATE KEY case is not supported  That part of the syntax is a proprietary MySQL extension

User · Answer

With PostgreSQL 9 1 this can be achieved using a writeable CTE  common table expression    WITH new values  id  field1  field2  as     values        1   A    X          2   B    Y          3   C    Z       upsert as        update mytable m          set field1   nv field1              field2   nv field2     FROM new values nv     WHERE m id   nv id     RETURNING m     INSERT INTO mytable  id  field1  field2  SELECT id  field1  field2 FROM new values WHERE NOT EXISTS  SELECT 1                    FROM upsert up                    WHERE up id   new values id    See these blog entries    Upserting via Writeable CTE WAITING FOR 9 1     WRITABLE CTE WHY IS UPSERT SO COMPLICATED      Note that this solution does not prevent a unique key violation but it is not vulnerable to lost updates  See the follow up by Craig Ringer on dba stackexchange com

User · Answer

Similar to most-liked answer  but works slightly faster   WITH upsert AS  UPDATE spider count SET tally 1 WHERE date  today  RETURNING    INSERT INTO spider count  spider  tally  SELECT  Googlebot   1 WHERE NOT EXISTS  SELECT   FROM upsert     source  http   www the-art-of-web com sql upsert

User · Answer

There is no simple command to do it   The most correct approach is to use function  like the one from docs   Another solution  although not that safe  is to do update with returning  check which rows were updates  and insert the rest of them  Something along the lines of   update table set column   x column from  values  1  aa    2  bb    3  cc    as x  id  column  where table id   x id returning id    assuming id 2 was returned   insert into table  id  column  values  1   aa     3   cc      Of course it will bail out sooner or later  in concurrent environment   as there is clear race condition in here  but usually it will work   Here s a longer and more comprehensive article on the topic

User · Answer

I have the same issue for managing account settings as name value pairs  The design criteria is that different clients could have different settings sets   My solution  similar to JWP  is to bulk erase and replace  generating the merge record within your application   This is pretty bulletproof  platform independent and since there are never more than about 20 settings per client  this is only 3 fairly low load db calls - probably the fastest method   The alternative of updating individual rows - checking for exceptions then inserting - or some combination of is hideous code  slow and often breaks because  as mentioned above  non standard SQL exception handling changing from db to db - or even release to release     This is pseudo-code - within the application   BEGIN TRANSACTION - get transaction lock  SELECT all current name value pairs where id    id into a hash record  create a merge record from the current and update record    set intersection where shared keys in new win  and empty values in new are deleted    DELETE all name value pairs where id    id  COPY INSERT merged records   END TRANSACTION

User · Answer

CREATE OR REPLACE FUNCTION save user  id integer   name character varying    RETURNS boolean AS  BODY  BEGIN     UPDATE users SET name    name WHERE id    id      IF FOUND THEN         RETURN true      END IF      BEGIN         INSERT INTO users  id  name  VALUES   id   name       EXCEPTION WHEN OTHERS THEN             UPDATE users SET name    name WHERE id    id          END      RETURN TRUE  END    BODY    LANGUAGE plpgsql VOLATILE STRICT

User · Answer

Warning  this is not safe if executed from multiple sessions at the same time  see caveats below      Another clever way to do an  UPSERT  in postgresql is to do two sequential UPDATE INSERT statements that are each designed to succeed or have no effect   UPDATE table SET field  C   field2  Z  WHERE id 3  INSERT INTO table  id  field  field2         SELECT 3   C    Z         WHERE NOT EXISTS  SELECT 1 FROM table WHERE id 3     The UPDATE will succeed if a row with  id 3  already exists  otherwise it has no effect   The INSERT will succeed only if row with  id 3  does not already exist   You can combine these two into a single string and run them both with a single SQL statement execute from your application   Running them together in a single transaction is highly recommended   This works very well when run in isolation or on a locked table  but is subject to race conditions that mean it might still fail with duplicate key error if a row is inserted concurrently  or might terminate with no row inserted when a row is deleted concurrently  A SERIALIZABLE transaction on PostgreSQL 9 1 or higher will handle it reliably at the cost of a very high serialization failure rate  meaning you ll have to retry a lot  See why is upsert so complicated  which discusses this case in more detail   This approach is also subject to lost updates in read committed isolation unless the application checks the affected row counts and verifies that either the insert or the update affected a row

User · Answer

I use this function merge  CREATE OR REPLACE FUNCTION merge tabla key INT  data TEXT    RETURNS void AS  BODY  BEGIN     IF EXISTS SELECT a FROM tabla WHERE a   key          THEN             UPDATE tabla SET b   data WHERE a   key          RETURN      ELSE         INSERT INTO tabla a b  VALUES  key  data           RETURN      END IF  END   BODY  LANGUAGE plpgsql

User · Answer

I custom  upsert  function above  if you want to INSERT AND REPLACE            CREATE OR REPLACE FUNCTION upsert sql insert text  sql update text    RETURNS void AS   BODY   BEGIN     -- first try to insert and after to update  Note   insert has pk and update not         EXECUTE sql insert      RETURN      EXCEPTION WHEN unique violation THEN     EXECUTE sql update       IF FOUND THEN          RETURN       END IF   END    BODY   LANGUAGE plpgsql VOLATILE  COST 100   ALTER FUNCTION upsert text  text   OWNER TO postgres     And after to execute  do something like this     SELECT upsert   INSERT INTO         UPDATE          Is important to put double dollar-comma to avoid compiler errors   check the speed

User · Answer

I was looking for the same thing when I came here  but the lack of a generic  upsert  function botherd me a bit so I thought you could just pass the update and insert sql as arguments on that function form the manual  that would look like this   CREATE FUNCTION upsert  sql update TEXT  sql insert TEXT      RETURNS VOID     LANGUAGE plpgsql AS    BEGIN     LOOP         -- first try to update         EXECUTE sql update          -- check if the row is found         IF FOUND THEN             RETURN          END IF          -- not found so insert the row         BEGIN             EXECUTE sql insert              RETURN              EXCEPTION WHEN unique violation THEN                 -- do nothing and loop         END      END LOOP  END        and perhaps to do what you initially wanted to do  batch  upsert   you could use Tcl to split the sql update and loop the individual updates  the preformance hit will be very small see http   archives postgresql org pgsql-performance 2006-04 msg00557 php  the highest cost is executing the query from your code  on the database side the execution cost is much smaller

User · Answer

In PostgreSQL 9 5 and newer you can use INSERT     ON CONFLICT UPDATE   See the documentation   A MySQL INSERT     ON DUPLICATE KEY UPDATE can be directly rephrased to a ON CONFLICT UPDATE  Neither is SQL-standard syntax  they re both database-specific extensions  There are good reasons MERGE wasn t used for this  a new syntax wasn t created just for fun   MySQL s syntax also has issues that mean it wasn t adopted directly    e g  given setup   CREATE TABLE tablename  a integer primary key  b integer  c integer   INSERT INTO tablename  a  b  c  values  1  2  3     the MySQL query   INSERT INTO tablename  a b c  VALUES  1 2 3    ON DUPLICATE KEY UPDATE c c 1    becomes   INSERT INTO tablename  a  b  c  values  1  2  10  ON CONFLICT  a  DO UPDATE SET c   tablename c   1    Differences    You must specify the column name  or unique constraint name  to use for the uniqueness check  That s the ON CONFLICT  columnname  DO The keyword SET must be used  as if this was a normal UPDATE statement   It has some nice features too    You can have a WHERE clause on your UPDATE  letting you effectively turn ON CONFLICT UPDATE into ON CONFLICT IGNORE for certain values  The proposed-for-insertion values are available as the row-variable EXCLUDED  which has the same structure as the target table  You can get the original values in the table by using the table name  So in this case EXCLUDED c will be 10  because that s what we tried to insert  and  table  c will be 3 because that s the current value in the table  You can use either or both in the SET expressions and WHERE clause    For background on upsert see How to UPSERT  MERGE  INSERT     ON DUPLICATE UPDATE  in PostgreSQL

User · Answer

Edit  This does not work as expected   Unlike the accepted answer  this produces unique key violations when two processes repeatedly call upsert foo concurrently   Eureka   I figured out a way to do it in one query  use UPDATE     RETURNING to test if any rows were affected   CREATE TABLE foo  k INT PRIMARY KEY  v TEXT    CREATE FUNCTION update foo k INT  v TEXT  RETURNS SETOF INT AS        UPDATE foo SET v    2 WHERE k    1 RETURNING  1    LANGUAGE sql   CREATE FUNCTION upsert foo k INT  v TEXT  RETURNS VOID AS        INSERT INTO foo         SELECT  1   2         WHERE NOT EXISTS  SELECT update foo  1   2      LANGUAGE sql    The UPDATE has to be done in a separate procedure because  unfortunately  this is a syntax error       WHERE NOT EXISTS  UPDATE        Now it works as desired   SELECT upsert foo 1   hi    SELECT upsert foo 1   bye    SELECT upsert foo 3   hi    SELECT upsert foo 3   bye

User · Answer

PostgreSQL since version 9 5 has UPSERT syntax  with ON CONFLICT clause  with the following syntax  similar to MySQL   INSERT INTO the table  id  column 1  column 2   VALUES  1   A    X     2   B    Y     3   C    Z   ON CONFLICT  id  DO UPDATE    SET column 1   excluded column 1         column 2   excluded column 2      Searching postgresql s email group archives for  upsert  leads to finding an example of doing what you possibly want to do  in the manual      Example 38-2  Exceptions with UPDATE INSERT      This example uses exception handling to perform either UPDATE or INSERT  as appropriate    CREATE TABLE db  a INT PRIMARY KEY  b TEXT    CREATE FUNCTION merge db key INT  data TEXT  RETURNS VOID AS    BEGIN     LOOP         -- first try to update the key         -- note that  a  must be unique         UPDATE db SET b   data WHERE a   key          IF found THEN             RETURN          END IF          -- not there  so try to insert the key         -- if someone else inserts the same key concurrently          -- we could get a unique-key failure         BEGIN             INSERT INTO db a b  VALUES  key  data               RETURN          EXCEPTION WHEN unique violation THEN             -- do nothing  and loop to try the UPDATE again         END      END LOOP  END     LANGUAGE plpgsql   SELECT merge db 1   david    SELECT merge db 1   dennis        There s possibly an example of how to do this in bulk  using CTEs in 9 1 and above  in the hackers mailing list   WITH foos AS  SELECT  UNNEST  foo        updated as  UPDATE foo SET foo a   foos a     RETURNING foo id  INSERT INTO foo SELECT foos   FROM foos LEFT JOIN updated USING id  WHERE updated id IS NULL    See a horse with no name s answer for a clearer example

User · Answer

For merging small sets  using the above function is fine  However  if you are merging large amounts of data  I d suggest looking into http   mbk projects postgresql org  The current best practice that I m aware of is    COPY new updated data into temp table  sure  or you can do INSERT if the cost is ok  Acquire Lock  optional   advisory is preferable to table locks  IMO  Merge   the fun part

[sql] Insert, on duplicate update in PostgreSQL?

Examples related to sql

Examples related to postgresql

Examples related to upsert

Examples related to sql-merge