Problem to insert with conflict and do nothing in postegresql

Question

I want to insert values in a table, but if some values is equal i want the query ignore this line

For example, let's take this table :

A     B     C     D
1   null    2     3
1   null    2     4

insert into table(a,b,c,d)
values(1, null, 2, 5)
on conflict(A, B, C) do nothing

If the values of columns A,B,C are equal to the values in the insert query, then the query shouldn't do anything but this new line is insert and the table update to this: A B C D 1 null 2 3 1 null 2 4 1 null 2 5

But the with the 'do nothing' clause that line shouldn't to insert. I don't know why, but postgres doesn't understand null = null and add the values in the table. How can I make the query don't insert this line?

the question isn't clear. can you please tell clearly what is the problem. Then tell what did you try, and what was the result. This will make things clearer — Abdel, Sep 10 '21 at 19:21
for example I can't understand this part "but postgres doesn't understand null as same value and add the values in the table" — Abdel, Sep 10 '21 at 19:22
I'm comparing two null values, it considers them different, even though they are both null, for the sql null is diferent from other null — Guilherme, Sep 10 '21 at 19:30
it seems this has been already answered here: https://stackoverflow.com/questions/33107470/inserting-unique-values-in-postgresql. Please check it, if you still without answer, I can help — Abdel, Sep 10 '21 at 19:37
I still without answer because o not exists will compare the two nulls and they still be diferents — Guilherme, Sep 10 '21 at 19:44

score 0 · Accepted Answer · answered Sep 10 '21 at 21:57

It is not that postgres doesn't understand null = null. It is your misunderstanding of NULL processing because the expression null = null is not True processing, notice I did not say the result False as it is not false either. NULL introduces 3-state logic, where the result can be True, False, or Null. The result of any comparison with NULL (or containing NULL) is NULL. Basically NULL means I don't know (or somtines I don't care). Let's take an example not completely in the SQL realm.

Image there are 2 bags on the table labeled A and B. In those bags there are some number of coins, but you cannot interact with them other than looking. So to you the values are Null and Null. Then someone asks you "is the value of coins in A = the value of coins in B". Your only answer is I don't know. So basically NULL. Now back in SQL suppose A and B are columns in a table that are Null. When you test A=B (null=null) Postgres is saying NULL; basically saying I don't know.

However, there is a workaround. There are functions that translate NULL into a 'known' value, you could create a constraint on one. But Postgres offers another way around it. With condition is distinct from Postgres considers 2 null values to be the same and Null with not_null to be distinct. With it this you can write a trigger to emulate on conflict do nothing. See demo

create or replace function do_nothing_on_table()
  returns trigger 
  language plpgsql
as $$
begin 
    if exists ( select null 
                  from <table_name> 
                 where (new.col_name, new.col_name, ...>
                       is not distinct from 
                       (col_name, col_name ...) 
              ) 
    then 
        return null;
    end if; 
   
    return new;
end ; 
$$;     
 
create trigger <table_name>_bir
  before insert on  <table_name>
  for each row 
  execute function do_nothing_on_table();

Ok, I thinks thats gone work, but can I put the values?? like this: create trigger _bir before insert vlues (1,null,2,4) on — Guilherme, Sep 13 '21 at 11:36
No. Triggers essentially become part of the table definition. They are automatically executed when the appropriate DML statement executes. Whenever you have questions/concerns about statement structure just keep in mind that the [Documentation](https://www.postgresql.org/docs/13/sql-createtrigger.html) is your friend (and first point of reference) — Belayer, Sep 13 '21 at 17:06

Problem to insert with conflict and do nothing in postegresql

1 Answers1