⚖

PostgreSQL Comparison Operators & Predicates

Complete reference for PostgreSQL comparison operators and predicates including BETWEEN, IN, LIKE, ILIKE, IS NULL, IS DISTINCT FROM, and similarity operators. Every operator includes syntax, NULL-handling behaviour, and notes on index usage. Updated for PostgreSQL 16.

7 functions

Jump to function

= ANY / <> ALL BETWEEN EXISTS / NOT EXISTS IN / NOT IN IS DISTINCT FROM IS NULL / IS NOT NULL LIKE / ILIKE

What are PostgreSQL Comparison Operators & Predicates?

PostgreSQL comparison operators evaluate conditions between values and return boolean results used in WHERE, HAVING, and CASE clauses. Standard operators include =, <>, <, >, <=, >= while predicates like BETWEEN, IN, LIKE, and IS NULL provide specialised matching. IS DISTINCT FROM provides NULL-safe equality (unlike = which returns NULL for any NULL operand), and ILIKE enables case-insensitive pattern matching. Understanding NULL semantics in comparisons prevents silent query bugs.

= ANY / <> ALL

PG 7.4+→ boolean

Compares a value against all elements of an array or subquery result. ANY returns true if the comparison is true for at least one element.

DeveloperData Eng

Signatures

value = ANY ( array ) → boolean

value = ANY ( subquery ) → boolean

value <> ALL ( array ) → boolean

Parameters

Parameter	Type	Description
value	any	Value to compare
array	anyarray or subquery	Set of values to compare against

Examples

sql

SELECT 5 = ANY(ARRAY[1,3,5,7]);

→true

sql

SELECT * FROM posts WHERE 'admin' = ANY(allowed_roles);

→Posts accessible to admin

sql

SELECT * FROM users WHERE id = ANY(ARRAY[1,5,42]::bigint[]);

→Specific users by ID array

sql

SELECT * FROM tasks WHERE priority <> ALL(ARRAY['low','trivial']);

→Tasks that are not low or trivial priority

⚠Anti-Pattern— Using = ANY with NULL values silently fails to match

`NULL = ANY(...)` always evaluates to NULL (not true), so rows where the comparison value is NULL will never match. Also, passing an untyped array literal can cause the planner to pick a suboptimal type, bypassing indexes on bigint columns.

✓ Instead: Always cast array parameters explicitly: `id = ANY($1::bigint[])`. Guard nullable comparison values with `IS NOT NULL` checks or use `IS NOT DISTINCT FROM` for NULL-safe matching.

Instead of building `IN (1,2,3,...)` SQL strings dynamically, pass an array parameter and use `id = ANY($1::bigint[])`. This is parameterizable, preventing SQL injection.

example

SELECT * FROM products WHERE id = ANY($1::integer[]);

→Products matching a dynamically passed ID array

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

BETWEEN

PG 7.4+→ boolean

Tests whether a value lies within a range (inclusive on both ends). Equivalent to value >= low AND value <= high.

DeveloperData Eng

Signatures

value BETWEEN low AND high → boolean

value NOT BETWEEN low AND high → boolean

Parameters

Parameter	Type	Description
value	any sortable type	Value to test
low	same type	Lower bound (inclusive)
high	same type	Upper bound (inclusive)

Examples

sql

SELECT 5 BETWEEN 1 AND 10;

→true

sql

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;

→Orders with amount 100–500

sql

SELECT * FROM events WHERE event_date BETWEEN '2025-01-01' AND '2025-03-31';

→Q1 events

sql

SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;

→Products outside the $10–$50 range

⚠Anti-Pattern— BETWEEN with timestamps captures inclusive end-of-day midnight, breaking date ranges

`BETWEEN '2025-01-01' AND '2025-03-31'` is inclusive on both ends. A timestamp of `2025-03-31 23:59:59` is within range, but developers often expect exclusive upper bounds for date ranges. Additionally, if `low > high`, PostgreSQL returns zero rows silently.

✓ Instead: Use half-open intervals: `created_at >= '2025-01-01' AND created_at < '2025-04-01'`. This is also more index-friendly and makes intent explicit.

`BETWEEN low AND high` is equivalent to `>= low AND <= high`. For timestamps, use `>= start AND < end + 1 day` to avoid capturing midnight of the end date ambiguously.

example

SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-04-01';

→Q1 orders — half-open interval, index-friendly

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

BETWEEN includes both endpoints — easy to double-count in date ranges

BETWEEN a AND b is equivalent to >= a AND <= b. For timestamp ranges across days, this causes off-by-one double-counting at midnight.

EXISTS / NOT EXISTS

PG 7.4+→ boolean

