Insert if does not exist, otherwise return id in postgresql

Question

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

• id primary key
• varchar key
• varchar value

I already saw this question here on SO: Insert, when duplicating updates in PostgreSQL? but I am wondering how to get the identifier, if it exists, instead of updating. If the standard practice is to always “embed” or “update if exists”, why? Is the cost of executing SELECT (LIMIT 1) more than performing 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 does not insert if exists, but I would like to get an identifier. Is there a "RETURNING id" sentence or something similar that I could click 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 a string 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 other existing one.

BTW, if the key / value pair makes it unique, then this is the primary key, and there is no need for an id column. If one or both of the key / value pairs cannot be empty.

Answer 2

 with vals as ( select 'key5' as key, 'value2' as value ) insert into Test1 (key, value) select v.key, v.value from vals as v where not exists (select * from Test1 as t where t.key = v.key and t.value = v.value) returning id 

sql script demonstration

Answer 3

And you can save the value returned by the variables in the form ... RETURNING field1, field2, ... INTO var1, var2, ...

RETURNING typically returns a query that would return a query query error for the final data if you call it in plpgsql without using its returned result set.