Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
PostgreSQL and MySQL share ANSI SQL syntax but diverge in enough ways that a migration requires careful attention to data types, default behaviors, and SQL dialect differences. Most of the work is schema translation and fixing queries that rely on PostgreSQL-specific features.

​
Data Type Mapping

PostgreSQL typeMySQL equivalentNotes
SERIAL / BIGSERIALINT AUTO_INCREMENT / BIGINT AUTO_INCREMENTMySQL uses AUTO_INCREMENT, not sequences
TEXTTEXT or LONGTEXTMySQL TEXT is limited to 65,535 bytes; use LONGTEXT for larger values
VARCHAR (no limit)Not supportedMySQL VARCHAR requires a length; use TEXT instead
BOOLEANTINYINT(1)MySQL has no true boolean; 0/1 convention
BYTEABLOB or LONGBLOBBinary data
JSONBJSONMySQL JSON is stored as text with validation; no JSONB binary format
UUIDCHAR(36) or BINARY(16)No native UUID type; see UUIDs in MySQL
TIMESTAMP WITH TIME ZONEDATETIME + explicit timezone handlingMySQL DATETIME has no timezone; see Timestamps and Time Zones
NUMERIC / DECIMALDECIMAL(p,s)Precision and scale required in MySQL
ARRAYNot supportedNormalize to a child table
HSTOREJSONKey-value pairs; use JSON in MySQL
INTERVALNot supported as a column typeStore as INT seconds or use application-side logic

​
Sequences vs AUTO_INCREMENT

PostgreSQL uses sequences (independent objects) for auto-increment values. MySQL uses AUTO_INCREMENT as a column attribute: PostgreSQL: 
CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
    id INTEGER DEFAULT nextval('orders_id_seq') PRIMARY KEY
);
MySQL equivalent: 
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY
);
To get the last inserted ID in MySQL: SELECT LAST_INSERT_ID(); (equivalent to PostgreSQL’s currval() or RETURNING id).

​
SQL Dialect Differences

String concatenation: 
-- PostgreSQL
SELECT first_name || ' ' || last_name FROM users;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;
String quoting: 
-- PostgreSQL: double quotes for identifiers, single quotes for strings
SELECT "first_name" FROM users WHERE status = 'active';

-- MySQL: backticks for identifiers, single quotes for strings
SELECT `first_name` FROM users WHERE status = 'active';
MySQL accepts double quotes for identifiers only when ANSI_QUOTES SQL mode is enabled. ILIKE (case-insensitive LIKE): 
-- PostgreSQL
SELECT * FROM users WHERE name ILIKE '%alice%';

-- MySQL (case-insensitive by default with ci collations)
SELECT * FROM users WHERE name LIKE '%alice%';
MySQL string comparisons are case-insensitive by default with utf8mb4_general_ci or similar collations. If you need case-sensitive matching, use a _bin collation. RETURNING clause: 
-- PostgreSQL
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

-- MySQL (no RETURNING)
INSERT INTO users (name) VALUES ('Alice');
SELECT LAST_INSERT_ID();
LIMIT / OFFSET: 
-- PostgreSQL
SELECT * FROM orders LIMIT 10 OFFSET 20;

-- MySQL (same syntax)
SELECT * FROM orders LIMIT 10 OFFSET 20;
-- or shorthand:
SELECT * FROM orders LIMIT 20, 10;  -- LIMIT offset, count
Schemas vs databases: PostgreSQL uses schemas within a database (mydb.public.orders). MySQL uses “schemas” and “databases” interchangeably — there’s no concept of a schema within a database. What PostgreSQL calls a schema, MySQL calls a database. NULL handling in UNIQUE indexes: PostgreSQL allows multiple NULL values in a unique column (NULLs are not equal to each other). MySQL (InnoDB) also allows multiple NULLs in unique indexes — behavior is the same. CTEs: MySQL 8.0+ supports CTEs including recursive CTEs. See CTEs in MySQL. Window functions: MySQL 8.0+ supports window functions. See Window Functions in MySQL.

​
Features Without MySQL Equivalents

Some PostgreSQL features have no direct equivalent:
PostgreSQL featureMySQL situation
Arrays as column typesNormalize to a child table
Table inheritanceNot supported; use separate tables
Custom operatorsNot supported
Full LISTEN/NOTIFYNot supported; use application-side polling or a message queue
Partial indexes (WHERE clause on index)Not supported; use generated columns to index a subset
Materialized viewsNot supported natively; approximate with tables + stored procedures
COPY command for bulk loadUse LOAD DATA INFILE or MySQL Shell’s loadDump

​
Migration Approach

  1. Export the schema from PostgreSQL (pg_dump --schema-only) and translate each table manually, using the type mapping above.
  2. Export the data from PostgreSQL as CSV (COPY table TO '/tmp/table.csv' CSV HEADER).
  3. Load the data into MySQL with LOAD DATA INFILE or mysqlimport.
  4. Test queries — find all PostgreSQL-specific syntax and rewrite it.
  5. Verify counts and checksums — SELECT COUNT(*) on every table; spot-check key rows.

​
Frequently Asked Questions

​
Does MySQL support UPSERT like PostgreSQL’s ON CONFLICT?

Yes, using different syntax. See UPSERT in MySQL. MySQL’s INSERT ... ON DUPLICATE KEY UPDATE and REPLACE INTO cover the same use case as PostgreSQL’s ON CONFLICT DO UPDATE and ON CONFLICT DO NOTHING.

​
How do I handle PostgreSQL’s BOOLEAN columns in MySQL?

Use TINYINT(1). Store 1 for true and 0 for false. Most MySQL client libraries and ORMs handle this automatically and present TINYINT(1) columns as booleans. You can also use BIT(1) but TINYINT(1) has broader tooling support.

​
Troubleshooting

ProblemSolution
Syntax errors on `` concatenationReplace with CONCAT()
ILIKE not recognizedUse LIKE (case-insensitive by default with ci collations)
Double-quoted identifiers not workingEnable ANSI_QUOTES SQL mode or switch to backtick quoting
RETURNING clause not workingUse LAST_INSERT_ID() after INSERT; there’s no MySQL equivalent
SERIAL not recognizedChange to INT AUTO_INCREMENT
Array columns failingNormalize to a child table with a foreign key