Wednesday 1 July 2009

How to union tables in PostgreSQL

This query unions two tables which contain the same columns thus works as table aggregate:
CREATE TABLE total_stats AS
SELECT order_nr
, transaction_date
, price_usd FROM january_stats
UNION
SELECT order_nr
, transaction_date
, price_usd FROM february_stats;


This query, however, will union tables where column structure not necessarily match:
CREATE TABLE total_stats AS
SELECT order_nr
, customer_id
, transaction_date
, price_usd FROM january_stats
UNION
SELECT order_nr
, (SELECT 'unknown' AS customer_id)
, transaction_date
, fare_usd AS price_usd FROM february_stats;


More then two tables can be united similarly.

No comments: