PostgreSQL-specific syntax: (1) JSONB operators: -> (get field as JSON), ->> (get field as text), @> (contains), ? (key exists). (2) Array syntax: ARRAY[1,2,3], @> (contains), && (overlaps). (3) Full-text search: to_tsvector (create search vector), @@ (match), ts_rank (ranking). (4) RETURNING clause: INSERT ... RETURNING id (return generated values). (5) LISTEN/NOTIFY: LISTEN channel, NOTIFY channel, payload (pub/sub). (6) CTEs: WITH name AS (query) SELECT ... (named subqueries). (7) Extensions: CREATE EXTENSION name (add features).

JSONB operators: -> (get field as JSON), ->> (get field as text), @> (contains), ? (key exists), #> (nested path)

Array syntax: ARRAY[1,2,3] or {1,2,3}, @> (contains), && (overlaps), || (concatenate), array_remove (remove element)

Full-text search: to_tsvector (create search vector), plainto_tsquery (create query), @@ (match), ts_rank (ranking)

RETURNING clause: INSERT/UPDATE/DELETE ... RETURNING * (return affected rows in same query)

LISTEN/NOTIFY: LISTEN channel (subscribe), NOTIFY channel, payload (publish), asynchronous notifications

CTEs: WITH name AS (query) SELECT ... (named subqueries), WITH RECURSIVE for hierarchical data

Extensions: CREATE EXTENSION name (PostGIS, pg_trgm, pgcrypto, uuid-ossp), DROP EXTENSION name

Most features: JSONB (binary, fast), arrays (native), MVCC (non-blocking), full-text search (built-in), extensions (PostGIS, pg_trgm)

Simpler: JSON (text, slower), no arrays (use junction tables), locking (blocking reads), no full-text search (need Elasticsearch), no extensions

Different: JSON (text), no arrays, locking (blocking reads), full-text search (different syntax), no extensions

Enterprise: JSON (text), no arrays, MVCC (similar), full-text search (Oracle Text), no extensions (built-in features)

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

AND

Requires every condition in the boolean expression to evaluate to TRUE.

condition_a AND condition_b

AND has higher precedence than OR.

OR

Matches rows when at least one condition is TRUE.

condition_a OR condition_b

Use parentheses when mixing OR with AND.

IS NULL / IS NOT NULL

Tests whether a value is missing. SQL NULL semantics require dedicated NULL predicates.

manager_id IS NULL

Never use = NULL or != NULL.

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

LIKE