10

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.

JumpOffBox
  • 703
  • 3
  • 10
  • 19
  • 6
    Please post the script you are using and the statement you are using to test if there is "no matching row". – Aushin Mar 04 '13 at 15:08
  • 1
    See this link http://stackoverflow.com/questions/4555966/merge-syntax-used-to-upsert-or-insert-on-duplicate-update – Kuberchaun Mar 04 '13 at 15:09
  • if you can use Ruby as your scripting language, you might try [`Upsert`](https://github.com/seamusabshere/upsert) – Seamus Abshere Mar 04 '13 at 17:58
  • 4
    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 Mar 14 '14 at 14:55
  • 1
    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](http://stackoverflow.com/q/4069718/1068283). – Michael Hampton Aug 11 '14 at 04:04
  • This is absolutely not a duplicate of the linked question. Please vote to re-open. – Myles McDonnell Aug 11 '16 at 10:21

1 Answers1

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);
Community
  • 1
  • 1
Andres Olarte
  • 4,380
  • 3
  • 24
  • 45
  • 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. – JumpOffBox Mar 04 '13 at 21:20
  • Postgres also now has an Upsert command where you can insert OR update a row: https://wiki.postgresql.org/wiki/UPSERT – The Coder Aug 17 '17 at 21:42
  • 5
    On 9.5+, `INSERT ... ON CONFLICT DO NOTHING` or `INSERT ... ON CONFLICT UPDATE` https://stackoverflow.com/a/17267423/1386245 – tim-phillips Dec 07 '17 at 21:31
  • @Rohmer note: rohmer's solution only works if you have a constraint on the table – Tiago Dec 20 '22 at 15:29