If a row does not exists insert else don't insert in postgres

Question

I need to check if a row exists or not. If it does not exist, it should be inserted.

This is in postgres and I am trying to insert row through a shell script. When I run the script it does not show error but it does not insert into table even though no matching row is present.

Please post the script you are using and the statement you are using to test if there is "no matching row". — Aushin
– Aushin, Commented Mar 4, 2013 at 15:08
if you can use Ruby as your scripting language, you might try Upsert — Seamus Abshere
– Seamus Abshere, Commented Mar 4, 2013 at 17:58
I'm upset (not to be confused with upsert) that many of you have disregarded the distinction between insert-if-not-exists (asked by this question) and upsert (asked by the erroneously-marked duplicate question). — ma11hew28
– ma11hew28, Commented Mar 14, 2014 at 14:55
This is not a duplicate of the linked question; its answers aren't exactly applicable here. It may be a duplicate of a different question. — Michael Hampton
– Michael Hampton, Commented Aug 11, 2014 at 4:04

Community · Accepted Answer · 2017-05-23 12:25:51Z

28

I like the solution they mention here

INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Mar 4, 2013 at 20:24

Andres Olarte

4,3803 gold badges26 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JumpOffBox Over a year ago

I tried this solution and it is not working as have a lower version. Hence, I wanted to split both of them into two different queries.

The Coder Over a year ago

Postgres also now has an Upsert command where you can insert OR update a row: wiki.postgresql.org/wiki/UPSERT

tim-phillips Over a year ago

On 9.5+, INSERT ... ON CONFLICT DO NOTHING or INSERT ... ON CONFLICT UPDATE stackoverflow.com/a/17267423/1386245

Tiago Over a year ago

@Rohmer note: rohmer's solution only works if you have a constraint on the table

Collectives™ on Stack Overflow

If a row does not exists insert else don't insert in postgres

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related