Postgresql Union to combine two table

Question

I Have two similar table but new & old.

table01 Old

id	customer	product	quantity
001	Cust001	Soap	200
002	Cust002	Shampoo	23
003	Cust003	Ketchup	30

table01 New

id	customer	product	quantity
002	Cust002	Shampoo	70
003	Cust003	Ketchup	50

what i want to get on union is id 001 from old table, and id 002 - 003 from new table

id	customer	product	quantity
001	Cust001	Soap	200
002	Cust002	Shampoo	70
003	Cust003	Ketchup	50

After i try with union all or simple union it's not show like what i want. How to get the view like my wanted table use case or what should i do after union?

*now, my real table got hundres transaction id

score 1 · Answer 1 · answered Jun 28 '22 at 04:50

demo
PostgreSQL DISTINCT ON with different ORDER BY

WITH cte AS (
(
        SELECT
            *
        FROM
            customer1
        ORDER BY
            quantity)
    UNION ALL (
        SELECT
            *
        FROM
            customer2
        ORDER BY
            quantity))
SELECT DISTINCT ON (id, customer, product)
    *
FROM
    cte
ORDER BY
    1,
    2,
    3,
    4 DESC;

UNION ALL make all the rows from tables is there, then distinct on remove duplicates, using ORDER BY to decide which row is being "saved" in an DISTINCT ON operation.

Postgresql Union to combine two table

1 Answers1