Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table:


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?

share|improve this question

4 Answers 4

up vote 6 down vote accepted

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 $$ 
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2; 
    INSERT INTO tableName values (value, arg1, arg2); 
    END IF; 
$$ LANGUAGE 'plpgsql'; 
share|improve this answer
Is upsert still planned for 9.2? Any details on this? – Joe Van Dyk Mar 27 '12 at 5:03
I don't see it in the 9.2 wiki so far :( – wildpeaks Mar 27 '12 at 10:41

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

share|improve this answer

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 ".

share|improve this answer

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:

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

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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