Returns true if the subquery returns any rows. Short-circuits on the first matching row — more efficient than IN for large subqueries.

DeveloperData Eng

Signatures

EXISTS ( subquery ) → boolean

NOT EXISTS ( subquery ) → boolean

Parameters

Parameter	Type	Description
subquery	subquery	Subquery to test for the existence of rows

Examples

sql

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

→Users with at least one order

sql

SELECT * FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.product_id = p.id);

→Products never ordered

sql

SELECT c.name FROM customers c WHERE EXISTS (SELECT 1 FROM invoices i WHERE i.customer_id = c.id AND i.overdue = true);

→Customers with at least one overdue invoice

sql

UPDATE tasks SET escalated = true WHERE NOT EXISTS (SELECT 1 FROM task_owners o WHERE o.task_id = tasks.id);

→Escalate tasks with no assigned owner

⚠Anti-Pattern— Using COUNT(*) > 0 instead of EXISTS for existence checks

`COUNT(*)` scans all matching rows to produce a total. `EXISTS` stops at the first match — O(1) per outer row with a suitable index versus O(n) for COUNT. For large related tables this is often a 10x–100x performance difference.

✓ Instead: Replace `WHERE (SELECT count(*) FROM orders WHERE user_id = u.id) > 0` with `WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)`.

`EXISTS` short-circuits after finding one matching row, unlike `COUNT(*) > 0` which scans all matching rows. Always use `EXISTS` over `COUNT` for existence checks.

example

-- Fast:
SELECT * FROM u WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);
-- Slow:
SELECT * FROM u WHERE (SELECT count(*) FROM orders WHERE user_id = u.id) > 0;

→EXISTS is faster for large related tables

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

NOT IN with a subquery that returns any NULL always returns empty set

If a NOT IN subquery returns even one NULL value, the entire NOT IN returns no rows — silently.

IN / NOT IN

PG 7.4+→ boolean

Tests whether a value equals any value in a parenthesized list or subquery.

DeveloperData Eng

Signatures

value IN (list) → boolean

value NOT IN (list) → boolean

Parameters

Parameter	Type	Description
value	any	Value to test
list	values or subquery	Set of values to compare against

Examples

sql

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

→Orders in those statuses

sql

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

→Non-banned users

sql

SELECT * FROM products WHERE category_id IN (1, 2, 5, 8);

→Products in specific categories

sql

SELECT * FROM employees WHERE department NOT IN ('HR', 'Legal', 'Finance');

→Employees outside excluded departments

⚠Anti-Pattern— NOT IN with a nullable subquery silently returns zero rows

If the subquery for `NOT IN` returns any NULL, the entire expression evaluates to NULL for every outer row — not false, not true — resulting in zero rows returned with no error. This is one of the most dangerous silent SQL bugs.

✓ Instead: Replace `WHERE id NOT IN (SELECT fk FROM t)` with `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.fk = outer.id)`. If you must use NOT IN, add `WHERE fk IS NOT NULL` inside the subquery.

If the subquery for NOT IN can return NULL, the entire NOT IN expression evaluates to NULL (not true) for any value. Use `NOT EXISTS` or `id != ALL(ARRAY[...])` to avoid this. Always add `WHERE col IS NOT NULL` to the NOT IN subquery.

example

-- Safe:
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM banned_users b WHERE b.user_id = u.id);

→Non-banned users — safe even if banned_users table is empty

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

IS DISTINCT FROM

PG 8.0+→ boolean

NULL-safe equality. Returns false when both sides are NULL (unlike <>), and true when one is NULL and the other isn't.

DeveloperData Eng

Signatures

value IS DISTINCT FROM value → boolean

value IS NOT DISTINCT FROM value → boolean

Parameters

Parameter	Type	Description
a	any	Left operand (NULL treated as a known value)
b	any	Right operand

Examples

sql

SELECT NULL IS DISTINCT FROM NULL;

→false

sql

SELECT 1 IS DISTINCT FROM NULL;

→true

sql

SELECT * FROM t WHERE old_value IS DISTINCT FROM new_value;

→Rows where value changed (including NULL transitions)

sql

SELECT * FROM settings WHERE current_value IS NOT DISTINCT FROM default_value;

→Settings still at their default, including rows where both are NULL

⚠Anti-Pattern— Using <> instead of IS DISTINCT FROM in change-detection logic, missing NULL transitions

When `OLD.col` or `NEW.col` is NULL, `<>` returns NULL (not true), so the IF branch is skipped and the change goes unlogged. NULL-to-value and value-to-NULL transitions are silently missed in audit triggers.

✓ Instead: Use `NEW.col IS DISTINCT FROM OLD.col` in triggers for NULL-safe change detection. `IS DISTINCT FROM` correctly returns true when one side is NULL and the other isn't.

