Postgres by Example: Expressions

An expression is anything PostgreSQL can evaluate to a single value: a literal, a column reference, an arithmetic operation, a function call, a CASE, even another SELECT that returns one value. Expressions can appear almost anywhere — in the select list, in WHERE, in ORDER BY, in GROUP BY, in index definitions. Mastering expressions is mastering SQL.

What you'll learn:

• Arithmetic, string, and boolean expressions
• Calling built-in functions in expressions
• Using expressions in SELECT, WHERE, and ORDER BY
• Casting between types with :: and CAST(... AS ...)
• Naming expression results with AS

-- Arithmetic and concatenation
SELECT 10 + 2 AS sum, 10 * 2 AS product, 10 / 4 AS int_div, 10 / 4.0 AS real_div;
SELECT 'Hello ' || 'world' AS greeting;
SELECT 'fruit_' || 1 AS labeled;  -- numbers are coerced to text for ||

-- Function calls in expressions
SELECT upper(name), length(name), name || ' (#' || id || ')' AS label
FROM fruits ORDER BY id LIMIT 3;

-- Expression in WHERE
SELECT * FROM fruits WHERE id * 2 > 4 ORDER BY id LIMIT 3;

-- Expression in ORDER BY
SELECT id, name FROM fruits ORDER BY length(name) DESC LIMIT 3;

-- Casting
SELECT '42'::integer AS to_int, 42::text AS to_text, '2024-01-15'::date AS to_date;
SELECT CAST(3.7 AS integer) AS rounded_down;  -- SQL-standard cast syntax

The || operator concatenates strings. In PostgreSQL, if either operand is not text, it is coerced to text (so 'id_' || 5 works). +, -, *, /, % are the usual arithmetic operators; remember that integer division truncates toward zero.

PostgreSQL casts come in two flavors. value::type is the PostgreSQL-specific shortcut and is the common style. CAST(value AS type) is SQL-standard and reads better when the expression is complex. Both compile to the same thing.

Expressions in WHERE and ORDER BY are evaluated for every candidate row, which means a function call there can prevent index use. WHERE lower(email) = 'a@b.com' cannot use a plain index on email; it can use an expression index defined as CREATE INDEX ON users (lower(email)).

To run:

$ psql -f source/expressions.sql postgres
 sum | product | int_div | real_div
-----+---------+---------+----------
  12 |      20 |       2 |      2.5
(1 row)

   greeting
-------------
 Hello world
(1 row)
 ...

Common pitfalls:

• Integer division returning 0: 1 / 2 is 0. Cast: 1.0 / 2, 1::numeric / 2, or use numeric columns.
• Casting strings that don't parse — e.g. 'abc'::integer raises an error. PostgreSQL is strict about types compared to MySQL.
• Expressions returning different types in different branches of CASE cause type-mismatch errors; cast to a common type.
• Operators have precedence; 1 + 2 * 3 is 7. When in doubt, parenthesize.

Tip: When an expression is reused several times in one query, put it in a CTE or derived table once, instead of repeating it. PostgreSQL is smart enough to fold many duplicates, but the code stays cleaner.

Try it: Run SELECT id, name, id || ': ' || name AS label FROM fruits ORDER BY id LIMIT 3;. Then try SELECT name FROM fruits WHERE length(name) > 5;. Then cast: SELECT '3.14'::numeric * 2 AS doubled;.

Source: expressions.sql

Next: Numeric Types

Home: Postgres by Example