How to check record if exists before insert with PostgreSQL?

Question

I have a record:

insert into posts(id, title, body) values(1, 'First post', 'Awesome');

If the First post title and Awesome body already exist in the db, I want to ignore it.

When create a table, it's possible to use IF NOT EXISTS syntax. For inserting, is there an easy way?

@Charlieface It's for MySQL. PostgreSQL doesn't have INSERT IGNORE. — Alpin Cleopatra, Sep 01 '22 at 02:15
What about this link then https://dba.stackexchange.com/questions/30499/optimal-way-to-ignore-duplicate-inserts — Charlieface, Sep 01 '22 at 02:17
@Charlieface I found this way: https://koalatea.io/postgres-insert-ignore/ But On CONFLICT(column1, ....) need unique index. It seems it's necessary to create multiple column unique index to do. — Alpin Cleopatra, Sep 01 '22 at 02:32
Not sure why you say that, it depends how many columns you want to check. — Charlieface, Sep 01 '22 at 09:39
Does this answer your question? Insert if NOT EXISTS not working — mustaccio, Sep 01 '22 at 11:47
@mustaccio - this is an answer related to mysql and not postgres — leole, Oct 26 '23 at 13:43

score 3 · Accepted Answer · answered Sep 01 '22 at 03:34

3

if you create a unique key constraint on title & body columns, you can use insert statement as below to ignore if record already exists

insert into posts(id, title, body) values (1, 'First post', 'Awesome') on conflict (title, body) do nothing;

answered Sep 01 '22 at 03:34

Raveendra Lingareddygari

46
2

score 1 · Answer 2 · answered Sep 01 '22 at 00:45

1

It's basically as you say:

insert into posts (id, title, body) 
 select 1, 'First post', 'Awesome' 
 where not exists (
  select null from posts 
  where (title, body) = ('First post', 'Awesome')
 )

You could also define a unique constraint on (title, body) and simply ignore the corresponding exception in your program.

In either case, if your application has more than one thread trying to do the same thing, you'll need to make sure it can handle errors properly.

answered Sep 01 '22 at 00:45

mustaccio

25,896
22
57
72

Why is select null from posts? Isn't it select * from posts? – Alpin Cleopatra Sep 01 '22 at 02:12
Because you don't need anything from that table except the fact that there is (or isn't) a matching row. – mustaccio Sep 01 '22 at 02:13
If use select null, even the record doesn't exist in the table, I can't create it. But select * works. – Alpin Cleopatra Sep 01 '22 at 02:16
@AlpinCleopatra: it's next to impossible that select * makes a difference in a sub-query used with an EXISTS condition. The value is completely ignored (you can even use select 1/0 without it throwing an error) – Sep 01 '22 at 07:41

How to check record if exists before insert with PostgreSQL?

2 Answers2