Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 90 ยท Getting Started with PostgreSQL

A Tour of PostgreSQL SQL

A quick tour of what PostgreSQL SQL adds to the basics: RETURNING, ILIKE, the :: cast, generate_series, DISTINCT ON, FILTER and LATERAL, and what is portable.

PostgreSQL speaks standard SQL, so most of what you already know works unchanged. On top of that it has a handful of conveniences that make everyday queries shorter, plus a few powerful features that you will not find in other databases. This lesson is a fast tour of the ones you will meet most often. None of them is explained in full here: each gets its own lesson later, and the goal now is to recognize them and to know which are portable and which are not.

The bookstore sample data from the SQL course is used throughout: authors, books, customers and orders. The parts that are portable standard SQL run live in your browser. The parts that only PostgreSQL has are read-only blocks marked -- PostgreSQL, and each one is paired with a live SQLite equivalent wherever one exists.

Seven things worth knowing early

RETURNING Get the changed rows back from INSERT, UPDATE, DELETE.
ILIKE Case-insensitive pattern matching.
:: A short way to cast a value to a type.
generate_series Produce rows of numbers or dates.

Next: three that change how you write grouped queries.

1 / 2

RETURNING: see what you changed

Normally INSERT, UPDATE and DELETE just report how many rows they touched. Adding RETURNING makes them return the affected rows like a SELECT would. This is how you learn the id that the database generated, without a second query:

SQL
-- PostgreSQL
INSERT INTO books (title, author_id, genre, published, price)
VALUES ('Lantern Season', 3, 'Poetry', 2024, 10.50)
RETURNING id, title, price;

UPDATE books SET stock = stock + 10 WHERE stock = 0
RETURNING id, title, stock;
-- two rows: Northern Ledger and Islands Without Names, each with stock 10

RETURNING is the standard way to get generated keys in PostgreSQL, and it can return any expression. SQLite supports it too (3.35 and later), so you can try it live. A second PostgreSQL-flavored feature, FILTER, is also standard SQL and also works in SQLite:

SQL RETURNING and FILTER ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Both queries are valid PostgreSQL as written. The new book gets id 11, and the FILTER clause computes three different numbers in one pass: all books, sold-out books and an average over recent books only. Mystery shows an empty average because none of its books were published since 2015, so the filtered average has nothing to average and returns NULL. FILTER is a cleaner replacement for the SUM(CASE WHEN ... THEN 1 ELSE 0 END) trick; the dedicated lesson is FILTER, GROUPING SETS, CUBE and ROLLUP.

ILIKE and friends: text matching

