Geek Answers Handbook

Postgresql insert if not exist

I am very new to SQL, and all I get is error after error, so any help would be appreciated.

I have a tag table: id, name, slug

I have google'd a search on stackoverflow but nothing works for me. I am trying to create a tag if it does not exist, but always return an identifier if it is created or exists.

INSERT INTO tags (name, slug) SELECT ('Wow', 'wow') WHERE NOT EXISTS (SELECT id FROM tags WHERE slug = 'wow') RETURNING id;

Here is what I have: http://sqlfiddle.com/#!15/4050a/18

+7
sql postgresql
source share
2 answers

Do not put columns in parentheses.

If you look at the full error message you get, Postgres actually tells you what is wrong.

ERROR: INSERT has more target columns than expressions. Tip: The insertion source is a row expression containing the same number of columns expected by INSERT. Have you accidentally used extra parentheses?

The expression ('Wow', 'wow') is just one column, an anonymous “record” with two variables (see the manual for details)

 INSERT INTO tags (name, slug) SELECT 'Wow', 'wow' WHERE NOT EXISTS (SELECT id FROM tags WHERE slug = 'wow') RETURNING id;

In general, adding parentheses is only recommended if they are really needed.

+13
source share

(1) Just remove the brackets. This should solve your problem.

 INSERT INTO tags (name, slug) SELECT 'Wow', 'wow' WHERE NOT EXISTS (SELECT id FROM tags WHERE slug = 'wow') RETURNING id;

(2) Try this. This should also do this (although FROM is not really required, as others have pointed out).

 INSERT INTO tags (name, slug) SELECT 'Wow', 'wow' FROM tags WHERE NOT EXISTS (SELECT id FROM tags WHERE slug = 'wow') LIMIT 1 RETURNING id;
+3
source share

More articles:


All Articles