Use `NEW.col IS DISTINCT FROM OLD.col` in UPDATE triggers to detect actual changes. The regular `<>` operator returns NULL when either side is NULL, missing NULL→value and value→NULL transitions.

example

IF NEW.email IS DISTINCT FROM OLD.email THEN INSERT INTO audit_log (user_id, old_email, new_email) VALUES (NEW.id, OLD.email, NEW.email); END IF;

→Logs all email changes, including changes involving NULL

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

IS NULL / IS NOT NULL

PG 7.4+→ boolean

Tests whether a value is NULL. Always returns true/false, even for NULL input.

DeveloperData Eng

Signatures

expression IS NULL → boolean

expression IS NOT NULL → boolean

Parameters

Parameter	Type	Description
expression	any	Value to test

Examples

sql

SELECT NULL IS NULL;

→true

sql

SELECT * FROM users WHERE deleted_at IS NULL;

→Active (non-deleted) users

sql

SELECT count(*) FILTER (WHERE phone IS NULL) FROM contacts;

→Count of missing phone numbers

sql

UPDATE subscriptions SET cancelled_at = now() WHERE cancelled_at IS NULL AND plan = 'free';

→Cancels all active free subscriptions

⚠Anti-Pattern— Using = NULL or != NULL instead of IS NULL / IS NOT NULL

`col = NULL` always evaluates to NULL (never true), silently returning zero rows. `col != NULL` has the same problem. This is the most common null-related bug in SQL — no warning is emitted.

✓ Instead: Always use `col IS NULL` and `col IS NOT NULL`. Never use `= NULL` or `<> NULL` or `!= NULL`.

If you frequently query `WHERE col IS NULL`, create a partial index: `CREATE INDEX ON t(id) WHERE col IS NULL`. This index only covers NULL rows and is very small.

example

CREATE INDEX users_unverified_idx ON users(id) WHERE verified_at IS NULL;

→Fast index for finding unverified users

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

LIKE / ILIKE

PG 7.4+→ boolean

Tests whether a string matches a pattern. % matches any sequence of characters; _ matches any single character. ILIKE is case-insensitive.

DeveloperData Eng

Signatures

string LIKE pattern → boolean

string ILIKE pattern → boolean

string NOT LIKE pattern → boolean

Parameters

Parameter	Type	Description
string	text	String to test
pattern	text	Pattern with % (any chars) and _ (one char) wildcards

Examples

sql

SELECT 'hello' LIKE 'he%';

→true

sql

SELECT * FROM users WHERE email ILIKE '%@gmail.com';

→Gmail users (case-insensitive)

sql

SELECT * FROM products WHERE sku LIKE 'ABC-___';

→SKUs matching 'ABC-' + exactly 3 chars

sql

SELECT * FROM articles WHERE title LIKE 'PostgreSQL%';

→Articles whose title starts with 'PostgreSQL' — prefix pattern uses a B-tree index

⚠Anti-Pattern— Leading wildcard in LIKE disables B-tree index use

A pattern like `LIKE '%@example.com'` or `LIKE '%keyword%'` cannot use a standard B-tree index — the database must scan every row and apply the pattern. On large tables this causes catastrophically slow queries.

✓ Instead: For suffix/contains searches, add a GIN trigram index: `CREATE INDEX ON t USING GIN (col gin_trgm_ops)`. Then use `col ILIKE '%keyword%'` — the trigram index handles it efficiently.

`col LIKE 'prefix%'` can use a B-tree index. `col LIKE '%suffix'` cannot. For suffix/contains searches, use a GIN trigram index with `pg_trgm` and the `%` (similarity) or `@@` operators.

example

CREATE INDEX ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%wireless%';

→Fast case-insensitive substring search

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

LIKE is case-sensitive; ILIKE is not — and LIKE is faster

LIKE 'hello%' will not match 'Hello'. Use ILIKE for case-insensitive pattern matching, but expect a performance cost.

PostgreSQL Comparison Operators & Predicates

Jump to function

What are PostgreSQL Comparison Operators & Predicates?

= ANY / <> ALL

Signatures

Parameters

Examples

Common Gotchas

BETWEEN

Signatures

Parameters

Examples

Common Gotchas

EXISTS / NOT EXISTS

Signatures

Parameters

Examples

Common Gotchas

IN / NOT IN

Signatures

Parameters

Examples

Common Gotchas

IS DISTINCT FROM

Signatures

Parameters

Examples

Common Gotchas

IS NULL / IS NOT NULL

Signatures

Parameters

Examples

Common Gotchas

LIKE / ILIKE

Signatures

Parameters

Examples

Common Gotchas

Related PostgreSQL Categories