Postgres INSERT if does not exist already

Question

I m using Python to write to a postgres database   sql string    INSERT INTO hundred  name name slug status  VALUES    sql string    hundred           hundred slug           status        cursor execute sql string    But because some of my rows are identical  I get the following error   psycopg2 IntegrityError  duplicate key value     violates unique constraint  hundred pkey    How can I write an  INSERT unless this row already exists  SQL statement    I ve seen complex statements like this recommended   IF EXISTS  SELECT   FROM invoices WHERE invoiceid    12345   UPDATE invoices SET billed    TRUE  WHERE invoiceid    12345  ELSE INSERT INTO invoices  invoiceid  billed  VALUES   12345    TRUE   END IF   But firstly  is this overkill for what I need  and secondly  how can I execute one of those as a simple string

User · Answer

The solution in simple  but not immediatly  If you want use this instruction  you must make one change to the db   ALTER USER user SET search path to  name of schema     after these changes  INSERT   will work correctly

User · Answer

If you say that many of your rows are identical you will end checking many times  You can send them and the database will determine if insert it or not with the ON CONFLICT clause as follows    INSERT INTO Hundred  name name slug status  VALUES   sql string    hundred             hundred slug           status      ON CONFLICT ON CONSTRAINT   hundred pkey DO NOTHING   cursor execute sql string

User · Answer

The approach with the most upvotes  from John Doe  does somehow work for me but in my case from expected 422 rows i get only 180  I couldn t find anything wrong and there are no errors at all  so i looked for a different simple approach   Using IF NOT FOUND THEN after a SELECT just works perfectly for me    described in PostgreSQL Documentation   Example from documentation   SELECT   INTO myrec FROM emp WHERE empname   myname  IF NOT FOUND THEN   RAISE EXCEPTION  employee   not found   myname  END IF

User · Answer

Postgres 9 5  released since 2016-01-07  offers an  upsert  command  also known as an ON CONFLICT clause to INSERT   INSERT     ON CONFLICT DO NOTHING UPDATE   It solves many of the subtle problems you can run into when using concurrent operation  which some other answers propose

User · Answer

Here is a generic python function that given a tablename  columns and values  generates the upsert equivalent for postgresql   import json  def upsert table name  id column  other columns  values hash        template           WITH new values    ALL COLUMNS    as         values             VALUES LIST               upsert as               update   TABLE NAME   m             set                   SET MAPPINGS           FROM new values nv         WHERE m   ID COLUMN     nv   ID COLUMN           RETURNING m             INSERT INTO   TABLE NAME      ALL COLUMNS        SELECT   ALL COLUMNS       FROM new values     WHERE NOT EXISTS  SELECT 1                       FROM upsert up                       WHERE up   ID COLUMN     new values   ID COLUMN                 all columns    id column    other columns     all columns csv       join all columns      all values csv       join  query value values hash column name   for column name in all columns       set mappings       join   c      nv    c for c in other columns        q   template     q   q replace    TABLE NAME     table name      q   q replace    ID COLUMN     id column      q   q replace    ALL COLUMNS     all columns csv      q   q replace    VALUES LIST     all values csv      q   q replace    SET MAPPINGS     set mappings       return q   def query value value       if value is None          return  NULL      if type value  in  str  unicode           return    s     value replace                if type value     dict          return    s     json dumps value  replace                if type value     bool          return   s    value     if type value     int          return   s    value     return value   if   name         main          my table name    mytable      my id column    id      my other columns     field1    field2       my values hash              id   123           field1    john            field2    doe            print upsert my table name  my id column  my other columns  my values hash

User · Answer

How can I write an  INSERT unless this row already exists  SQL statement     There is a nice way of doing conditional INSERT in PostgreSQL   INSERT INTO example table      id  name  SELECT 1   John  WHERE     NOT EXISTS           SELECT id FROM example table WHERE id   1          CAVEAT This approach is not 100  reliable for concurrent write operations  though  There is a very tiny race condition between the SELECT in the NOT EXISTS anti-semi-join and the INSERT itself  It can fail under such conditions

User · Answer

It s easy with rules   CREATE RULE file insert defer AS ON INSERT TO file WHERE  EXISTS   SELECT   FROM file WHERE file id   new id   DO INSTEAD NOTHING   But it fails with concurrent writes

User · Answer

You can make use of VALUES - available in Postgres   INSERT INTO person  name      SELECT name FROM person     UNION      VALUES   Bob       EXCEPT     SELECT name FROM person

User · Answer

