⚖

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

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

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.

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 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

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

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

= 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

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

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

`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

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

`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

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

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

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

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

PostgreSQL Comparison Operators & Predicates

Jump to function

What are PostgreSQL Comparison Operators & Predicates?

BETWEEN

Signatures

Parameters

Examples

IN / NOT IN

Signatures

Parameters

Examples

= ANY / <> ALL

Signatures

Parameters

Examples

EXISTS / NOT EXISTS

Signatures

Parameters

Examples

LIKE / ILIKE

Signatures

Parameters

Examples

IS NULL / IS NOT NULL

Signatures

Parameters

Examples

IS DISTINCT FROM

Signatures

Parameters

Examples

Related PostgreSQL Categories