Insert if not exists, else return id in postgresql

Question

I have a simple table in PostgreSQL that has three columns:

• id serial primary key
• key varchar
• value varchar

I have already seen this question here on SO: Insert, on duplicate update in PostgreSQL? but I'm wondering just how to get the id if it exists, instead of updating. If the standard practice is to always either "insert" or "update if exists", why is that? Is the cost of doing a SELECT (LIMIT 1) greater than doing an UPDATE?

I have the following code

INSERT INTO tag     ("key", "value")     SELECT 'key1', 'value1' WHERE     NOT EXISTS (         SELECT id,"key","value" FROM tag WHERE key = 'key1' AND value = 'value1'     ); 

which works in the sense that it doesn't insert if exists, but I'd like to get the id. Is there a "RETURNING id" clause or something similar that I could tap in there?

Answer 1

Yes there is returning

INSERT INTO tag ("key", "value") SELECT 'key1', 'value1' WHERE NOT EXISTS (     SELECT id, "key", "value"     FROM node_tag     WHERE key = 'key1' AND value = 'value1'     ) returning id, "key", "value" 

To return the row if it already exists

with s as (     select id, "key", "value"     from tag     where key = 'key1' and value = 'value1' ), i as (     insert into tag ("key", "value")     select 'key1', 'value1'     where not exists (select 1 from s)     returning id, "key", "value" ) select id, "key", "value" from i union all select id, "key", "value" from s 

If the row does not exist it will return the inserted one else the existing one.

BTW, if the pair "key"/"value" makes it unique then it is the primary key, and there is no need for an id column. Unless one or both of the "key"/"value" pair can be null.