On conflict do nothing with a custom constraint-CodePudding

I need to do the following:

insert into table_a (primarykey_field, other_field)
select primarykey_field, other_field from table_b b
on conflict (primarykey_field) where primarykey_field >>= b.primarykey_field do nothing;

Nevermind the operation of my where condition it could be anything except a simple equal. in mycase I'm using a custom ip range field soI I want to check that one ip address is not in the range of the other ip adderss when I'm inserting a new row.

Is there a way I can do this with on conflict or with another query?

CodePudding user response：

You can filter out all rows which have a pkey_ip_range that's already contained by an existing pkey_ip_range:

insert into table_a as a (
    pkey_ip_range, 
    other_field)
select  pkey_ip_range, 
        other_field 
from table_b b
where not exists (
    select 1 
    from table_a 
    where b.pkey_ip_range >>= table_a.pkey_ip_range);

If you wanted to check if the incoming ip range either contains or is contained by the existing ip range (&& rather than >>=), you can use an exclusion constraint:

drop table if exists table_a;
create table table_a (
    pkey_ip_range inet primary key, 
    other_column text);
alter table table_a
add constraint table_a_no_contained_ip_ranges
exclude using gist (pkey_ip_range inet_ops WITH &&);

insert into table_a 
(pkey_ip_range,other_column) 
values ('192.168.0.0/31','abc');

insert into table_a 
(pkey_ip_range,other_column) 
values ('192.168.0.0/30','def');
--ERROR:  conflicting key value violates exclusion constraint "table_a_no_contained_ip_ranges"
--DETAIL:  Key (pkey_ip_range)=(192.168.0.0/30) conflicts with existing key (pkey_ip_range)=(192.168.0.0/31).

insert into table_a 
(pkey_ip_range,other_column) 
values ('192.168.0.0/32','ghi') 
on conflict do nothing;
--table table_a;
-- pkey_ip_range  | other_column
------------------ --------------
-- 192.168.0.0/31 | abc
--(1 row)