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(dot)klaver(at)aklaver(dot)com