The key syntax differences between MySQL and PostgreSQL – everything you should know

Discover the most common SQL syntax differences developers encounter when moving from MySQL to PostgreSQL, including identifier quoting, identifier rules, reserved keywords, limit vs. fetch first, inserting and updating data, and truncating tables.

Throughout this article – part of Aisha Bukar’s complete MySQL vs PostgreSQL comparison guide – you’ll also learn why these differences exist, and how to adapt your queries to work effectively across both databases.

Moving an application from MySQL to PostgreSQL involves more than exporting data and changing a few SQL statements. While both are relational database management systems, they interpret and extend SQL differently – and that gap has widened over the years as each system introduced its own syntax conventions, default behaviors, and features.

That’s exactly why some SQL statements look nearly identical across both systems, but others require syntax changes or a different approach altogether. Some of these easy to spot – for example, a query that executes successfully in MySQL may produce an error in PostgreSQL, because the latter enforces SQL standards more strictly. It may also return different results because of the differences in default behavior.

Similarly, features such as identifier quoting, auto-generated keys, pagination, and data manipulation are implemented differently – making database migration more than just a simple copy-and-paste exercise.

Identifier quoting in MySQL and PostgreSQL – the key differences explained

One of the first syntax differences developers notice when moving from MySQL to PostgreSQL is how identifiers are quoted. Identifiers include database objects such as table names, column names, and schema names.

