PostgreSQL Logical Operators — AND, OR, NOT

Complete reference for PostgreSQL logical operators AND, OR, and NOT with full three-valued logic NULL semantics. Covers boolean expression evaluation order, short-circuit evaluation, and common NULL surprises in WHERE clauses. Updated for PostgreSQL 16.

3 functions

Jump to function

AND OR NOT

What are PostgreSQL Logical Operators — AND, OR, NOT?

PostgreSQL logical operators combine boolean expressions in WHERE, HAVING, and CASE clauses using three-valued logic. AND returns true only if both operands are true, OR returns true if either operand is true, and NOT negates a boolean value. NULL values propagate through AND and OR in non-obvious ways — for example, NULL AND false returns false, but NULL AND true returns NULL — understanding these rules is critical for writing correct filter conditions.

AND

PG 7.4+→ boolean

Logical AND. Returns true if both operands are true. Returns NULL if either operand is NULL and the other is not false.

DeveloperData Eng

Signature

boolean AND boolean → boolean

Parameters

Parameter	Type	Description
condition1	boolean	Left operand
condition2	boolean	Right operand

Examples

sql

SELECT true AND true;

→true

sql

SELECT true AND false;

→false

NULL AND TRUE = NULL, not false — three-valued logic

sql

SELECT true AND NULL;

→NULL

false AND anything = false, even NULL

sql

SELECT false AND NULL;

→false

sql

SELECT * FROM orders WHERE status = 'active' AND total > 500;

→Rows where both conditions hold; put indexed column first for efficiency

sql

SELECT count(*) FROM events WHERE is_public AND starts_at > now();

→Count of upcoming public events — boolean column used directly as condition

⚠Anti-Pattern

PostgreSQL uses three-valued logic (true/false/NULL). `false AND NULL = false` (short-circuit), but `true AND NULL = NULL`. Place the cheapest/most-selective condition first for query optimization.

example

SELECT * FROM orders WHERE is_paid = true AND amount > 1000 AND customer_id IN (SELECT ...);

→Put indexed conditions first for best performance

OR

PG 7.4+→ boolean

Logical OR. Returns true if at least one operand is true. Returns NULL if one operand is NULL and the other is false.

DeveloperData Eng

Signature

boolean OR boolean → boolean

Parameters

Parameter	Type	Description
condition1	boolean	Left operand
condition2	boolean	Right operand

Examples

sql

SELECT true OR false;

→true

sql

SELECT false OR false;

→false

NULL OR FALSE = NULL, not false — the NULL propagates

sql

SELECT false OR NULL;

→NULL

true OR anything = true

sql

SELECT true OR NULL;

→true

sql

-- Anti-pattern: OR across two indexed columns inhibits index use
SELECT * FROM products WHERE category_id = 5 OR is_featured = true;

-- Better: UNION ALL to leverage both indexes
SELECT * FROM products WHERE category_id = 5
UNION ALL
SELECT * FROM products WHERE is_featured = true AND category_id <> 5;

→UNION ALL variant can use separate indexes on category_id and is_featured

sql

SELECT * FROM users WHERE role = 'admin' OR role = 'moderator';

→Matches both roles; rewrite as role IN ('admin', 'moderator') for clarity — IN generates an equivalent plan

⚠Anti-Pattern

Queries with `col1 = x OR col2 = y` often can't use indexes on both columns efficiently. Consider rewriting as a UNION: `SELECT ... WHERE col1 = x UNION SELECT ... WHERE col2 = y` to use both indexes.

example

-- Better than OR for two-index case:
SELECT * FROM products WHERE category = 'A'
UNION
SELECT * FROM products WHERE is_featured = true;

→Can use separate indexes on both conditions

NOT

PG 7.4+→ boolean

Logical NOT (negation). Returns the opposite boolean value. NOT NULL = NULL.

DeveloperData Eng

Signature

NOT boolean → boolean

Parameters

Parameter	Type	Description
condition	boolean	Boolean expression to negate

Examples

sql

SELECT NOT true;

→false

sql

SELECT NOT false;

→true

NOT NULL = NULL — negation does not convert NULL to a boolean

sql

SELECT NOT NULL;

→NULL

sql

SELECT * FROM products WHERE NOT is_deleted;

→Rows where is_deleted is false — NULLs are silently excluded; use IS NOT TRUE to include NULLs

sql

-- Anti-pattern: NOT IN with a subquery that can return NULL
SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- If any customer_id in orders is NULL, this returns zero rows.

-- Safe alternative:
SELECT id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

→NOT EXISTS handles NULLs correctly; NOT IN with a nullable subquery silently returns no rows

sql

-- Operator precedence surprise: NOT binds tighter than AND in some parse contexts
SELECT NOT 1 = 1 AND 2 = 2;   -- parsed as (NOT (1 = 1)) AND (2 = 2) → false
SELECT NOT (1 = 1 AND 2 = 2); -- explicit grouping → false (same result, but intent is clear)
SELECT NOT 1 = 1 OR 2 = 2;    -- parsed as (NOT (1 = 1)) OR (2 = 2) → true

→Always parenthesise complex NOT expressions to make precedence explicit

⚠Anti-Pattern

`NOT col IS NULL` and `col IS NOT NULL` are equivalent. Prefer `IS NOT NULL` for clarity. Note that `NOT (col = 5)` is NOT the same as `col <> 5` when col can be NULL — `<>` returns NULL for NULL inputs, while `NOT (col = 5)` also returns NULL.

example

SELECT * FROM users WHERE NOT (role = 'admin') AND role IS NOT NULL;

→Users with a non-admin, non-null role

PostgreSQL Logical Operators — AND, OR, NOT

Jump to function

What are PostgreSQL Logical Operators — AND, OR, NOT?

AND

Signature

Parameters

Examples

OR

Signature

Parameters

Examples

NOT

Signature

Parameters

Examples

Related PostgreSQL Categories