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?
Best Answer
Yes there is
returning
To return the row if it already exists
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.