PostgreSQL Full-Text Search syntax: (1) to_tsvector(config, text) converts text to searchable format, config is language (english, spanish, simple). (2) to_tsquery(config, query) creates search query, supports & (AND), | (OR), ! (NOT), <-> (phrase). (3) @@ operator matches tsvector against tsquery. (4) ts_rank(tsvector, tsquery) ranks by relevance. (5) ts_headline(config, text, tsquery, options) highlights matches. (6) GIN index: CREATE INDEX USING GIN (to_tsvector(config, column)) or use generated column. (7) Generated column: ADD COLUMN col_tsvector tsvector GENERATED ALWAYS AS (to_tsvector(config, col)) STORED.

to_tsvector(config, text): Convert text to searchable format, config is language (english, spanish, french, simple)

to_tsquery(config, query): Create search query, supports & (AND), | (OR), ! (NOT), <-> (phrase), :* (prefix)

@@ operator: Match tsvector against tsquery, returns boolean (true if match)

ts_rank(tsvector, tsquery): Rank by relevance (word frequency), higher = more relevant

ts_headline(config, text, tsquery, options): Highlight matches, options: StartSel, StopSel, MaxWords, MinWords

GIN index: CREATE INDEX USING GIN (to_tsvector(config, column)) or use generated column for better performance

Generated column: ADD COLUMN col_tsvector tsvector GENERATED ALWAYS AS (to_tsvector(config, col)) STORED

Built-in FTS with tsvector/tsquery, GIN indexes, multiple languages, stemming, ranking, highlighting

FULLTEXT indexes (InnoDB/MyISAM), limited features, no ranking, boolean mode only, less powerful

Full-Text Search with separate service, CONTAINS/FREETEXT, complex setup, powerful but heavy

Oracle Text (separate license), powerful features, complex setup, expensive

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

ANY / ALL

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

salary > ALL (SELECT salary FROM interns)

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

PRIMARY KEY

Uniquely identifies each row and implicitly requires NOT NULL.

customer_id INT PRIMARY KEY