PostgreSQL UNION Clause

PostgreSQL UNION Clause: Combine Results from Multiple Queries


Ever find yourself in a situation where you want to pull together results from separate queries into a single result set? Well, the UNION clause in PostgreSQL is your friend here. In this guide, we’ll dive into how to use the UNION clause effectively, giving you the tips you need to avoid common mistakes and optimize your queries.

Basic Syntax and Usage

The fundamental syntax of a UNION query involves two or more SELECT queries separated by the UNION keyword.

SELECT column1, column2 FROM table1
SELECT column1, column2 FROM table2;

Here, column1 and column2 must be of the same data type in both table1 and table2.

Example with tv_series Table

Assume we have a tv_series table and another table called web_series. We’ll fetch names of series from both tables that have been released after 2010.

SELECT name FROM tv_series WHERE release_year > 2010
SELECT name FROM web_series WHERE release_year > 2010;
Peaky Blinders
Stranger Friends
Big Little Lies

web_series Table

Let us create a new table called web_series and seed it with some data. This table will be used by the queries below.

-- Create web_series table
CREATE TABLE public.web_series (
    id serial4 NOT NULL,
    "name" text NOT NULL,
    genre text NOT NULL,
    seasons int4 NULL DEFAULT 1,
    is_running bool NULL DEFAULT true,
    release_year int4 NULL,
    CONSTRAINT web_series_pkey PRIMARY KEY (id)

-- Seed web_series table with sample data
INSERT INTO public.web_series (name, genre, seasons, is_running, release_year) VALUES
('Stranger Friends', 'Drama', 3, true, 2019),
('Coder Life', 'Comedy', 2, true, 2020),
('Dark Web', 'Thriller', 4, false, 2018),
('Cooking Masters', 'Reality', 5, true, 2015),
('Treasure Hunt', 'Adventure', 1, false, 2021);

-- query new table
SELECT * FROM web_series;
id name genre seasons is_running release_year
1 Stranger Friends Drama 3 true 2019
2 Coder Life Comedy 2 true 2020
3 Dark Web Thriller 4 false 2018
4 Cooking Masters Reality 5 true 2015
5 Treasure Hunt Adventure 1 false 2021

Common Flags and Usage Patterns

  • UNION vs UNION ALL: By default, UNION removes duplicate rows. If you want to keep them, use UNION ALL.

    SELECT name FROM tv_series WHERE genre = 'Drama'
    SELECT name FROM web_series WHERE genre = 'Drama';
Breaking Bad
The Crown
Stranger Friends
  • Ordering Results: Use ORDER BY at the end to sort the final result set.

    SELECT name FROM tv_series WHERE genre = 'Comedy'
    SELECT name FROM web_series WHERE genre = 'Comedy'
    ORDER BY name ASC;
Brooklyn Nine-Nine
Coder Life
Modern Family
Orange is the New Black
Ted Lasso
The Good Place
The Marvelous Mrs. Maisel
The Office

Common Errors and Solutions

Error Message:

ERROR:  each UNION query must have the same number of columns

This error occurs when the SELECT queries combined using UNION don’t have the same number of columns.

Make sure that each SELECT statement within the UNION has the same number of columns, and those columns are of compatible data types.

Pro Tips

  • Use UNION ALL when you’re sure that the queries won’t have duplicate rows or when duplicates are acceptable. It’s faster because it doesn’t have to check for duplicates.

  • Be mindful of the data types for each column in the SELECT queries. They must be compatible for the UNION to work.

Leave a Reply