Firstly, MySQL uses backticks (`) to quote identifiers:

PostgreSQL, on the other hand, follows the SQL standard by using double quotes (“):

Interestingly, MySQL can also use double quotes for identifiers, but only when the ANSI_QUOTES SQL mode is enabled. Without this mode, double quotes are interpreted as string delimiters rather than identifier quotes.

Since ANSI_QUOTES is not enabled by default in most MySQL installations, many applications and codebases continue to use backticks.

This difference often becomes apparent during migration. If MySQL queries are copied into PostgreSQL without modification, any identifiers enclosed in backticks will cause syntax errors, because PostgreSQL does not recognize backticks as identifier delimiters. 

Replacing backticks with double quotes is usually straightforward, but developers should be aware that quoted identifiers in PostgreSQL become case-sensitive. An identifier created as “Employees”, for example, is different from employees, which can introduce unexpected errors if naming conventions are inconsistent.

This is why many PostgreSQL developers avoid quoting identifiers unless necessary – preferring lowercase, unquoted names that are easier to reference across applications.

While replacing backticks with double quotes is usually one of the simpler syntax changes during migration, it’s only one part of the story. Identifiers often need to be quoted because they conflict with SQL keywords or violate a database’s naming rules.

Since MySQL and PostgreSQL don’t reserve exactly the same keywords or enforce identifier rules in the same way, an acceptable object name in one database may require quoting or renaming in the other.

Understanding these differences is essential for avoiding syntax errors and ensuring that schemas and queries remain portable across both systems.

What are the key differences in reserved keywords and identifier rules in MySQL compared to PostgreSQL?

When writing SQL, identifiers are the names assigned to database objects such as tables, columns, views, indexes, and schemas. Although developers are free to choose nearly any descriptive name, those names can’t conflict with the database’s reserved keywords.

Reserved keywords are words that have a predefined meaning in the SQL language. Statements such as SELECT, FROM, WHERE, and ORDER are reserved because they form part of SQL syntax. Attempting to use these words as identifiers without proper quoting can cause parsing errors or unexpected behavior.

MySQL and PostgreSQL maintain different sets of reserved keywords. PostgreSQL generally reserves more keywords because it more closely adheres to the SQL standard and includes additional language features. Consequently, a valid identifier in MySQL may be rejected by PostgreSQL.

For example, consider the following table definition:

Depending on the database configuration and version, this statement may execute successfully in MySQL, but PostgreSQL treats user as a reserved keyword because it refers to the current database role. To use it as a table name, the identifier must be quoted:

Although quoting resolves the syntax error, it introduces another consideration. In PostgreSQL, quoted identifiers preserve their original letter casing and become case-sensitive. For example, a table created as “User” must always be referenced using exactly the same capitalization and quotation marks.

By contrast, unquoted identifiers are automatically folded to lowercase, making them easier to reference consistently. A common example is user. It’s not a reserved keyword in MySQL, but has special significance in PostgreSQL because it’s associated with the current database role.

Why you should rename conflicting identifiers instead of relying on quoted names

Although quoting such identifiers can resolve syntax errors, relying heavily on quoted names often makes SQL harder to read and maintain, particularly in large applications with hundreds of queries.

For this reason, many migration projects prefer to rename conflicting identifiers instead of relying on quoted names. Using descriptive, lowercase identifiers that avoid reserved keywords improves readability, reduces portability issues, and minimizes the likelihood of syntax errors when moving between database systems.

How to avoid naming conflicts with reserved keywords in MySQL and PostgreSQL

Since the list of reserved keywords evolves as new SQL features are introduced, it’s good practice to always verify whether an identifier is reserved before creating or migrating a schema. Detecting and correcting any naming conflicts early is considerably easier than discovering syntax errors after the application has been deployed.

To help with this, MySQL provides a comprehensive list of keywords in its documentation, where entries marked with (R) indicate reserved words that cannot be used as identifiers without quoting.

PostgreSQL likewise maintains an appendix listing all SQL keywords, indicating whether each word is reserved, non-reserved, or has other parser-specific classifications throughout.

Consulting these references during a migration can help identify naming conflicts before they become syntax errors.

Comparing MySQL AUTO_INCREMENT with PostgreSQL SERIAL and IDENTITY (the key differences explained)

Automatically-generated primary keys are a common feature of relational databases, but MySQL and PostgreSQL have different ways of implementing them.

This is often one of the first schema changes developers encounter during migration because the AUTO_INCREMENT attribute used in MySQL is not recognized by PostgreSQL.

In MySQL, a column is made self-incrementing by applying the AUTO_INCREMENT attribute. Whenever a new row is inserted without an explicit value for that column, MySQL automatically generates the next available integer.

This approach is simple, and tightly integrated into the table definition:

Attempting to use the same definition in PostgreSQL results in a syntax error, because PostgreSQL does not support the AUTO_INCREMENT keyword:

..imAGE

Historically, PostgreSQL solved this problem using the SERIAL pseudo-type:

Although SERIAL looks like a data type, it’s actually a convenient shorthand. Behind the scenes, PostgreSQL creates an integer column, creates a separate sequence object, sets the column’s default value on the sequence to nextval(), and associates the sequence with the column.

In other words, SERIAL is a shortcut for creating a sequence-backed auto-generating column rather than a true data type.

Modern PostgreSQL also supports SQL-standard identity columns using the GENERATED ... AS IDENTITY clause:

Additionally, PostgreSQL supports GENERATED BY DEFAULT AS IDENTITY, which generates values automatically but allows applications to provide explicit values when needed.

By contrast, GENERATED ALWAYS AS IDENTITY strictly enforces automatic value generation by rejecting user-supplied values unless the INSERT statement explicitly includes the OVERRIDING SYSTEM VALUE clause.

This makes GENERATED ALWAYS a better choice when you want to prevent accidental modification of identity values while still retaining the option to override them in exceptional cases.

For new PostgreSQL applications, IDENTITY is generally preferred over SERIAL because it conforms to the SQL standard, clearly expressing that the column’s values are generated automatically.

The PostgreSQL documentation does still support SERIAL – and it remains common in existing schemas – but IDENTITY is the recommended choice for new database designs.

Why you shouldn’t replace AUTO_INCREMENT with SERIAL in PostgreSQL

A common mistake during migration is attempting to replace MySQL’s AUTO_INCREMENT with PostgreSQL’s SERIAL using a simple text substitution. While this resolves the immediate syntax error, it overlooks the fact that PostgreSQL uses sequences to generate values.

As a result, operations involving sequence management, resetting counters, or retrieving generated values may need to be updated.

For example, many MySQL applications retrieve the last generated identifier using LAST_INSERT_ID(). In PostgreSQL, the preferred approach is to use the RETURNING clause as part of the INSERT statement, which returns the generated value directly – without requiring a separate query.

Another important distinction is that, although a SERIAL column is created with a NOT NULL constraint, neither SERIAL or IDENTITY create a PRIMARY KEY or UNIQUE constraint automatically. These mechanisms define how values are generated – not how uniqueness is enforced.

If the column is intended to uniquely identify each row (as is typically the case for surrogate keys), you must explicitly declare a PRIMARY KEY or UNIQUE constraint.  

It’s worth noting that MySQL also provides a SERIAL alias, although – unlike PostgreSQL – it’s not a pseudo-type that creates and manages a sequence. Instead, SERIAL is simply an alias for the following column definition:

Consequently, a MySQL SERIAL column is always an unsigned 64-bit integer with the AUTO_INCREMENT attribute and an implicit UNIQUE constraint.

In PostgreSQL, SERIAL behaves very differently: it expands into an integer column backed by a sequence object, but doesn’t automatically create a PRIMARY KEY or UNIQUE constraint.

So, although both databases provide a SERIAL alias, they serve different purposes and should not be considered equivalent during migration.

Differing implementation philosophies

Finally, while AUTO_INCREMENT, SERIAL, and IDENTITY all address the same requirement of generating numeric identifiers, they reflect different implementation philosophies.

MySQL exposes auto-incrementing behavior as a column attribute, whereas PostgreSQL historically relied on independent sequence objects before adopting the SQL-standard identity column syntax.

Understanding these implementation differences makes schema migration considerably smoother – helping to avoid issues that extend beyond simply replacing one keyword with another.

Comparing MySQL LIMIT and PostgreSQL FETCH FIRST (the key differences explained)

In most cases, applications rarely need to retrieve every row from a table. Whether displaying search results or paginating records, it’s common to limit the number of rows returned by a query.

While MySQL and PostgreSQL both support this capability, they offer different syntax options, with PostgreSQL also implementing the SQL-standard approach.

In MySQL, the LIMIT clause restricts the number of rows returned by a query:

PostgreSQL also supports the same syntax:

For developers migrating from MySQL to PostgreSQL, this is good news, because most queries using LIMIT work without modification.

When skipping rows, both databases support the OFFSET clause:

The query skips the first 20 rows and returns the next 10. It’s commonly used for implementing pagination.

MySQL also provides a shorthand form that combines the offset and row count into a single LIMIT clause:

In this example, the first value specifies the number of rows to skip, while the second specifies the maximum number of rows to return. Although this syntax is widely used in MySQL applications, PostgreSQL does not support it. During migration, it should be rewritten using the explicit LIMIT ... OFFSET form.

In addition to LIMIT, PostgreSQL supports the SQL-standard FETCH FIRST clause.

To skip rows before fetching results, FETCH FIRST is typically combined with OFFSET.

Unlike LIMIT, FETCH FIRST is part of the SQL standard, making it portable across several relational database systems. Although PostgreSQL supports both approaches, LIMIT remains the syntax most commonly used in PostgreSQL applications because of its brevity and long-standing familiarity.

Why these small syntax differences still need your attention

The syntax differences between MySQL and PostgreSQL are relatively small in this area, but they can still cause migration issues. Queries written using MySQL’s LIMIT offset, count syntax must be rewritten using LIMIT count OFFSET offset, or alternatively OFFSET ... FETCH FIRST ... ROWS ONLY if adopting the SQL-standard syntax.

Another important consideration is that neither LIMIT or FETCH FIRST guarantee which rows are returned unless the query includes an ORDER BY clause. Without an explicit ordering, the database is free to return rows in any order – and that order may differ between the two platforms.

This is because both MySQL and PostgreSQL use their own query planner and execution strategy. To ensure consistent and repeatable results, always combine row-limiting clauses with an appropriate ORDER BY clause –especially when implementing pagination.

Although PostgreSQL supports both LIMIT and FETCH FIRST, choosing between them largely comes down to coding standards and portability requirements.

LIMIT is concise and familiar to developers coming from MySQL, while FETCH FIRST aligns closer to the SQL standard and may be preferable in environments where cross-database compatibility is a priority.

What are the key differences in how the INSERT statement works in MySQL compared to PostgreSQL?

The basic INSERT syntax is remarkably similar in MySQL and PostgreSQL. In both databases, you can specify the target columns and provide one or more rows of values:

The same statement works in both databases. This means that simple INSERT statements are generally among the easier parts of a MySQL-to-PostgreSQL migration.

The differences become more noticeable when an application relies on database-specific behavior, particularly when inserting rows with generated values, handling duplicate records, or retrieving the values generated by the database.

One of the more significant differences appears when an application needs the value generated by the database after an INSERT.

In MySQL, an application commonly retrieves the automatically generated ID using LAST_INSERT_ID():

PostgreSQL provides the RETURNING clause, allowing the generated value to be returned as part of the INSERT statement itself:

The PostgreSQL approach can also return multiple columns or the entire newly inserted row:

This difference matters during application migration, as code expecting a separate call to retrieve the generated identifier may need rewriting to use PostgreSQL’s RETURNING clause instead.

INSERT IGNORE and ON DUPLICATE KEY UPDATE

Another important difference appears when an application requires a row insert only if it doesn’t already exist, or updates an existing row when a conflict occurs. MySQL uses INSERT IGNORE and ON DUPLICATE KEY UPDATE.

The INSERT IGNORE command tells the database to continue processing the statement when certain errors or warnings occur. For example:

If the value of customer_id conflicts with an existing unique or primary key value, MySQL can ignore the conflicting row instead of terminating the statement with an error.

MySQL also uses the ON DUPLICATE KEY UPDATE. It basically tells the database to not fail if the insert violates a PRIMARY KEY or UNIQUE constraint. Instead, it should update the existing row.

ON CONFLICT

PostgreSQL uses the ON CONFLICT command for this:

PostgreSQL can also ignore the conflicting row:

The syntax is different but, more importantly, the two databases express the conflict-handling logic differently. MySQL’s syntax is centered around duplicate-key detection, while PostgreSQL allows the developer to specify a conflict target and then choose what happens when that conflict occurs.

There’s no direct equivalent to INSERT IGNORE in PostgreSQL. When the intention is specifically to ignore a conflict involving a unique or primary key constraint, PostgreSQL uses ON CONFLICT DO NOTHING.

What are the key differences in how TRUNCATING TABLES works in MySQL compared to PostgreSQL?

TRUNCATE is used to remove all rows from a table without removing the table itself. At first, MySQL and PostgreSQL appear to handle it in much the same way. The table remains available for future inserts, but its existing rows are removed:

However, the similarities become less obvious when you look at transactions, generated identifiers, and relationships between tables.

One of the important differences is how TRUNCATE interacts with transactions. PostgreSQL treats TRUNCATE as a transactional operation. It can be included in a transaction and rolled back if the transaction is subsequently aborted:

After the rollback, any rows removed by TRUNCATE are restored.

MySQL’s behavior depends on the storage engine and transaction context. With InnoDB, for example, TRUNCATE TABLE causes an implicit commit before execution and cannot be rolled back like a regular transactional DELETE.

This difference can become significant when migrating application code that performs table cleanup as part of a larger transaction.

How MySQL and PostgreSQL handle generated identifiers – the key differences explained

Another difference concerns automatically generated identifiers. Consider a PostgreSQL table using an identity column:

PostgreSQL allows the identity sequence to be reset when truncating the table:

The next inserted row will start again from the identity sequence’s configured starting value. PostgreSQL also provides CONTINUE IDENTITY when the existing sequence values should be preserved:  

A MySQL TRUNCATE TABLE also resets the AUTO_INCREMENT counter for an InnoDB table, so a straightforward PostgreSQL TRUNCATE will generally have similar behavior with respect to generated identifiers.

However, PostgreSQL gives you explicit control over this behavior through RESTART IDENTITY and CONTINUE IDENTITY.  

What are the key differences in how foreign key relationships work in MySQL compared to PostgreSQL?

Foreign key relationships are handled differently in MySQL and PostgreSQL. If, for example, orders reference customers through a foreign key, PostgreSQL won’t allow a table to be truncated because it would violate a foreign key relationship.

The only exception would be if the related tables are included in the operation, or if cascading is explicitly requested. For this, PostgreSQL provides the CASCADE option:

This can also truncate tables that depend on customers through foreign key relationships.

Because CASCADE can affect more tables than the one named in the statement, it should be used carefully, particularly in production environments.

PostgreSQL also supports truncating several related tables explicitly:

Over in MySQL, the way you truncate tables with foreign key relationships differs from PostgreSQL. In particular, TRUNCATE TABLE is treated as a DDL operation in MySQL, and foreign key restrictions can prevent the operation – even when the referencing table is empty.

You should therefore review foreign key dependencies, rather than just assuming a MySQL TRUNCATE statement can simply be copied to PostgreSQL. And remember: despite them both removing rows, TRUNCATE is not the same as DELETE, so should not be treated as such.

A statement such as:

removes rows through the normal row-level deletion mechanism and can be combined with a WHERE clause. TRUNCATE, by contrast, removes all rows from the table, and does not support a WHERE clause.

Why this matters

The distinction matters during migration. Replacing one with the other can change transaction behavior, trigger behavior, identity handling, and the way foreign key relationships are handled.

The basic TRUNCATE TABLE syntax is portable between MySQL and PostgreSQL, but the behavior surrounding it is not. In particular, migration scripts should be reviewed for assumptions about transaction rollback, automatically- generated identifiers, foreign key dependencies, and cascading behavior.

If the purpose of the operation is simply to remove selected rows, DELETE is usually the appropriate choice. If the intention is to empty an entire table and the database-specific consequences are understood, however, TRUNCATE can be more efficient.

In summary

The important point during migration is not to treat the two commands as interchangeable – and never assume that a TRUNCATE statement copied from MySQL will have exactly the same effect in PostgreSQL.

These examples highlight an important pattern throughout a MySQL-to-PostgreSQL migration: the closer you look, the more the differences extend beyond syntax. A statement may look familiar while relying on database-specific behavior underneath.

The same becomes even more apparent when logic moves beyond individual SQL statements and into stored procedures, functions, and triggers.