Question 90085 in PostgreSQL Question posted in
The official documentation can be found here.

PostgrSQL UNION by key – Postgresql

Can’t come up with easy solution how to union tables with same columns but from second table add only rows which are present in first table.

t1:

id A B C
1 xx r g
2 cc r g
5 d g e

t2:

id A B C
101 jyj u j
5 y jku u
12 y r j

desired t1 union t2:

id A B C
1 xx r g
2 cc r g
5 d g e
5 y jku u

Appreciate any help.

Tags:

4

Answers


  1. 0

    We can try the following union approach with the help of window functions:

    WITH cte AS (
    SELECT id, A, B, C, 1 AS src FROM t1
    UNION ALL
    SELECT id, A, B, C, 2 FROM t2
),
cte2 AS (
    SELECT *, MIN(src) OVER (PARTITION BY id) AS min_src,
              MAX(src) OVER (PARTITION BY id) AS max_src
    FROM cte
)

SELECT id, A, B, C
FROM cte2
WHERE src = 1 OR min_src <> max_src
ORDER BY id, src;
    Login or Signup to reply.
  2. 0

    Something like:

    SELECT id, a, b, c
from t1
union all
SELECT id, a, b, c
from t2
where exists (select *
              from t1
              where t1.id = t2.id);
    Login or Signup to reply.
  3. 0

    Something like this:

    SELECT t1.id, t1.A, t1.B, t1.C
FROM t1
FULL OUTER JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NOT NULL
    Login or Signup to reply.
  4. 0

    We can use an IN clause and say the id of table 2 must appear in table 1:

    SELECT id, A, B, C
FROM t1
UNION ALL
SELECT id, A, B, C
FROM t2
WHERE t2.id IN (SELECT id FROM t1);

    Try out: db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search