8

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

I have a tags table: id, name, slug

I've Google'd, searched on Stackoverflow, but nothing works for me. I'm trying to create a tag if it doesn't exist, but always return the ID whether it's created or exists.

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

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

daryl
  • 14,307
  • 21
  • 67
  • 92
  • Possible duplicate of [Postgres: INSERT if does not exist already](https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already) – Gajus Oct 22 '18 at 15:45

2 Answers2

21

Don't put the columns in parentheses.

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

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

The expression ('Wow', 'wow') is just a single 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 it's a good idea to add parentheses only if they are really required

3

(1) Just remove the brackets. That 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. That should also do it (even though a FROM
clause is not actually needed as others 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;
peter.petrov
  • 38,363
  • 16
  • 94
  • 159