Your column  hundred  seems to be defined as primary key and therefore must be unique which is not the case  The problem isn t with  it is with your data   I suggest you insert an id as serial type to handly the primary key

User · Answer

Unfortunately  PostgreSQL supports neither MERGE nor ON DUPLICATE KEY UPDATE  so you ll have to do it in two statements   UPDATE  invoices SET     billed    TRUE  WHERE   invoices    12345   INSERT INTO    invoices  invoiceid  billed  SELECT   12345    TRUE  WHERE    12345  NOT IN                   SELECT  invoiceid         FROM    invoices             You can wrap it into a function   CREATE OR REPLACE FUNCTION fn upd invoices id VARCHAR 32   billed VARCHAR 32   RETURNS VOID AS            UPDATE  invoices         SET     billed    2         WHERE   invoices    1           INSERT         INTO    invoices  invoiceid  billed          SELECT   1   2         WHERE    1 NOT IN                                   SELECT  invoiceid                 FROM    invoices                       LANGUAGE  sql     and just call it   SELECT  fn upd invoices  12345    TRUE

User · Answer

There is a nice way of doing conditional INSERT in PostgreSQL using WITH query  Like   WITH a as  select   id  from   schema table name  where   column name   your identical column value   INSERT into   schema table name  col name1  col name2  SELECT      col name1  col name2  WHERE NOT EXISTS        SELECT          id      FROM          a             RETURNING id

User · Answer

I was looking for a similar solution  trying to find SQL that work work in PostgreSQL as well as HSQLDB   HSQLDB was what made this difficult   Using your example as a basis  this is the format that I found elsewhere   sql    INSERT INTO hundred  name name slug status   sql        SELECT     hundred           hundred slug           status sql      FROM hundred  sql      WHERE name       hundred     AND name slug        hundred slug      AND status       status sql      HAVING COUNT      0

User · Answer

I know this question is from a while ago  but thought this might help someone   I think the easiest way to do this is via a trigger   E g    Create Function ignore dups   Returns Trigger As    Begin     If Exists           Select                       From             hundred h         Where             -- Assuming all three fields are primary key             h name   NEW name             And h hundred slug   NEW hundred slug             And h status   NEW status       Then         Return NULL      End If      Return NEW  End     Language plpgsql   Create Trigger ignore dups     Before Insert On hundred     For Each Row     Execute Procedure ignore dups      Execute this code from a psql prompt  or however you like to execute queries directly on the database    Then you can insert as normal from Python   E g    sql    Insert Into hundreds  name  name slug  status  Values   s   s   s   cursor execute sql   hundred  hundred slug  status     Note that as  Thomas Wouters already mentioned  the code above takes advantage of parameters rather than concatenating the string

User · Answer

INSERT    WHERE NOT EXISTS is good approach  And race conditions can be avoided by transaction  envelope    BEGIN  LOCK TABLE hundred IN SHARE ROW EXCLUSIVE MODE  INSERT       COMMIT

User · Answer

One approach would be to create a non-constrained  no unique indexes  table to insert all your data into and do a select distinct from that to do your insert into your hundred table   So high level would be   I assume all three columns are distinct in my example so for step3 change the NOT EXITS join to only join on the unique columns in the hundred table    Create temporary table  See docs here   CREATE TEMPORARY TABLE temp data name  name slug  status    INSERT Data into temp table   INSERT INTO temp data name  name slug  status     Add any indexes to the temp table  Do main table insert   INSERT INTO hundred name  name slug  status       SELECT DISTINCT name  name slug  status     FROM hundred     WHERE NOT EXISTS           SELECT  X           FROM temp data         WHERE              temp data name            hundred name             AND temp data name slug   hundred name slug             AND temp data status      status

User · Answer

psycopgs cursor class has the attribute rowcount      This read-only attribute specifies the number of rows that the last   execute    produced  for DQL statements like SELECT  or affected  for   DML statements like UPDATE or INSERT     So you could try UPDATE first and INSERT only if rowcount is 0   But depending on activity levels in your database you may hit a race condition between UPDATE and INSERT where another process may create that record in the interim

User · Answer

This is exactly the problem I face and my version is 9 5  And I solve it with SQL query below   INSERT INTO example table  id  name  SELECT 1 AS id   John  AS name FROM example table WHERE NOT EXISTS              SELECT id FROM example table WHERE id   1       LIMIT 1    Hope that will help someone who has the same issue with version    9 5   Thanks for reading

[postgresql] Postgres: INSERT if does not exist already

Examples related to postgresql

Examples related to sql-insert

Examples related to upsert