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@xxxxxxxxxxx
