INSERT, if does not exist, else return id

Question

There are some limitations:

• Unable to modify database
• Columns are not unique.
• It is required to return the last insert identifier ( RETURNING id )
• If exists, return the existing id
• It will be called through our db user library (the values ​​in select will be as parameters from PHP (?,?,?,!))

 INSERT INTO tags (name, color, groupid) SELECT 'test', '000000', 56 WHERE NOT EXISTS ( SELECT text FROM tags WHERE name = 'test' AND groupid = 56 ) RETURNING id 

This works - up to the point where I need to get an existing identifier as well. With this, I only get the inserted identifier. Is it possible to return the value of a SELECT statement if it is not inserted?

UPDATE:

 DO $$ BEGIN IF NOT EXISTS ( SELECT text FROM tags WHERE name = 'test' AND groupid = 56 ) THEN INSERT INTO tags (name, color, groupid) VALUES ('test', '000000', 56) RETURNING id; ELSE RETURN SELECT text FROM tags WHERE name = 'test' AND groupid = 56; END IF; END $$ 

It was checked with this format - however, this ends with several errors:

 RETURN cannot have a parameter in function returning void 

Answer 1

You can do this using CTE.

The cte information is raw, so replace the values ​​there with your placeholders for PHP.

CTE will return the results from the first half of the union if an existing record exists (the old identifier will be displayed), and the second half if the insert was performed (the new identifier will be displayed).

 WITH info (name, color, groupid) AS (values('test','000000',56)), trial AS ( INSERT INTO tags(name, color, groupid) SELECT info.name, info.color, info.groupid FROM info WHERE NOT EXISTS ( SELECT * FROM tags t2 WHERE t2.name = info.name AND t2.groupid= info.groupid) RETURNING id) SELECT tags.id FROM tags INNER JOIN info ON tags.name = info.name AND tags.groupid= info.groupid UNION ALL SELECT trial.id FROM trial; 

SQL script example: http://sqlfiddle.com/#!15/a7b0f/2

Postgres man page for using CTE

http://www.postgresql.org/docs/9.1/static/queries-with.html

Answer 2

You can use the IF statement for this.

 IF NOT EXISTS(SELECT text FROM tags WHERE name = 'test') BEGIN INSERT INTO tags (name, color, groupid) VALUES ('test', '000000', 56) SET @textId = SCOPE_IDENTITY() END ELSE BEGIN SELECT @textId = ID FROM tags WHERE name = 'test' END 

You must make the necessary changes to the above request in accordance with your requirements.

Answer 3

if name is unique:

 INSERT INTO `tags` (name, color, groupid) VALUES ('test', '000000', 56) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), `name`='test'; SELECT LAST_INSERT_ID();