Expressions

Examples on this page use this small demo schema:

CREATE TABLE products  (id BIGINT PRIMARY KEY, name TEXT, price DOUBLE PRECISION,
                        category TEXT, discount DOUBLE PRECISION, stock BIGINT);
CREATE TABLE orders    (id BIGINT PRIMARY KEY, customer_id BIGINT, amount DOUBLE PRECISION,
                        status TEXT, order_date DATE, created_at TIMESTAMP);
CREATE TABLE customers (id BIGINT PRIMARY KEY, name TEXT, email TEXT, country TEXT);
CREATE TABLE users     (id BIGINT PRIMARY KEY, name TEXT, username TEXT, email TEXT,
                        age INT, status TEXT, nickname TEXT, first_name TEXT);
CREATE TABLE employees (id BIGINT PRIMARY KEY, name TEXT, department TEXT,
                        salary DOUBLE PRECISION, manager_id BIGINT);
CREATE TABLE students  (id BIGINT PRIMARY KEY, name TEXT, score DOUBLE PRECISION);

CASE

-- Searched CASE
SELECT name,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'F'
    END AS grade
FROM students;

-- Simple CASE
SELECT name,
    CASE status
        WHEN 'active' THEN 'Active'
        WHEN 'inactive' THEN 'Inactive'
        ELSE 'Unknown'
    END
FROM users;

When a condition is known at planning time (a constant true or false), a CASE folds down to the one branch that runs, but it keeps the type the whole, unfolded expression would have had, not just the winning branch's own type:

SELECT CASE WHEN true THEN 1 ELSE 2::bigint END AS always_one;

This answers a BIGINT 1, matching what an un-folded CASE WHEN true THEN 1 ELSE 2::bigint END reports, not the plain INTEGER type 1 would have on its own.

A quoted literal branch ('64', or a parameter bound as text) takes the type of the branches that have one, as in PostgreSQL: CASE WHEN id = 1 THEN '64' ELSE n END over an integer column n is an integer, so it can be assigned to an integer column, compared with a number and sorted as a number, and 'abc' in its place is invalid integer input (SQLSTATE 22P02). The same holds for COALESCE, NULLIF, GREATEST and LEAST. When every branch is a quoted literal (or NULL), the result is text, and assigning it to a column of another type is refused with SQLSTATE 42804.

BETWEEN

SELECT * FROM orders
WHERE amount BETWEEN 100 AND 500;

-- Equivalent to:
SELECT * FROM orders
WHERE amount >= 100 AND amount <= 500;

-- NOT BETWEEN
SELECT * FROM orders
WHERE created_at NOT BETWEEN '2024-01-01' AND '2024-06-30';

BETWEEN is ASYMMETRIC by default: the low bound must come first, or no row matches. BETWEEN SYMMETRIC orders the bounds for you, so it works either way. The plain form can also be spelled out as BETWEEN ASYMMETRIC, and both forms take NOT.

-- SYMMETRIC orders the bounds, so a reversed pair still matches
SELECT * FROM orders
WHERE amount BETWEEN SYMMETRIC 500 AND 100;   -- same as BETWEEN 100 AND 500

LIKE / ILIKE

-- Case-sensitive pattern matching
SELECT * FROM products WHERE name LIKE 'Pro%';         -- starts with
SELECT * FROM products WHERE name LIKE '%widget%';     -- contains
SELECT * FROM products WHERE name LIKE 'Item_';        -- single char wildcard

-- Case-insensitive
SELECT * FROM products WHERE name ILIKE '%widget%';

-- NOT LIKE
SELECT * FROM products WHERE name NOT LIKE 'TEST%';

Wildcards: % matches any sequence of characters, _ matches any single character (a character, not a byte).

LIKE and ILIKE exist only for text (and bytea) operands. A non-text operand, such as an integer or an enum column, is refused at planning time with SQLSTATE 42883 (operator does not exist: integer ~~ unknown) rather than running and simply never matching; cast the operand to text first if that is genuinely what you want.

A backslash in the pattern escapes the character after it, so LIKE 'a\%c' matches the literal string a%c, not "a, then anything, then c". ESCAPE changes the escape character:

-- A custom escape character
SELECT * FROM products WHERE name LIKE 'a!%c' ESCAPE '!';   -- matches 'a%c'

ESCAPE '' disables escaping altogether, so a backslash in the pattern is an ordinary character (and % and _ keep their wildcard meaning), and ESCAPE NULL makes the whole LIKE expression NULL, whatever the pattern and the value.

COALESCE

Returns the first non-NULL argument:

SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

Like CASE, a COALESCE the planner can fold to one argument keeps the type the whole expression would have had:

SELECT COALESCE(1, 2::bigint) AS coalesced;   -- BIGINT 1, not INTEGER 1

