PostgreSQL vs MySQL: AI Rules Comparison

Same SQL, Different Dialects

PostgreSQL and MySQL both use SQL, but their dialects diverge on: data types (Postgres has JSONB, arrays, enums, UUID; MySQL has JSON but no JSONB, no native arrays, no UUID type), functions (Postgres gen_random_uuid() vs MySQL UUID()), full-text search (Postgres tsvector + GIN index vs MySQL FULLTEXT index), JSON operations (Postgres @> containment operator vs MySQL JSON_CONTAINS), and upsert syntax (Postgres ON CONFLICT vs MySQL ON DUPLICATE KEY UPDATE). AI generating Postgres SQL for MySQL produces syntax errors and vice versa.

Without database-specific rules: AI generates jsonb columns in MySQL (type does not exist — MySQL uses json), uses gen_random_uuid() in MySQL (function does not exist), writes Postgres array syntax in MySQL (arrays are not a MySQL type), or uses MySQL's backtick quoting in Postgres (Postgres uses double quotes). The SQL looks correct in general but fails on the specific database. One rule about which database is in use prevents every dialect-specific error.

This article provides: the key SQL dialect differences, the AI rules needed for each database, and copy-paste CLAUDE.md templates. The rules tell the AI: this project uses PostgreSQL (use JSONB, gen_random_uuid(), GIN indexes) or this project uses MySQL (use JSON, UUID(), FULLTEXT indexes) — aligning every SQL statement with the correct dialect.

Data Types: JSONB, Arrays, UUIDs

PostgreSQL data types: JSONB (binary JSON with indexing — query individual keys efficiently), arrays (text[], integer[] — store lists without a junction table), UUID (native UUID type with gen_random_uuid() default), enum (CREATE TYPE status AS ENUM ('draft', 'published')), and inet/cidr (IP address types). Postgres has richer types that enable: schema-less flexibility (JSONB), list storage without joins (arrays), and built-in UUID generation. AI rule: 'Postgres: use JSONB for flexible JSON data, UUID for primary keys with gen_random_uuid(), text[] for simple lists.'

MySQL data types: JSON (text-based JSON — validated on insert but not binary-indexed like JSONB), no native arrays (use JSON arrays or junction tables), no native UUID type (store as CHAR(36) or BINARY(16), generate with UUID() function), ENUM (inline enum definition — ENUM('draft', 'published') on the column, not a separate type). MySQL's type system is simpler: JSON for flexible data, VARCHAR/CHAR for most strings, INT for numbers. AI rule: 'MySQL: use JSON for flexible data, CHAR(36) for UUIDs with UUID() function, ENUM inline on columns.'

The data type rule is the most common source of errors: AI generating JSONB in a MySQL CREATE TABLE statement fails (MySQL uses JSON, not JSONB). AI using gen_random_uuid() in MySQL fails (use UUID() instead). AI creating array columns in MySQL fails (arrays do not exist as a column type). One rule about which types are available prevents every CREATE TABLE and ALTER TABLE error.

• Postgres: JSONB (binary, indexable), text[] (arrays), UUID (native type), CREATE TYPE ENUM
• MySQL: JSON (text-based), no arrays, CHAR(36) for UUID, inline ENUM('a','b')
• Postgres gen_random_uuid() vs MySQL UUID() — different function names
• Postgres JSONB @> operator for containment vs MySQL JSON_CONTAINS() function
• AI error: JSONB in MySQL = syntax error. text[] in MySQL = type does not exist

Indexing and Full-Text Search

PostgreSQL indexes: B-tree (default, equality and range), GIN (Generalized Inverted Index — for JSONB, arrays, and full-text search), GiST (Generalized Search Tree — for geometric, spatial, and range types), and BRIN (Block Range Index — for large sequential data). Full-text search: tsvector column + GIN index + to_tsvector/plainto_tsquery functions. Postgres FTS is built-in and powerful enough for most applications. AI rule: 'Postgres: GIN index for JSONB and full-text search. Create tsvector column for FTS. B-tree for equality/range.'

MySQL indexes: B-tree (default, InnoDB), FULLTEXT (for full-text search on VARCHAR/TEXT columns), spatial (for geometric data), and hash (MEMORY engine only). Full-text search: FULLTEXT index + MATCH...AGAINST syntax. MySQL FULLTEXT is simpler than Postgres FTS but has fewer features (no ranking weights, no language-specific stemming configuration). AI rule: 'MySQL: FULLTEXT index for search on text columns. MATCH(column) AGAINST(query) for search queries. B-tree for equality/range.'

The index rule prevents: AI creating GIN indexes in MySQL (GIN does not exist in MySQL), using tsvector in MySQL (Postgres-specific type), writing MATCH...AGAINST in Postgres (MySQL-specific syntax), or creating FULLTEXT indexes in Postgres (use GIN with tsvector instead). The search and indexing syntax is entirely database-specific — one rule about which index types and search syntax to use aligns every query.

Upsert and CTE Syntax

PostgreSQL upsert: INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = now(). The EXCLUDED keyword references the row that was proposed for insertion. The ON CONFLICT clause specifies which unique constraint triggers the upsert. This is the Postgres-standard way to insert-or-update in a single atomic operation.

