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?
Clodoaldo Neto :
Yes there is returning\n\nINSERT INTO tag (\"key\", \"value\")\nSELECT 'key1', 'value1'\nWHERE NOT EXISTS (\n SELECT id, \"key\", \"value\"\n FROM node_tag\n WHERE key = 'key1' AND value = 'value1'\n )\nreturning id, \"key\", \"value\"\n\n\nTo return the row if it already exists\n\nwith s as (\n select id, \"key\", \"value\"\n from tag\n where key = 'key1' and value = 'value1'\n), i as (\n insert into tag (\"key\", \"value\")\n select 'key1', 'value1'\n where not exists (select 1 from s)\n returning id, \"key\", \"value\"\n)\nselect id, \"key\", \"value\"\nfrom i\nunion all\nselect id, \"key\", \"value\"\nfrom s\n\n\nIf the row does not exist it will return the inserted one else the existing one.\n\nBTW, 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.",
2013-08-12T17:14:58
Roman Pekar :
with vals as (\n select 'key5' as key, 'value2' as value\n)\ninsert into Test1 (key, value)\nselect v.key, v.value\nfrom vals as v\nwhere not exists (select * from Test1 as t where t.key = v.key and t.value = v.value)\nreturning id\n\n\nsql fiddle demo",
2013-08-12T17:30:03