Using PostgreSQL 10, I have two tables (say T1
and T2
) where I would like to INSERT into columns A
and B
of table T1
records using columns D
and E
of table T2
while satisfying the UNIQUE constraint of column A
on table T1
.
Basically, ...
- Find records in table
T2
where a value is present in columnD
of tableT2
that is not present in columnA
of tableT1
. - Take at most one record for each distinct column
D
value identified in (1) (doesn't matter which one if multiple records exist) and insert it (along with one or more other columns from tableT2
) into tableT1
.
Table illustration:
T1:
UID : primary key, autoassigned by database sequence
A : Unique constraint (distinct values from column D of Table T2)
B : Data field #1
C : Data field #2
T2:
UID : primary key
D : Multiple values
E : Data field #1
F : Data field #3
Based on research thus far and searches of somewhat similar questions, I believe that I'm looking for something akin to the following:
INSERT INTO T1 ("A","B")
SELECT T2."D", T2."E"
FROM T2 LEFT INNER JOIN LATERAL (...) ON (...)
WHERE T1."UID" IS NULL;
I'm unclear how to formulate the query to perform the desired result. For example, given the following data, I'd like the query to INSERT either {w,e} or {w,g} into columns {A,B} of Table #1:
T1: T2:
UID | A | B | C UID | D | E | F
-----+---+---+--- -----+---+---+---
0 x a b 100 w e f
1 y c d 200 w g h
300 x a i
400 y c j
T1: (post-transaction):
UID | A | B | C
-----+---+-----+---
0 x a b
1 y c d
2 w e|g NULL
Thank you in advance.
Update (2018-08-06 9:40 AM EDT): Changed post-transaction value in column C of T1 for newly inserted record to NULL to avoid ambiguity.
k
come from in the inserted record? Can it bew g k
andw e k
or only or just one of the two values? What logic decides which value gets inserted?