Take the 2-minute tour ×
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:

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?

share|improve this question

3 Answers 3

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 $$ 
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'; 
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

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

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

Your Answer

 
discard

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.