PostgreSQL Arrays and JSONB syntax: (1) Arrays: ARRAY[1,2,3] or '{1,2,3}' literal, ANY(array) checks existence, @> contains, && overlaps, array_agg() aggregates, unnest() expands. (2) JSONB: '{"key": "value"}'::jsonb cast, -> gets JSONB, ->> gets text, @> contains, ? key exists, jsonb_set() updates. (3) GIN indexes: CREATE INDEX USING GIN (column) for fast queries. (4) Array access: array[1] gets first element (1-indexed). (5) JSONB path: data -> 'key1' -> 'key2' for nested access.

Arrays: ARRAY[1,2,3] or '{1,2,3}' literal, array[1] gets first element (1-indexed), ANY(array) checks existence

Array operators: @> (contains), <@ (contained by), && (overlaps), || (concatenate), = (equal)

Array functions: array_agg() aggregates, unnest() expands, array_length() gets length, array_append() adds element

JSONB: '{"key": "value"}'::jsonb cast, -> gets JSONB, ->> gets text, #> gets path, #>> gets path as text

JSONB operators: @> (contains), <@ (contained by), ? (key exists), ?| (any key exists), ?& (all keys exist)

JSONB functions: jsonb_set() updates, jsonb_build_object() builds, jsonb_agg() aggregates, jsonb_object_keys() gets keys

GIN indexes: CREATE INDEX USING GIN (column) for arrays/JSONB, speeds up @>, ?, && queries by 10-100x

Native arrays (TEXT[], INTEGER[]), JSONB binary format (2-3x faster), GIN indexes, rich operators (@>, ?, &&)

No native arrays (use JSON array), JSON text format (slower), no GIN (use generated columns + index), limited operators

No native arrays (use XML or JSON), JSON text format, no GIN (use computed columns + index), limited operators

VARRAY and nested tables (complex), JSON text format, no GIN (use JSON search index), limited operators

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

OR

Matches rows when at least one condition is TRUE.

condition_a OR condition_b

Use parentheses when mixing OR with AND.

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

PRIMARY KEY

Uniquely identifies each row and implicitly requires NOT NULL.

customer_id INT PRIMARY KEY