SELECT or INSERT a row in one command

Question

I'm using PostgreSQL 9.0 and I have a table with just an artificial key (auto-incrementing sequence) and another unique key. (Yes, there is a reason for this table. :)) I want to look up an ID by the other key or, if it doesn't exist, insert it:

SELECT id FROM mytable WHERE other_key = 'SOMETHING' 

Then, if no match:

INSERT INTO mytable (other_key) VALUES ('SOMETHING') RETURNING id 

The question: is it possible to save a round-trip to the DB by doing both of these in one statement? I can insert the row if it doesn't exist like this:

INSERT INTO mytable (other_key) SELECT 'SOMETHING' WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING') RETURNING id 

... but that doesn't give the ID of an existing row. Any ideas? There is a unique constraint on other_key, if that helps.

Answer 1

Have you tried to union it?

---

Edit - this requires Postgres 9.1:

create table mytable (id serial primary key, other_key varchar not null unique);  WITH new_row AS ( INSERT INTO mytable (other_key) SELECT 'SOMETHING' WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING') RETURNING * ) SELECT * FROM new_row UNION SELECT * FROM mytable WHERE other_key = 'SOMETHING'; 

results in:

 id | other_key  ----+-----------   1 | SOMETHING (1 row) 

Answer 2

No, there is no special SQL syntax that allows you to do select or insert. You can do what Ilia mentions and create a sproc, which means it will not do a round trip fromt he client to server, but it will still result in two queries (three actually, if you count the sproc itself).