LIKE is case-sensitive in PostgreSQL (unlike MySQL's default). ILIKE ignores case, and ~ and ~* match regular expressions:

SQL
-- PostgreSQL
SELECT title FROM books WHERE title ILIKE '%map%';
--         title
-- ---------------------
--  A Map of Small Things

SELECT title FROM books WHERE title ~ '^(The|A) ';        -- case-sensitive regex
SELECT title FROM books WHERE title ~* 'ISLANDS?';        -- case-insensitive regex

SQLite's LIKE happens to be case-insensitive for plain English letters, so the live equivalent is simply LIKE with a lowercase or uppercase pattern. The text-matching toolbox, including regular expressions and fuzzy matching, is the subject of Pattern Matching and Regular Expressions.

The :: cast operator

You can convert a value to another type with the standard CAST(value AS type), or with PostgreSQL's shorthand value::type. They mean exactly the same thing:

SQL
-- PostgreSQL
SELECT '42'::integer + 1        AS answer,
       '2024-03-02'::date + 30  AS later,
       3.99::integer            AS rounded;
--  answer |   later    | rounded
-- --------+------------+---------
--      43 | 2024-04-01 |       4

Notice two behaviors: adding an integer to a date adds days, and casting 3.99 to an integer rounds to 4 rather than truncating to 3. The standard form CAST(x AS integer) is portable, so prefer it in code that might move to another database, and keep :: for interactive work and PostgreSQL-only code. Type conversion and the data types themselves start in Numeric Types.

generate_series: rows out of thin air

generate_series is a function that returns a table, so it goes in FROM. It produces a run of numbers or timestamps, which is handy for test data, calendars and filling gaps in reports:

SQL
-- PostgreSQL
SELECT n FROM generate_series(1, 5) AS n;            -- 1, 2, 3, 4, 5
SELECT n FROM generate_series(0, 10, 5) AS n;        -- 0, 5, 10

SELECT d::date AS day
FROM generate_series('2025-01-01'::date, '2025-01-03'::date, interval '1 day') AS d;
--     day
-- ------------
--  2025-01-01
--  2025-01-02
--  2025-01-03

SQLite builds without this function, but a recursive CTE does the same job and is portable standard SQL, and this one also shows the standard CAST:

SQL A portable series, casts and top-per-genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Careful with the last column of the first result: CAST(7.5 AS INTEGER) in SQLite truncates to 7, whereas PostgreSQL rounds to 8. The same function name does not always mean the same behavior across engines, which is a good reason to test on your target database. The full story is in generate_series and Set-Returning Functions.

DISTINCT ON: the first row of each group

The second query above, the most expensive book per genre, needs a window function in standard SQL. PostgreSQL has a shortcut. DISTINCT ON (expression) keeps only the first row for each distinct value of the expression, where "first" is decided by the ORDER BY:

SQL
-- PostgreSQL
SELECT DISTINCT ON (genre) genre, title, price
FROM books
ORDER BY genre, price DESC;
--   genre   |         title         | price
-- ----------+-----------------------+-------
--  Children | A Map of Small Things |  8.50
--  Fiction  | Rivers of Glass       | 16.50
--  Mystery  | Cold Harbour Accounts | 12.75
--  Poetry   | Paper Lanterns        |  9.99
--  Science  | The Quiet Algorithm   | 24.00
--  Travel   | Islands Without Names |  17.40

That is exactly the output of the portable ROW_NUMBER query you just ran (each live example starts from a fresh database, so the extra book from the earlier example is not there). The ORDER BY must start with the same expressions as DISTINCT ON, then add the tie-breaker (price DESC here). The details and pitfalls are in DISTINCT ON and Top-N per Group.

LATERAL: a subquery that sees its neighbor

Normally a subquery in FROM cannot refer to the other tables beside it. Add the keyword LATERAL and it can, as if it ran once per row. That makes "top N per row" queries natural:

SQL
-- PostgreSQL
SELECT a.name, top.title, top.price
FROM authors AS a
CROSS JOIN LATERAL (
  SELECT b.title, b.price
  FROM books AS b
  WHERE b.author_id = a.id
  ORDER BY b.price DESC
  LIMIT 1
) AS top
ORDER BY a.name;
--       name       |         title         | price
-- -----------------+-----------------------+-------
--  Aiko Tanabe     | The Quiet Algorithm   | 24.00
--  Daniel Brook    | Weather for Beginners | 19.95
--  Lucรญa Ferreyra  | Tango at Midnight     | 13.25
--  Mira Okafor     | Rivers of Glass       | 16.50
--  Tomas Lindqvist | Cold Harbour Accounts | 12.75

For every author, the inner query runs with that author's id and returns the priciest book. Change LIMIT 1 to LIMIT 3 and you get each author's three priciest books with no other changes. LATERAL has its own lesson: LATERAL Joins.

Combining queries: UNION, INTERSECT, EXCEPT and their ALL forms

A join adds columns; a set operation stacks the rows of two queries. Both queries need the same number of columns with compatible types, and the column names come from the first query. PostgreSQL has three operators, each in a plain form that removes duplicates and an ALL form that keeps them:

Operator Plain form returns ALL form returns
UNION Rows in either query, each once Every row from both, duplicates kept
INTERSECT Rows found in both, each once Rows found in both, repeated as many times as the smaller count
EXCEPT Rows of the first query not in the second, each once The first query's count minus the second's

The next example uses two tiny tables, a holding 1, 1, 1, 2, 3 and b holding 1, 1, 2, 2, 4. The plain forms run live (SQLite supports them):

SQL UNION, INTERSECT and EXCEPT

Press Run (or Ctrl + Enter) to see the result here.

You get 1,2,3,4 for UNION, ten values for UNION ALL, 1,2 for INTERSECT and 3 for EXCEPT. SQLite has no INTERSECT ALL or EXCEPT ALL; PostgreSQL does, and these are its results on the same data:

SQL
SELECT v FROM a INTERSECT ALL SELECT v FROM b ORDER BY v;   -- 1, 1, 2     (1 appears 3 times in a, 2 in b: min = 2)
SELECT v FROM a EXCEPT ALL SELECT v FROM b ORDER BY v;      -- 1, 3        (three 1s minus two 1s leaves one)
SELECT v FROM a EXCEPT SELECT v FROM b ORDER BY v;          -- 3           (plain EXCEPT drops every 1)

Rules to remember in PostgreSQL:

  • UNION ALL is cheaper. Plain UNION must sort or hash the result to remove duplicates. Use ALL when duplicates are impossible or harmless.
  • Types must line up. SELECT 1 UNION SELECT 'x' fails with invalid input syntax for type integer: "x" because PostgreSQL tries to turn the text into the first column's type; and a different column count gives each UNION query must have the same number of columns.
  • INTERSECT binds tighter than UNION and EXCEPT, as the standard says. SELECT 1 UNION SELECT 2 INTERSECT SELECT 2 means 1 UNION (2 INTERSECT 2) and returns 1 and 2. SQLite evaluates the same text left to right and returns only 2, so use parentheses whenever you mix operators: (SELECT 1 UNION SELECT 2) INTERSECT SELECT 2 returns just 2 everywhere.
  • ORDER BY and LIMIT apply to the whole result. To limit each side, wrap each query in parentheses: (SELECT v FROM a ORDER BY v DESC LIMIT 1) UNION ALL (SELECT v FROM b ORDER BY v LIMIT 1) returns 3 and then 1.
  • NULLs count as equal when removing duplicates, so SELECT NULL UNION SELECT NULL returns a single row.

ANY, ALL and SOME with subqueries

x > ANY (subquery) is true when x beats at least one value the subquery returns, and x > ALL (subquery) when it beats every value. SOME is a synonym of ANY. With the table g of books (Poetry 12.00 and 15.50, Fiction 14.00, 16.00 and 9.00), PostgreSQL returns:

SQL
SELECT id FROM g WHERE genre = 'Fiction'
  AND price > ALL (SELECT price FROM g WHERE genre = 'Poetry');    -- 4          (16.00 beats both)
SELECT id FROM g WHERE genre = 'Fiction'
  AND price > ANY (SELECT price FROM g WHERE genre = 'Poetry');    -- 3, 4       (14.00 beats 12.00)
SELECT id FROM g WHERE price = ANY (SELECT price FROM g WHERE genre = 'Poetry');   -- 1, 2   same as IN
SELECT id FROM g WHERE price <> ALL (SELECT price FROM g WHERE genre = 'Poetry');  -- 3, 4, 5  same as NOT IN

> ALL is the same as comparing with MAX, and > ANY the same as comparing with MIN, with one difference for an empty subquery: > ALL (empty) is true for every row and > ANY (empty) is false. If the subquery contains a NULL, ALL and NOT IN can turn the result into "unknown", exactly like the NOT IN trap.

PostgreSQL also lets ANY and ALL take an array instead of a subquery, which is how most application code uses them:

SQL
SELECT id FROM g WHERE id = ANY ('{1,3,9}');            -- 1, 3     the ids that exist
SELECT id FROM g WHERE genre = ANY (ARRAY['Poetry']);   -- 1, 2
SELECT id, price FROM g WHERE price > ALL (ARRAY[12, 15]);   -- 2 (15.50) and 4 (16.00)

A single = ANY ($1) with an array parameter replaces an IN (...) list whose length changes from call to call, which also keeps the prepared statement the same (Prepared Statements). More array operators are in Arrays.

What is portable and what is not

When you write SQL that might run elsewhere, it helps to know which category a feature falls in:

Feature Standard / portable? Notes
RETURNING Not standard, but also in SQLite, MariaDB, Oracle (different form) Not in MySQL
FILTER (WHERE ...) Standard SQL Also in SQLite 3.30+
Window functions, CTEs Standard SQL Work almost everywhere
CAST(x AS type) Standard SQL Rounding and truncation rules differ by engine
LATERAL Standard SQL MySQL 8, SQL Server (APPLY) have equivalents
x::type PostgreSQL only Use CAST in portable code
ILIKE, ~, ~* PostgreSQL only Other engines use LOWER(), REGEXP
DISTINCT ON PostgreSQL only Use ROW_NUMBER() elsewhere
generate_series PostgreSQL only Recursive CTE works elsewhere

Using PostgreSQL-only features is perfectly fine when you have decided to commit to PostgreSQL, which most teams eventually do. Just do it on purpose, not by accident.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does adding RETURNING id to an INSERT statement do?

    RETURNING makes INSERT, UPDATE and DELETE return the affected rows (or chosen expressions) like a SELECT, which is the usual way to get generated keys.
  2. 2. What is the difference between '42'::integer and CAST('42' AS integer)?

    :: is PostgreSQL shorthand for the standard CAST. Use CAST in code that has to work on other databases.
  3. 3. In SELECT DISTINCT ON (genre) genre, title FROM books ORDER BY genre, price DESC;, which book is kept for each genre?

    DISTINCT ON keeps the first row of each genre according to the ORDER BY, and price DESC puts the most expensive first.
  4. 4. What does LATERAL allow a subquery in the FROM clause to do?

    A LATERAL subquery can use values from the row it is joined to, as if it were run once per row. That is why it is ideal for top-N-per-row queries.

Summary

  • RETURNING hands back the rows that INSERT, UPDATE and DELETE changed, including generated ids.
  • FILTER (WHERE ...) limits an aggregate to some rows, and is standard SQL that also runs in SQLite.
  • ILIKE matches text without caring about case; ~ and ~* match regular expressions.
  • value::type is shorthand for CAST(value AS type); prefer CAST for portable code.
  • generate_series creates rows of numbers or dates; a recursive CTE is the portable equivalent.
  • DISTINCT ON keeps the first row per group and LATERAL lets a subquery use the row beside it.
  • Know which features are standard and which are PostgreSQL-only before you rely on them.

Next: Numeric Types, the first lesson of the Data Types chapter.