A quoted literal argument takes the type of the typed arguments, as a CASE branch does: COALESCE('2024-03-01', d) over a date column d is a date.

NULLIF

Returns NULL if the two arguments are equal:

SELECT NULLIF(discount, 0) FROM products;
-- Returns NULL when discount is 0, otherwise returns discount

IN / NOT IN

-- Value list
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- Subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- NOT IN
SELECT * FROM products
WHERE category NOT IN ('deprecated', 'archived');

A subquery answers an untyped string literal as TEXT, as PostgreSQL does, so its items compare with the operand as text: id IN (SELECT '1') is SQLSTATE 42883 (operator does not exist: bigint = text), while a text or CHAR(n) operand compares with them as strings. Cast the item to the operand's type (SELECT '1'::bigint) to compare numbers.

ANY / ALL / SOME

Compare one value against every element of an array. ANY (and its synonym SOME) holds when the comparison is true for at least one element, ALL when it is true for every one:

SELECT * FROM orders WHERE status = ANY (ARRAY['pending', 'processing']);

= ANY is another spelling of IN, and <> ALL of NOT IN.

The same comparison works against a subquery:

SELECT * FROM orders
WHERE amount > ANY (SELECT amount FROM orders WHERE status = 'refunded');

SELECT * FROM products
WHERE price >= ALL (SELECT price FROM products WHERE category = 'budget');

The LIKE family accepts them too, which IN cannot express:

SELECT * FROM products WHERE name LIKE ANY (ARRAY['Pro%', 'Item%']);
SELECT * FROM products WHERE name NOT ILIKE ALL (ARRAY['%test%', '%demo%']);

Arrays

An ARRAY[...] literal's elements can each carry their own cast, including a typed NULL, which is PostgreSQL's own way of writing a NULL array element whose type is not otherwise obvious:

CREATE TABLE flags (id BIGINT PRIMARY KEY, opts BOOLEAN[]);
INSERT INTO flags VALUES (1, ARRAY[true, false]), (2, ARRAY[NULL::boolean]);

A subscript reads one element, 1-based: a[2] is the second element, and a subscript past either end, a NULL array or a NULL subscript answers NULL. A slice a[lower:upper] reads a run of elements as an array: an omitted bound is the array's own (a[2:], a[:3], a[:]), bounds past the ends are clamped, a lower bound past the upper answers the empty array {}, and a NULL bound answers NULL. When any subscript of a[...][...] is a slice, every one is, a plain [i] reading as [1:i]; since arrays have one dimension, a second subscript then answers {}. An array subscript is an integer: a number of another type is converted as PostgreSQL converts it (a[1.6] is a[2], and a BIGINT past the integer range is refused with SQLSTATE 22003), a quoted constant is read as an integer (a['2'] is a[2]), and any other type is refused (SQLSTATE 42804). A JSONB value is subscripted by key or position instead (j['k'], j[0], the -> operator), and a slice of one is refused (SQLSTATE 42804). Subscripts and slices work on columns, expressions and literals, in every clause:

SELECT (ARRAY[1,2,3,4])[1:3];                      -- {1,2,3}
SELECT opts[2:] FROM flags WHERE opts[1:1] = '{true}';

Vector Distance Operators

pgvector's distance operators work over VECTOR, HALFVEC, and SPARSEVEC, and read the same as PostgreSQL's:

Operator	Function	Distance
<->	l2_distance	Euclidean (L2) distance
<#>	inner_product	Negative inner product, so ascending order still means "closest first"
<=>	cosine_distance	Cosine distance, 1 - cosine similarity
<+>	l1_distance	Taxicab (L1) distance

<~> (hamming_distance) and <%> (jaccard_distance) work the same way over BIT(n). When two BIT values share no set bit, <%> is defined as exactly 1, regardless of length.

None of the six has a negator, and none takes a NULL operand without answering NULL: every one is STRICT, like every pgvector operator and function.

CREATE TABLE vector_demo (id BIGINT PRIMARY KEY, embedding VECTOR(3));
INSERT INTO vector_demo VALUES (1, '[1,0,0]'), (2, '[0,1,0]'), (3, '[1,1,0]');

-- Nearest neighbors by Euclidean distance
SELECT id, embedding <-> '[1,0,0]' AS distance
FROM vector_demo
ORDER BY distance
LIMIT 2;

DROP TABLE vector_demo;

CREATE TABLE vector_demo (id BIGINT PRIMARY KEY, signature BIT(4));
INSERT INTO vector_demo VALUES (1, B'1010'), (2, B'1000'), (3, B'0101');

SELECT id, signature <~> B'1010' AS hamming, signature <%> B'1010' AS jaccard
FROM vector_demo
ORDER BY hamming;

