Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - 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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...