2

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.

2
  • And where does the value k come from in the inserted record? Can it be w g k and w e k or only or just one of the two values? What logic decides which value gets inserted?
    – John K. N.
    Aug 6, 2018 at 7:12
  • Sorry - thought about that last night as going to sleep - 'k' can be assumed to be NULL.
    – Whee
    Aug 6, 2018 at 13:00

1 Answer 1

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.

1
  • This worked perfectly, and have updated the question to remove the ambiguity for t1.c value. Marking as answer as it is the first answer that accomplishes the expected output. Really appreciate it!
    – Whee
    Aug 6, 2018 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.