DROP TABLE vector_demo;

VECTOR and HALFVEC also support element-wise arithmetic and concatenation:

Operator	Result	Notes
+, -, *	same type	Element-wise; both operands must have the same dimension count. VECTOR refuses a result past a finite float4 ("value out of range: overflow"); HALFVEC refuses the same past 65504, and HALFVEC's * additionally refuses a result that rounds to exact zero without either input being zero ("value out of range: underflow")
||	same type, concatenated	Dimensions add; the result is refused past 16000 dimensions

CREATE TABLE vector_demo (id BIGINT PRIMARY KEY, a VECTOR(2), b VECTOR(2));
INSERT INTO vector_demo VALUES (1, '[1,2]', '[3,4]');

SELECT a + b, a - b, a * b, a || b FROM vector_demo;

DROP TABLE vector_demo;

VECTOR, HALFVEC, and SPARSEVEC also support <, <=, =, <>, >=, and >, ordering by value element-by-element, then by dimension count when every compared element is equal (the shorter vector sorts first). Unlike the distance operators above, the six comparisons tolerate a dimension mismatch instead of refusing it:

CREATE TABLE vector_demo (id BIGINT PRIMARY KEY, embedding VECTOR);
INSERT INTO vector_demo VALUES (1, '[1,2,3]'), (2, '[1,2]'), (3, '[1,2,4]');

-- [1,2] sorts first (shorter, equal on every shared element), then [1,2,3], then [1,2,4]
SELECT id, embedding FROM vector_demo ORDER BY embedding;

DROP TABLE vector_demo;

IS DISTINCT FROM

IS DISTINCT FROM compares two values like <>, except it treats NULL as a regular, comparable value instead of propagating NULL. IS NOT DISTINCT FROM is the = counterpart:

SELECT * FROM orders WHERE status IS DISTINCT FROM 'cancelled';
SELECT * FROM orders WHERE status IS NOT DISTINCT FROM NULL;

Unlike = and <>, which return NULL whenever either side is NULL, IS [NOT] DISTINCT FROM always returns a definite TRUE or FALSE, treating two NULLs as not distinct from each other.

EXISTS / NOT EXISTS

-- Correlated subquery
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 500
);

SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS/NOT EXISTS subqueries are algebraically unnested to Semi/Anti-Semi joins by the optimizer - no per-row execution.

An uncorrelated EXISTS also answers as an ordinary boolean value, not only as a WHERE filter: in the SELECT list, inside a CASE, or anywhere else a boolean expression is legal.

SELECT EXISTS (SELECT 1 FROM orders WHERE amount > 10000) AS has_big_order;

SELECT CASE
    WHEN EXISTS (SELECT 1 FROM orders WHERE amount > 10000) THEN 'yes'
    ELSE 'no'
END AS has_big_order;

Subqueries

Scalar Subquery

SELECT name, salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Correlated Scalar Subquery

SELECT p.name, p.price,
    (SELECT AVG(price) FROM products p2 WHERE p2.category = p.category) AS cat_avg
FROM products p;

Correlated subqueries are automatically decorrelated by the optimizer into LEFT JOINs.

String Literals

A single-quoted string is the ordinary literal ('text'), and a doubled quote inside one is a literal quote ('it''s'). PostgreSQL's E'...' escape syntax additionally decodes backslash escapes: \n, \t, \r, \b, \f, \\, \', \xHH (a hex byte), \ooo (an octal byte), and \uXXXX/\UXXXXXXXX (a Unicode code point). An unrecognized backslash sequence passes its character through unchanged. So E'line one\nline two' holds a newline, E'\x41' is the single character A, and E'it''s' and E'it\'s' are both it's.

Expression Complexity

An expression nested far past what a hand-written query ever needs is refused before it runs rather than crashing the connection or hanging it. A very long flat chain of one operator (1+1+1+... or a AND b AND c AND ... thousands of terms long) is refused with PostgreSQL's own stack-depth error, SQLSTATE 54001 (stack depth limit exceeded), with the same HINT PostgreSQL gives about the max_stack_depth setting. The same error refuses, while the statement is still being read, anything nested past 4,096 levels in all, such as a chain of set operations (SELECT ... UNION ALL SELECT ...) more than about 4,000 arms long. Deeply nested parentheses, a deeply nested CASE, or a deeply nested chain of function calls are refused earlier, by the parser, with a syntax error (SQLSTATE 42601, typically memory exhausted at or near "("), which is also what PostgreSQL's own parser answers. An expression of ordinary size, hundreds of levels deep, is unaffected either way.

CAST

SELECT CAST(price AS INTEGER) FROM products;
SELECT price::INTEGER FROM products;
SELECT CAST('2024-01-15' AS DATE);