SQL to update table with records from second table (but only those with distinct value in a given column)

Question

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, ...

1. Find records in table T2 where a value is present in column D of table T2 that is not present in column A of table T1.
2. 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 table T2) into table T1.

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.

Answer 1

Use NOT EXISTS to check that no matching record already exists in t1 and the row_number() window function to pick only one row per t2.d.

INSERT INTO t1
            (a,
             b)
            SELECT x.d,
                   x.e
                   FROM (SELECT t21.d,
                                t21.e,
                                row_number() OVER (PARTITION BY t21.d) rn
                                FROM t2 t21
                                WHERE NOT EXISTS (SELECT *
                                                         FROM t1 t11
                                                         WHERE t11.a = t21.d)) x
                   WHERE x.rn = 1;

I don't know how t1.c should be calculated, that's not clear from your post. So I left this part out. But maybe this helps to solve the main problem.