PostgreSQL OR Operator

The OR operator is a logical operator in PostgreSQL that returns TRUE when at least one of its two Boolean operands is TRUE. It is used in WHERE clauses to broaden a result set by matching rows that satisfy any one of several conditions.

Syntax

expression1 OR expression2

PostgreSQL uses three-valued logic: TRUE, FALSE, and NULL. The full truth table for OR:

The key rule: OR returns TRUE if at least one operand is TRUE. It only returns FALSE when both operands are definitively FALSE.

Practical Example

Create a sample table for an order management system:

CREATE TABLE orders (
  order_id     SERIAL PRIMARY KEY,
  customer_ref VARCHAR(20) NOT NULL,
  status       VARCHAR(20) NOT NULL DEFAULT 'pending',
  region       VARCHAR(30) NOT NULL,
  total_usd    NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (customer_ref, status, region, total_usd) VALUES
  ('CUST-001', 'shipped',   'EU',   142.50),
  ('CUST-002', 'pending',   'US',    89.00),
  ('CUST-003', 'cancelled', 'EU',   310.00),
  ('CUST-004', 'shipped',   'APAC', 205.75),
  ('CUST-005', 'pending',   'US',  1200.00),
  ('CUST-006', 'refunded',  'EU',    55.00);

Find orders that are either cancelled or refunded:

SELECT order_id, customer_ref, status, total_usd
FROM orders
WHERE status = 'cancelled'
   OR status = 'refunded'
ORDER BY order_id;

Expected result:

 order_id | customer_ref |  status   | total_usd
----------+--------------+-----------+-----------
        3 | CUST-003     | cancelled |    310.00
        6 | CUST-006     | refunded  |     55.00

OR across different columns — find high-value orders or any APAC orders:

SELECT order_id, customer_ref, region, total_usd
FROM orders
WHERE total_usd > 1000.00
   OR region = 'APAC'
ORDER BY total_usd DESC;

Verify row counts without and with the OR condition:

SELECT
  COUNT(*) FILTER (WHERE total_usd > 1000.00) AS high_value,
  COUNT(*) FILTER (WHERE region = 'APAC')     AS apac,
  COUNT(*) FILTER (WHERE total_usd > 1000.00 OR region = 'APAC') AS combined;

OR vs. IN

When comparing the same column against a list of values, IN is cleaner and equally efficient:

-- Using OR
WHERE status = 'cancelled' OR status = 'refunded' OR status = 'on_hold'

-- Equivalent with IN — preferred for readability
WHERE status IN ('cancelled', 'refunded', 'on_hold')

Use OR when the conditions involve different columns or complex expressions that cannot be expressed as a simple list.

AND and OR Precedence

AND has higher precedence than OR. Without parentheses, mixed expressions may not behave as you expect:

-- Evaluated as: (region = 'EU' AND total_usd > 200) OR status = 'pending'
WHERE region = 'EU' AND total_usd > 200 OR status = 'pending';

-- Explicit: require total_usd > 200 for both cases
WHERE (region = 'EU' OR status = 'pending') AND total_usd > 200;

Always use parentheses when mixing AND and OR.

Testing with Vela

OR conditions that span multiple columns can produce larger result sets than anticipated. Before running UPDATE or DELETE statements with OR predicates, use Vela’s database branching to validate the filter on a production-data copy. Create a branch, run the statement as a SELECT, confirm the row count and contents, then apply the change to production only when you’re satisfied it targets the right rows.

Production Tips

• Replace same-column OR chains with IN for readability and cleaner query plans.
• OR conditions across different columns can use a BitmapOr plan that combines separate index scans. Verify with EXPLAIN ANALYZE — this is typically more efficient than a full sequential scan but less efficient than a single composite index.
• For high-cardinality OR conditions that span many values, a UNION ALL of two focused queries can outperform a single query with OR, because each sub-query can use its own targeted index.
• FALSE OR NULL evaluates to NULL, not FALSE. This means rows can be silently excluded when OR involves nullable columns. Use explicit IS NULL checks to handle null cases intentionally.
• Never assume OR NOT condition is the complement of OR condition when NULL is involved — use NOT (a OR b) with care and test on representative data.