CODEWITHSUNDEEP
sqlpostgresql
daryl
daryl

Reputation: 15197

Postgresql insert if not exists

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

Upvotes: 8

Views: 30589

Answers (2)

peter.petrov
peter.petrov

Reputation: 39437

(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;

Upvotes: 3

user330315
user330315

Reputation:

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

Upvotes: 21

Related Questions