MySQL upsert: INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW(). The VALUES() function references the proposed insertion values (deprecated in MySQL 8.0.20+, replaced by aliases: AS new ON DUPLICATE KEY UPDATE name = new.name). The ON DUPLICATE KEY clause triggers on any unique key violation, not a specific constraint.

The upsert rule prevents: AI generating ON CONFLICT (Postgres) in MySQL queries (syntax error), using EXCLUDED in MySQL (Postgres keyword), writing ON DUPLICATE KEY UPDATE in Postgres (MySQL syntax), or using VALUES() references that are deprecated in newer MySQL versions. Both achieve upsert; the syntax is incompatible. One rule about upsert syntax prevents every insert-or-update query error.

• Postgres: ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col
• MySQL: ON DUPLICATE KEY UPDATE col = VALUES(col) (or alias syntax in 8.0.20+)
• Postgres: EXCLUDED references proposed row. MySQL: VALUES() or alias references
• Postgres: specify which constraint triggers upsert. MySQL: any unique key triggers it
• CTEs: both support WITH ... AS, but MySQL added CTE support in 8.0 (check version)

Extensions and Advanced Features

PostgreSQL extensions: PostGIS (spatial queries, geographic data), pg_trgm (trigram fuzzy matching), pgcrypto (cryptographic functions), pg_stat_statements (query performance monitoring), and hundreds more. Extensions are a core Postgres strength: CREATE EXTENSION postgis adds spatial capabilities to any database. Neon Postgres supports many extensions out of the box. AI rule: 'Postgres: use extensions for advanced features. PostGIS for spatial, pg_trgm for fuzzy search, pgcrypto for hashing. CREATE EXTENSION to enable.'

MySQL features: replication (built-in primary-replica), partitioning (range, list, hash), window functions (8.0+), JSON path expressions (8.0+), and MySQL-specific engines (InnoDB default, MEMORY for caches). MySQL does not have an extension system like Postgres — features are built into the server or require separate tools. Advanced capabilities (spatial, full-text, JSON) are built-in but less powerful than Postgres equivalents. AI rule: 'MySQL: built-in features, no extension system. InnoDB engine for most tables. Partitioning for large tables. JSON path for JSON queries.'

The extension rule matters for feature selection: AI suggesting CREATE EXTENSION in MySQL produces an error (MySQL has no extension system). AI suggesting PostGIS spatial queries in MySQL produces function-not-found errors (use MySQL's built-in spatial functions instead, which have a different API). The rule tells the AI: what is available in each database and how to access it.

Ready-to-Use Rule Templates

PostgreSQL CLAUDE.md template: '# Database (PostgreSQL). Use PostgreSQL via Neon serverless. Types: UUID (gen_random_uuid()), JSONB (indexable JSON), text[] (arrays), TIMESTAMPTZ (timezone-aware). Indexes: B-tree (default), GIN (JSONB, FTS, arrays), GiST (spatial). Full-text search: tsvector + GIN index + plainto_tsquery. Upsert: ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col. Extensions: PostGIS, pg_trgm, pgcrypto. Quoting: double quotes for identifiers. Never MySQL syntax (backticks, FULLTEXT, ON DUPLICATE KEY, UUID()).'

MySQL CLAUDE.md template: '# Database (MySQL). Use MySQL 8.0+. Types: JSON (text-based), CHAR(36) for UUID (UUID() function), ENUM inline, DATETIME/TIMESTAMP. Indexes: B-tree (InnoDB), FULLTEXT (text search). Full-text search: FULLTEXT index + MATCH(col) AGAINST(query). Upsert: ON DUPLICATE KEY UPDATE col = VALUES(col). Engine: InnoDB (default, transactions). Quoting: backticks for identifiers. Never PostgreSQL syntax (JSONB, text[], gen_random_uuid(), ON CONFLICT, GIN/GiST, EXCLUDED).'

The negative rules are extensive because SQL dialects have many incompatible features. Every Postgres-specific feature must be listed as "never" in the MySQL template and vice versa. The AI has strong SQL training but does not distinguish Postgres SQL from MySQL SQL without explicit rules. These templates draw the dialect boundary clearly.

Comparison Summary

Summary of PostgreSQL vs MySQL AI rules.

• Types: Postgres JSONB/UUID/arrays vs MySQL JSON/CHAR(36)/no arrays
• UUID: gen_random_uuid() (Postgres) vs UUID() (MySQL) — different functions
• Search: tsvector + GIN (Postgres) vs FULLTEXT + MATCH AGAINST (MySQL)
• Upsert: ON CONFLICT DO UPDATE (Postgres) vs ON DUPLICATE KEY UPDATE (MySQL)
• Indexes: GIN/GiST/BRIN (Postgres) vs B-tree/FULLTEXT (MySQL)
• Extensions: Postgres has PostGIS, pg_trgm, hundreds more. MySQL has no extension system
• Quoting: double quotes (Postgres) vs backticks (MySQL) for identifiers
• Templates: extensive negative rules needed — SQL dialects have many incompatible features