Migration Reference

Syntax and behavior differences when moving to PostgreSQL

Concept	MySQL	PostgreSQL	Notes
Data Types
Integer	`INT`	`INTEGER`	Identical in all three. INT is also accepted in PostgreSQL.
Large integer	`BIGINT`	`BIGINT`	Identical. Use BIGINT for IDs on large tables.
Small integer	`TINYINT / SMALLINT`	`SMALLINT`	PostgreSQL has no TINYINT. SMALLINT stores -32768 to 32767.
Unsigned integer	`INT UNSIGNED`	`INTEGER with CHECK (col >= 0)`	PostgreSQL has no unsigned types. Use a CHECK constraint or a larger type (BIGINT) instead.
Boolean	`TINYINT(1)`	`BOOLEAN`	PostgreSQL has a native BOOLEAN type. Accepts TRUE/FALSE, 't'/'f', 'yes'/'no', '1'/'0'.
Variable-length string	`VARCHAR(n)`	`VARCHAR(n) or TEXT`	PostgreSQL TEXT has no length limit and performs identically to VARCHAR. Prefer TEXT unless you need the length constraint enforced.
Unlimited text	`TEXT / MEDIUMTEXT / LONGTEXT`	`TEXT`	PostgreSQL TEXT handles all sizes. No need for MEDIUM or LONG variants.
Date and time	`DATETIME`	`TIMESTAMP`	PostgreSQL TIMESTAMP has microsecond precision. No timezone awareness — use TIMESTAMPTZ for timezone-aware storage.
Timezone-aware timestamp	`TIMESTAMP (UTC-stored)`	`TIMESTAMPTZ`	MySQL TIMESTAMP auto-converts to UTC. PostgreSQL TIMESTAMPTZ stores UTC and converts on display. Use TIMESTAMPTZ for any time that crosses timezones.
Floating point	`FLOAT / DOUBLE`	`REAL (4-byte) / DOUBLE PRECISION (8-byte)`	FLOAT and DOUBLE are inexact. Use NUMERIC for money or exact values.
Exact decimal	`DECIMAL(p,s)`	`NUMERIC(p,s)`	NUMERIC is exact. For SQL Server MONEY, use NUMERIC(19,4). Avoid FLOAT for financial data.
Binary data	`BLOB / MEDIUMBLOB / LONGBLOB`	`BYTEA`	PostgreSQL BYTEA stores binary data. For large objects, consider pg_lo (large objects) or store files externally.
UUID	`VARCHAR(36)`	`UUID`	PostgreSQL has a native UUID type. Use gen_random_uuid() (PG13+) or uuidv7() (PG18+) to generate UUIDs.
Enumerated type	`ENUM('a','b','c')`	`CREATE TYPE name AS ENUM ('a','b','c')`	PostgreSQL ENUMs are first-class types. Adding values requires ALTER TYPE. Consider a CHECK constraint for simpler cases.
JSON	`JSON`	`JSONB`	Prefer JSONB over JSON in PostgreSQL — it stores data in a parsed binary format, supports indexing, and is faster to query.
Array	`(not supported)`	`INTEGER[], TEXT[], etc.`	PostgreSQL supports native arrays of any type. Use unnest() to expand arrays into rows.
Auto-increment
Auto-incrementing primary key	`INT AUTO_INCREMENT PRIMARY KEY`	`INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY`	GENERATED ALWAYS AS IDENTITY is the SQL-standard approach (PG10+). SERIAL is the legacy equivalent and still widely used.
Serial shorthand	`INT AUTO_INCREMENT`	`SERIAL`	SERIAL is shorthand for INTEGER + sequence + DEFAULT. BIGSERIAL for large tables. Prefer GENERATED AS IDENTITY for new schemas.
Get last inserted ID	`LAST_INSERT_ID()`	`RETURNING id`	PostgreSQL's RETURNING clause is far more powerful — it returns any column(s) from the inserted row in the same statement.
DDL
List tables	`SHOW TABLES`	`\dt or SELECT tablename FROM pg_tables WHERE schemaname = 'public'`
Describe table structure	`DESCRIBE tablename or SHOW COLUMNS FROM tablename`	`\d tablename or SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'name'`
List databases	`SHOW DATABASES`	`\l or SELECT datname FROM pg_database`
Switch database	`USE dbname`	`\c dbname (psql only — reconnect required)`	PostgreSQL does not have a USE command. You must reconnect to a different database. Schemas serve a similar role within a database.
Identifier quoting	`backticks`	`"double quotes"`	Unquoted identifiers are folded to lowercase in PostgreSQL. "MyTable" ≠ mytable. Avoid mixed-case identifiers to prevent quoting headaches.
Conditional create	`CREATE TABLE IF NOT EXISTS`	`CREATE TABLE IF NOT EXISTS`
Add column	`ALTER TABLE t ADD COLUMN c INT`	`ALTER TABLE t ADD COLUMN c INTEGER`
Rename column	`ALTER TABLE t CHANGE old_name new_name INT`	`ALTER TABLE t RENAME COLUMN old_name TO new_name`
Change column type	`ALTER TABLE t MODIFY COLUMN c VARCHAR(255)`	`ALTER TABLE t ALTER COLUMN c TYPE VARCHAR(255)`	PostgreSQL may require a USING clause for non-trivial conversions: ALTER COLUMN c TYPE INTEGER USING c::integer
DML
Upsert (insert or update)	`INSERT ... ON DUPLICATE KEY UPDATE`	`INSERT ... ON CONFLICT DO UPDATE SET ...`	PostgreSQL ON CONFLICT is cleaner and more explicit than MySQL's ON DUPLICATE KEY UPDATE.
Limit rows returned	`SELECT ... LIMIT 10`	`SELECT ... LIMIT 10`
Limit with offset	`LIMIT 10, 20 (offset, count)`	`LIMIT 20 OFFSET 10`	MySQL's LIMIT offset,count order is the opposite of PostgreSQL's LIMIT count OFFSET offset. Common migration gotcha.
Return values from INSERT/UPDATE/DELETE	`LAST_INSERT_ID() (insert only)`	`RETURNING col1, col2`	RETURNING works on INSERT, UPDATE, and DELETE. More flexible than SQL Server's OUTPUT clause.
Truncate table	`TRUNCATE TABLE t`	`TRUNCATE TABLE t or TRUNCATE TABLE t RESTART IDENTITY CASCADE`	Add RESTART IDENTITY to reset sequences. Add CASCADE to truncate dependent tables.
String Functions
String concatenation	`CONCAT(a, b) or (no \|\| by default)`	`a \|\| b or CONCAT(a, b)`	\|\| is the SQL-standard concatenation operator. NULL \|\| 'x' returns NULL — use CONCAT() if NULLs are possible.
Null substitution	`IFNULL(val, default)`	`COALESCE(val, default)`	COALESCE is SQL-standard and accepts multiple fallback values: COALESCE(a, b, c, 'default').
String length	`LENGTH() (bytes) / CHAR_LENGTH() (chars)`	`LENGTH() (chars) / OCTET_LENGTH() (bytes)`	PostgreSQL LENGTH() counts characters, not bytes. For multibyte UTF-8, this distinction matters.
Find substring position	`LOCATE(substr, str)`	`POSITION(substr IN str) or STRPOS(str, substr)`
Extract substring	`SUBSTR(str, pos, len) or SUBSTRING(str, pos, len)`	`SUBSTRING(str FROM pos FOR len) or SUBSTR(str, pos, len)`
Replace substring	`REPLACE(str, from, to)`	`REPLACE(str, from, to)`	Identical in all three.
Upper/lower case	`UPPER() / LOWER()`	`UPPER() / LOWER()`	Identical. For case-insensitive search, use ILIKE or LOWER(col) LIKE LOWER(pattern) or citext extension.
Pad string	`LPAD(str, len, pad) / RPAD(str, len, pad)`	`LPAD(str, len, pad) / RPAD(str, len, pad)`
Regex match	`col REGEXP 'pattern'`	`col ~ 'pattern' (case-sensitive) col ~* 'pattern' (case-insensitive) col !~ 'pattern' (not match)`	PostgreSQL has full POSIX regex support. ~ is case-sensitive, ~* is case-insensitive.
Split string	`No split — use SUBSTRING_INDEX()`	`STRING_TO_TABLE(str, delim) (PG16+) or regexp_split_to_table(str, pattern)`
Date & Time
Current timestamp	`NOW() or CURRENT_TIMESTAMP`	`NOW() or CURRENT_TIMESTAMP`	NOW() returns TIMESTAMPTZ in PostgreSQL. Use CLOCK_TIMESTAMP() for the real current time inside a long transaction.
Current date	`CURDATE() or CURRENT_DATE`	`CURRENT_DATE`
Add interval to date	`DATE_ADD(date, INTERVAL 1 DAY) DATE_ADD(date, INTERVAL 2 MONTH)`	`date + INTERVAL '1 day' date + INTERVAL '2 months'`	PostgreSQL interval arithmetic is natural and SQL-standard. Supports: '1 year 2 months 3 days'.
Difference between dates	`DATEDIFF(end, start) (days only) TIMESTAMPDIFF(unit, start, end)`	`end::date - start::date (days) EXTRACT(EPOCH FROM (end - start)) (seconds)`	Date subtraction in PostgreSQL returns an integer (days). For other units, use EXTRACT or AGE().
Extract part of date	`YEAR(d) / MONTH(d) / DAY(d) DATE_PART('year', d)`	`EXTRACT(YEAR FROM d) DATE_PART('year', d)`	EXTRACT is SQL-standard. Both EXTRACT and DATE_PART work in PostgreSQL.
Format date as string	`DATE_FORMAT(d, '%Y-%m-%d')`	`TO_CHAR(d, 'YYYY-MM-DD')`	TO_CHAR format codes: YYYY (year), MM (month), DD (day), HH24 (hour), MI (minute), SS (second).
Parse string to date	`STR_TO_DATE('15/01/2024', '%d/%m/%Y')`	`TO_DATE('15/01/2024', 'DD/MM/YYYY') TO_TIMESTAMP('15/01/2024 10:30', 'DD/MM/YYYY HH24:MI')`
Unix timestamp (epoch)	`UNIX_TIMESTAMP() FROM_UNIXTIME(epoch)`	`EXTRACT(EPOCH FROM NOW()) TO_TIMESTAMP(epoch)`	EXTRACT(EPOCH FROM ...) returns a float (fractional seconds). Multiply by 1000 for milliseconds.
Truncate to time unit	`DATE(NOW()) (day only) DATE_FORMAT(d, '%Y-%m-01') (month)`	`DATE_TRUNC('month', d) DATE_TRUNC('hour', d)`	DATE_TRUNC is one of PostgreSQL's most useful date functions. Supports: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century.
Conditional
IF expression	`IF(condition, true_val, false_val)`	`CASE WHEN condition THEN true_val ELSE false_val END`	PostgreSQL has no IF() function in SQL. Use CASE WHEN. In PL/pgSQL functions, IF...THEN...ELSE is available.
Return NULL if equal	`NULLIF(a, b)`	`NULLIF(a, b)`	Identical. Returns NULL if a = b, otherwise returns a. Useful to avoid division by zero: val / NULLIF(denom, 0).
Greatest / Least of values	`GREATEST(a, b, c) / LEAST(a, b, c)`	`GREATEST(a, b, c) / LEAST(a, b, c)`	Returns NULL if any argument is NULL in PostgreSQL and MySQL.
Aggregates
Aggregate strings	`GROUP_CONCAT(col SEPARATOR ', ')`	`STRING_AGG(col, ', ')`	Add ORDER BY inside: STRING_AGG(col, ', ' ORDER BY col). PostgreSQL and SQL Server syntax is identical.
Count distinct	`COUNT(DISTINCT col)`	`COUNT(DISTINCT col)`	Identical in all three.
Arbitrary value from group	`ANY_VALUE(col)`	`ANY_VALUE(col) (PG16+) or MIN(col) / MAX(col)`	Useful when you need a non-grouped column that has the same value for all rows in the group.
Aggregate into JSON array	`JSON_ARRAYAGG(col)`	`JSON_AGG(col) or JSONB_AGG(col)`
JSON
Extract JSON value	`JSON_EXTRACT(doc, '$.key') doc->>'$.key'`	`doc->>'key' (text) doc->'key' (jsonb)`	-> returns jsonb, ->> returns text. For nested: doc->'outer'->>'inner'.
JSON contains check	`JSON_CONTAINS(doc, '"value"', '$.key')`	`doc @> '{"key": "value"}'`	@> is the containment operator. Create a GIN index for fast containment queries: CREATE INDEX ON t USING GIN (doc).
Set JSON value	`JSON_SET(doc, '$.key', 'value')`	`jsonb_set(doc, '{key}', '"value"')`
Build JSON object	`JSON_OBJECT('k', v)`	`jsonb_build_object('k', v) JSON_OBJECT('k': v) (PG17+)`
Indexes
Full-text search index	`FULLTEXT INDEX`	`CREATE INDEX ON t USING GIN (to_tsvector('english', col))`	PostgreSQL full-text search uses tsvector/tsquery. More powerful but requires learning the syntax. pg_trgm extension adds trigram similarity search.
Partial index	`(not supported)`	`CREATE INDEX ON t (col) WHERE condition`	Partial indexes are a major PostgreSQL advantage — index only the rows you actually query, dramatically reducing index size.
Expression / function index	`CREATE INDEX ON t ((LOWER(col)))`	`CREATE INDEX ON t (LOWER(col))`	PostgreSQL can index any immutable expression. Essential for case-insensitive search without the citext extension.
Non-blocking index build	`(online DDL in some versions)`	`CREATE INDEX CONCURRENTLY ON t (col)`	CONCURRENTLY builds the index without locking the table for reads/writes. Takes longer but safe for production.
Transactions
Start transaction	`BEGIN or START TRANSACTION`	`BEGIN`	PostgreSQL is fully ACID. All statements run in implicit transactions unless autocommit is off.
Savepoint	`SAVEPOINT name`	`SAVEPOINT name ROLLBACK TO SAVEPOINT name RELEASE SAVEPOINT name`	Use savepoints to partially roll back within a transaction without aborting the whole thing.
Set isolation level	`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE`	`BEGIN ISOLATION LEVEL SERIALIZABLE`	PostgreSQL uses SSI (Serializable Snapshot Isolation) — more correct and less locking than traditional SERIALIZABLE.
Users & Auth
Create a user	`CREATE USER 'alice'@'%' IDENTIFIED BY 'pass'`	`CREATE ROLE alice LOGIN PASSWORD 'pass'`	PostgreSQL merges users and roles — a role with LOGIN is a user. Host-based auth is in pg_hba.conf, not the role definition.
Grant table access	`GRANT SELECT ON db.table TO 'alice'@'%'`	`GRANT SELECT ON table TO alice`
Grant all on schema	`GRANT ALL ON db.* TO 'alice'@'%'`	`GRANT ALL ON ALL TABLES IN SCHEMA public TO alice GRANT USAGE ON SCHEMA public TO alice`	In PostgreSQL 15+, PUBLIC no longer has default CREATE on public schema. Grant USAGE + CREATE explicitly.
Apply privilege changes	`FLUSH PRIVILEGES`	`(automatic — no FLUSH needed)`	PostgreSQL privilege changes take effect immediately. No FLUSH PRIVILEGES equivalent needed.
Gotchas
Case-sensitive string comparison	`Case-insensitive by default (latin1_swedish_ci)`	`Case-sensitive by default`	SELECT * FROM t WHERE name = 'Alice' will NOT match 'alice' in PostgreSQL. Use ILIKE for case-insensitive pattern matching, or LOWER() on both sides.
Empty string vs NULL	`'' ≠ NULL (but some contexts treat them similarly)`	`'' ≠ NULL (strictly different)`	PostgreSQL strictly differentiates '' (empty string) from NULL. WHERE col = '' will never match NULL rows.
String truncation	`Silently truncates strings that exceed column width`	`Raises error (22001: string_data_right_truncation)`	PostgreSQL never silently truncates. Strings that are too long always raise an error — fix the data or increase column width.
Zero date	`'0000-00-00' is a valid special value`	`Not valid — use NULL instead`	MySQL zero dates must be converted to NULL during migration. They have no PostgreSQL equivalent.
Integer division	`5 / 2 = 2 (integer division)`	`5 / 2 = 2 (integer division)`	Same behavior in all three — integer divided by integer returns integer. Cast to get decimal: 5::float / 2 = 2.5.
GROUP BY strictness	`Allows selecting non-aggregated columns not in GROUP BY (ONLY_FULL_GROUP_BY optional)`	`Strict — all non-aggregated columns must be in GROUP BY`	MySQL with ONLY_FULL_GROUP_BY=off returns unpredictable values. PostgreSQL always enforces correct GROUP BY. Fix queries by adding missing columns to GROUP BY or wrapping in an aggregate.
Backslash in strings	`Backslash is an escape character by default`	`Backslash is a literal character (standard_conforming_strings=on by default since PG9.1)`	Use E'' escape strings for backslash sequences in PostgreSQL: E'path\\to\\file'. Or use $$ dollar quoting.
Schemas vs databases	`Schema and database are synonymous`	`Schemas are namespaces within a database. public is the default schema.`	MySQL databases map to PostgreSQL schemas, not databases. A PostgreSQL database can contain multiple schemas. Set search_path to control which schema is used by default.