Often in PostgreSQL you may want to combine two tables that have different columns.
You can use the following basic syntax to do so:
SELECT product, NULL as position, sales
FROM store1
UNION ALL
SELECT product, position, NULL as sales
FROM store2;
This particular example combines the tables named store1 and store2.
In this example, the store1 table has the following columns:
- product
- sales
And the store2 table has the following columns:
- product
- position
Since the two tables do not have the same columns, we artificially create the missing columns in each table and assign a value of NULL to each of these columns.
By doing so, we are able to create the same columns in each table so that when we use the UNION ALL statement we are able to combine the two tables without any errors.
The following example shows how to use this syntax in practice.
Example: How to Combine Two Tables with Different Columns in PostgreSQL
Suppose that we create the following table named store1 that contains information about the total number of sales of various products:
-- create table named 'store1'
CREATE TABLE store1 (
product TEXT,
sales INTEGER
);
-- add rows to the table
INSERT INTO store1 VALUES ('A', 22);
INSERT INTO store1 VALUES ('B', 16);
INSERT INTO store1 VALUES ('C', 40);
INSERT INTO store1 VALUES ('D', 31);
INSERT INTO store1 VALUES ('E', 12);
-- view all rows in table
SELECT * FROM store1;
product | sales
---------+-------
A | 22
B | 16
C | 40
D | 31
E | 12
Then suppose we create another table named store2 that contains information about the position where various products are placed on the shelves at some store:
-- create table named 'store2'
CREATE TABLE store2 (
product TEXT,
position TEXT
);
-- add rows to the table
INSERT INTO store2 VALUES ('F', 'High');
INSERT INTO store2 VALUES ('G', 'High');
INSERT INTO store2 VALUES ('H', 'Low');
INSERT INTO store2 VALUES ('I', 'Middle');
INSERT INTO store2 VALUES ('J', 'Low');
-- view all rows in table
SELECT * FROM store2;
product | position
---------+----------
F | High
G | High
H | Low
I | Middle
J | Low
We can use the following syntax to combine these two tables into one table, despite the fact that they do not have the same columns:
SELECT product, NULL as position, sales
FROM store1
UNION ALL
SELECT product, position, NULL as sales
FROM store2;
product | position | sales
---------+----------+-------
A | | 22
B | | 16
C | | 40
D | | 31
E | | 12
F | High |
G | High |
H | Low |
I | Middle |
J | Low |
Notice that the resulting table contains all rows from both tables.
You can use similar syntax to combine tables that do not have the same columns in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Use LEFT JOIN on Multiple Tables
PostgreSQL: How to Use INNER JOIN with GROUP BY
PostgreSQL: How to Perform an Anti-Join
