MySQL FULLTEXT Index → PostgreSQL Full Text Search

Replacing a single FULLTEXT index declaration with PostgreSQL's tsvector column + GIN index approach — a redesign, not a syntax swap.

HIGH

Quick answer

PostgreSQL has no FULLTEXT index type. Build full-text search from a generated tsvector column plus a GIN index on it, then rewrite MATCH ... AGAINST queries to use the @@ operator against that column.

Why it breaks

MySQL's FULLTEXT KEY is a single index-type declaration that both stores the search index and defines what MATCH ... AGAINST queries against. PostgreSQL's text-search machinery is assembled from separate pieces — a tsvector representation of the searchable text, a GIN (or GiST) index on that representation, and the @@ match operator — there is no single keyword that replaces FULLTEXT.

Real examples

Index declaration → generated column + GIN index

CREATE TABLE posts (
  title VARCHAR(255),
  body TEXT,
  FULLTEXT KEY ft_search (title, body)
);

CREATE TABLE posts (
  title VARCHAR(255),
  body TEXT,
  search_vector tsvector GENERATED ALWAYS AS
    (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED
);
CREATE INDEX posts_search_idx ON posts USING GIN (search_vector);

coalesce() guards against NULL title/body, which would otherwise make the whole concatenation NULL.

Query rewrite

SELECT * FROM posts WHERE MATCH(title, body) AGAINST ('postgresql migration');

SELECT * FROM posts WHERE search_vector @@ plainto_tsquery('english', 'postgresql migration');

plainto_tsquery handles plain user-typed search phrases; websearch_to_tsquery is closer to typical search-engine syntax if you need quoted phrases/exclusions.

Safe migration options

Add a generated tsvector column (GENERATED ALWAYS AS (to_tsvector('english', col1 || ' ' || col2)) STORED) covering the same columns the FULLTEXT index covered.
Create a GIN index on that generated column — this is the PostgreSQL equivalent of the FULLTEXT index itself.
Rewrite MATCH(col1, col2) AGAINST ('term') queries as WHERE search_vector @@ plainto_tsquery('english', 'term') — the query syntax is unrelated to MySQL's, not just renamed.

PostgreSQL solution

ALTER TABLE t ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED; CREATE INDEX ON t USING GIN (search_vector);

Validation

Before cutover

Run a representative set of real search terms through both the old MATCH ... AGAINST queries and the new @@ queries against the same data, and compare result sets — relevance ranking and stemming behavior differ between the two engines, so exact result-set parity is not guaranteed and should be explicitly signed off on, not assumed.

Scan your own schema

This page documents detector rule FULLTEXT_INDEX in the DevEquiv analyzer — paste or upload your real MySQL/MariaDB schema to find every occurrence of this (and 19 other documented issues) automatically, with a prioritized readiness report.

Analyze a schema free

References

MySQL Manual — FULLTEXT Index (v8.0) — checked 2026-07-30
PostgreSQL Manual — Full Text Search (v17) — checked 2026-07-30

Last verified 2026-07-30.