up vote 2 down vote favorite
share [fb]

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?

link|improve this question
95% accept rate
feedback

2 Answers

up vote 2 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';
link|improve this answer
feedback
up vote 0 down vote

I think it's very hard to to it in a single SQL-statments , May be you shoud write a funciton to do this. This is my personal opinions. Because you want to return the id even if the sql turns out a "ERROR: duplicate key value ".

link|improve this answer
feedback

Your Answer

 
or
required, but never shown

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