Re: Copy Bulk Ignore Duplicated

On 6/17/19 9:22 AM, Adrian Klaver wrote:
> On 6/17/19 9:06 AM, Leandro Guimarães wrote:
> Please reply to list also.
> Ccing list.
>> Ugh My bad again.
>>
>> They are UNIQUE:
>> CONSTRAINT unique_const_value_20190501_45 UNIQUE (customer_id, 
>> date_time, indicator_id, element_id),
>>
>> I've made a mistake typing "check constraint" before because these are 
>> partitioned tables and I have the CHECK CONSTRAINT to partition.
>>
>> Is that clear?
> 
> Yes.
> 
> To get back to the original issue, the problem is that when you COPY in 
> new data you may get rows that conflict on the above UNIQUE constraint, 
> correct?

Assuming the above is correct, would not something like below work?:

create table orig_tbl(id integer, fld_1 varchar, fld_2 integer, fld_3 
varchar, CONSTRAINT u_idx UNIQUE(id, fld_1, fld_2));

\d orig_tbl
                    Table "public.orig_tbl"
  Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
  id     | integer           |           |          |
  fld_1  | character varying |           |          |
  fld_2  | integer           |           |          |
  fld_3  | character varying |           |          |
Indexes:
     "u_idx" UNIQUE CONSTRAINT, btree (id, fld_1, fld_2)

create table tmp_tbl(id integer, fld_1 varchar, fld_2 integer, fld_3 
varchar);

insert into orig_tbl values (1, 'test', 3, 'test'), (2, 'foo', 5, 
'bar'), (3, 'cat', 8, 'dog');

select * from orig_tbl ;

  id | fld_1 | fld_2 | fld_3
----+-------+-------+-------
   1 | test  |     3 | test
   2 | foo   |     5 | bar
   3 | cat   |     8 | dog
(3 rows)

insert into tmp_tbl values (1, 'test', 3, 'test'), (4, 'fish', 6, 
'bird'), (7, 'rabbit', 8, 'squirrel'), (10, 'plant', 2, 'animal');

select * from tmp_tbl ;

  id | fld_1  | fld_2 |  fld_3
----+--------+-------+----------
   1 | test   |     3 | test
   4 | fish   |     6 | bird
   7 | rabbit |     8 | squirrel
  10 | plant  |     2 | animal
(4 rows)

select * from tmp_tbl AS tt left join orig_tbl AS ot on (tt.id, 
tt.fld_1, tt.fld_2) = (ot.id, ot.fld_1, ot.fld_2) where ot.id is null;

  id | fld_1  | fld_2 |  fld_3   | id | fld_1 | fld_2 | fld_3
----+--------+-------+----------+----+-------+-------+-------
   4 | fish   |     6 | bird     |    |       |       |
   7 | rabbit |     8 | squirrel |    |       |       |
  10 | plant  |     2 | animal   |    |       |       |

> 
>>
>> Thanks for your patience!
>> Leandro Guimarães
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com