9

I have a table:

CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY)

Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the id. How can I do this with PostgreSQL?

I have seen a few scripts for this, but is there no single SQL-statement to do it?

E.g. I can INSERT and return id with:

INSERT INTO mytable (name) VALUES ('Jonas') RETURNING id

it works the first time and returns id. But it fails if Jonas already exist in the table, but I want to return the id even if the Insert fails. Is this possible to do with PostgreSQL?

4 Answers 4

8

Upsert statements used to be planned for 9.1 but have been postponed to 9.2, so until then, your only choice is to test if the value already exists before inserting.

Alternatively, if your intention is merely to have a unique identifier, you could simply use a sequence + nextval.


If you want to create a function to do that, this should get you started:

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$ 
DECLARE 
BEGIN 
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2; 
    IF NOT FOUND THEN 
    INSERT INTO tableName values (value, arg1, arg2); 
    END IF; 
END; 
$$ LANGUAGE 'plpgsql'; 
2
  • Is upsert still planned for 9.2? Any details on this? Mar 27, 2012 at 5:03
  • I don't see it in the 9.2 wiki so far :(
    – wildpeaks
    Mar 27, 2012 at 10:41
5

If you are on 9.1 there is a kind of workaround for this using writeable CTEs

http://xzilla.net/blog/2011/Mar/Upserting-via-Writeable-CTE.html

0

I think it's very hard to do it in a single SQL-statement. Maybe you should write a function to do this. This is my personal opinion. Because you want to return the id even if the sql turns out an "ERROR: duplicate key value ".

0

I like simply catching the unique_violation exception as proposed here:

Optimal way to ignore duplicate inserts?

Ignore Duplicates #1

Create a transaction that catches unique constraint violations, taking no action:

  BEGIN
    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
  EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.
  END;

The beauty of this solution is that it does not suffer the race conditions. You'd have to add code to the exception handler to look up the id.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.