{"id":112395,"date":"2026-10-26T12:00:00","date_gmt":"2026-10-26T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112395"},"modified":"2026-09-29T15:32:53","modified_gmt":"2026-09-29T15:32:53","slug":"the-key-syntax-differences-between-mysql-and-postgresql-everything-you-should-know","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/the-key-syntax-differences-between-mysql-and-postgresql-everything-you-should-know\/","title":{"rendered":"The key syntax differences between MySQL and PostgreSQL – everything you should know"},"content":{"rendered":"\n

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.<\/strong><\/p>\n\n\n\n

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

Moving an application from MySQL<\/a> to PostgreSQL<\/a> involves more than exporting data and changing a few SQL statements. While both are relational database management systems<\/a>, 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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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

Identifier quoting in MySQL and PostgreSQL – the key differences explained<\/h2>\n\n\n\n

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

Firstly, MySQL uses backticks (`) to quote identifiers:<\/strong><\/p>\n\n\n\n

SELECT `first_name`, `last_name`FROM `employees`;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

PostgreSQL, on the other hand, follows the SQL standard by using double quotes (“):<\/strong><\/p>\n\n\n\n

SELECT \"first_name\", \"last_name\"FROM \"employees\";<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Interestingly, MySQL can also use double quotes for identifiers, but only when the ANSI_QUOTES SQL<\/a><\/code> mode is enabled. Without this mode, double quotes are interpreted as string delimiters<\/a> rather than identifier quotes. <\/p>\n\n\n\n

Since ANSI_QUOTES<\/code> is not<\/em> enabled by default in most MySQL installations, many applications and codebases continue to use backticks.<\/p>\n\n\n\n

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<\/a>, because PostgreSQL does not<\/em> recognize backticks as identifier delimiters. <\/strong><\/p>\n\n\n\n

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”<\/em>, for example, is different from employees<\/em>, which can introduce unexpected errors if naming conventions<\/a> are inconsistent.<\/p>\n\n\n\n

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

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. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Understanding these differences is essential for avoiding syntax errors and ensuring that schemas and queries remain portable across both systems.<\/p>\n\n\n\n

\n
\n
\n
\n

Get started with PostgreSQL – free book download<\/h3>\n
\n ‘Introduction to PostgreSQL for the data professional’, written by Grant Fritchey and Ryan Booz, covers all the basics of how to get started with PostgreSQL. <\/div>\n <\/div>\n Download your free copy<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

What are the key differences in reserved keywords and identifier rules in MySQL compared to PostgreSQL?<\/h2>\n\n\n\n

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

Reserved keywords<\/a> are words that have a predefined meaning in the SQL language. <\/strong>Statements such as SELECT<\/a><\/code>, FROM<\/code>, WHERE<\/code>, and ORDER<\/code> 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.<\/p>\n\n\n\n

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

For example, consider the following table definition:<\/p>\n\n\n\n

CREATE TABLE user (id INT PRIMARY KEY,name VARCHAR(100));<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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<\/a>. To use it as a table name, the identifier must be quoted:<\/p>\n\n\n\n

CREATE TABLE \"user\" (id INTEGER PRIMARY KEY,name VARCHAR(100));<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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”<\/em> must always be referenced using exactly the same capitalization and quotation marks.<\/p>\n\n\n\n

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

Why you should rename conflicting identifiers instead of relying on quoted names<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\n
\n\n

Want to advance your MySQL or PostgreSQL learning?<\/strong> You may be interested in:<\/strong><\/p>\n\n\n\n

Simple Talk’s full archive of MySQL content (articles, guides, and more)<\/a><\/p>\n\n\n\n

Simple Talk’s full archive of PostgreSQL content (articles, guides, and more)<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

How to avoid naming conflicts with reserved keywords in MySQL and PostgreSQL<\/h2>\n\n\n\n

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<\/em> creating or migrating a schema. Detecting and correcting any naming conflicts early is considerably easier than discovering syntax errors after<\/em> the application has been deployed.<\/strong><\/p>\n\n\n\n

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

PostgreSQL likewise maintains an appendix listing all SQL keywords<\/a>, indicating whether each word is reserved, non-reserved, or has other parser-specific classifications throughout. <\/p>\n\n\n\n

Consulting these references during a migration can help identify naming conflicts before they become syntax errors.<\/p>\n\n\n\n

Comparing MySQL AUTO_INCREMENT<\/code> with PostgreSQL SERIAL<\/code> and IDENTITY<\/code> (the key differences explained)<\/h2>\n\n\n\n

Automatically-generated primary keys<\/a> are a common feature of relational databases, but MySQL and PostgreSQL have different ways of implementing them. <\/strong><\/p>\n\n\n\n

This is often one of the first schema changes developers encounter during migration because the AUTO_INCREMENT<\/a><\/code> attribute used in MySQL is not<\/em> recognized by PostgreSQL.<\/strong><\/p>\n\n\n\n

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

This approach is simple, and tightly integrated into the table definition:<\/p>\n\n\n\n

CREATE TABLE customers (    customer_id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100) NOT NULL);<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Attempting to use the same definition in PostgreSQL results in a syntax error, because PostgreSQL does not support the AUTO_INCREMENT<\/code> keyword<\/strong>:<\/p>\n\n\n\n

..imAGE<\/code><\/p>\n\n\n\n

Historically, PostgreSQL solved this problem using the SERIAL<\/code> pseudo-type:<\/p>\n\n\n\n

CREATE TABLE customers (    customer_id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL);<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Although SERIAL<\/a><\/code> looks<\/em> 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()<\/code>, and associates the sequence with the column. <\/p>\n\n\n\n

In other words, SERIAL<\/code> is a shortcut for creating a sequence-backed auto-generating column rather than a true data type.<\/p>\n\n\n\n

Modern PostgreSQL also supports SQL-standard identity columns using the GENERATED ... AS IDENTITY<\/code> clause:<\/p>\n\n\n\n

CREATE TABLE customers (    customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    name VARCHAR(100) NOT NULL);<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Additionally, PostgreSQL supports GENERATED BY DEFAULT AS IDENTITY<\/code>, which generates values automatically but allows applications to provide explicit values when needed. <\/p>\n\n\n\n

By contrast, GENERATED ALWAYS AS IDENTITY<\/code> strictly enforces automatic value generation by rejecting user-supplied values unless<\/em> the INSERT<\/a><\/code> statement explicitly includes the OVERRIDING SYSTEM VALUE<\/code> clause. <\/p>\n\n\n\n

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

For new PostgreSQL applications, IDENTITY<\/a><\/code> is generally preferred over SERIAL<\/code> because it conforms to the SQL standard, clearly expressing that the column’s values are generated automatically. <\/strong><\/p>\n\n\n\n

The PostgreSQL documentation does still support SERIAL<\/code> – and it remains common in existing schemas – but IDENTITY<\/code> is the recommended choice for new database designs.<\/p>\n\n\n\n

Why you shouldn’t<\/em> replace AUTO_INCREMENT<\/code> with SERIAL<\/code> in PostgreSQL<\/h2>\n\n\n\n

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

As a result, operations involving sequence management, resetting counters, or retrieving generated values may need to be updated.<\/p>\n\n\n\n

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

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

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

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

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Consequently, a MySQL SERIAL<\/code> column is always an unsigned 64-bit integer with the AUTO_INCREMENT<\/code> attribute and<\/em> an implicit UNIQUE<\/code> constraint. <\/p>\n\n\n\n

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

So, although both databases provide a SERIAL<\/code> alias, they serve different purposes and should not<\/em> be considered equivalent during migration.<\/strong><\/p>\n\n\n\n

\n
\n\n

Learn more about primary keys on Simple Talk<\/strong>:<\/p>\n\n\n\n

Primary Key Primer for SQL Server<\/a><\/p>\n\n\n\n

MySQL Index Overviews: PRIMARY KEY Indexes<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

Differing implementation philosophies <\/h3>\n\n\n\n

Finally, while AUTO_INCREMENT<\/code>, SERIAL<\/code>, and IDENTITY<\/code> all address the same requirement of generating numeric identifiers, they reflect different implementation philosophies. <\/p>\n\n\n\n

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. <\/p>\n\n\n\n

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

Comparing MySQL LIMIT<\/code> and PostgreSQL FETCH FIRST<\/code> (the key differences explained)<\/h2>\n\n\n\n

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

While MySQL and PostgreSQL both support this capability, they offer different syntax options, with PostgreSQL also implementing the SQL-standard approach.<\/strong><\/p>\n\n\n\n

In MySQL, the LIMIT<\/code> clause restricts the number of rows returned by a query:<\/p>\n\n\n\n

SELECT *FROM employeesLIMIT 10;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

PostgreSQL also supports the same syntax:<\/p>\n\n\n\n

SELECT *FROM employeesLIMIT 10;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

For developers migrating from MySQL to PostgreSQL, this is good news, because most queries using LIMIT<\/code> work without modification.<\/strong><\/p>\n\n\n\n

When skipping rows, both databases support the OFFSET<\/code> clause:<\/p>\n\n\n\n

SELECT *FROM employeesLIMIT 10 OFFSET 20;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The query skips the first 20 rows and returns the next 10. It’s commonly used for implementing pagination.<\/p>\n\n\n\n

MySQL also provides a shorthand form that combines the offset and row count into a single LIMIT<\/code> clause:<\/p>\n\n\n\n

SELECT *FROM employeesLIMIT 20, 10;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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<\/em> support it. During migration, it should be rewritten using the explicit LIMIT ... OFFSET<\/code> form.<\/p>\n\n\n\n

In addition to LIMIT<\/code>, PostgreSQL supports the SQL-standard FETCH FIRST<\/code> clause.<\/p>\n\n\n\n

SELECT *FROM employeesFETCH FIRST 10 ROWS ONLY;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

To skip rows before fetching results, FETCH FIRST<\/code> is typically combined with OFFSET<\/code>.<\/p>\n\n\n\n

SELECT *FROM employeesOFFSET 20FETCH FIRST 10 ROWS ONLY;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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

\n
\n
\n
\n

Move fast. Govern at scale.<\/h3>\n
\n Redgate Flyway Enterprise embeds guardrails in the database layer, so every change is policy-checked, deterministic, and traceable. <\/div>\n <\/div>\n Try for free<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

Why these small syntax differences still need your attention<\/h3>\n\n\n\n

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<\/code> offset, count syntax must be rewritten using LIMIT<\/code> count OFFSET<\/code> offset, or alternatively OFFSET ... FETCH FIRST ... ROWS ONLY<\/code> if adopting the SQL-standard syntax.<\/p>\n\n\n\n

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

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

Although PostgreSQL supports both LIMIT<\/code> and FETCH FIRST<\/code>, choosing between them largely comes down to coding standards and portability requirements. <\/strong><\/p>\n\n\n\n

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

What are the key differences in how the INSERT<\/code> statement works in MySQL compared to PostgreSQL?<\/h2>\n\n\n\n

The basic INSERT<\/code> 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:<\/strong><\/p>\n\n\n\n

INSERT INTO customers (name, email)VALUES ('Aisha', 'aisha@example.com');<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The same statement works in both databases. This means that simple INSERT<\/code> statements are generally among the easier parts of a MySQL-to-PostgreSQL migration.<\/strong><\/p>\n\n\n\n

\n
\n\n

Want to learn more about the INSERT<\/code> statement in MySQL? Read Robert Sheldon’s guide<\/a>.<\/p>\n\n<\/div>\n<\/div> \n\n\n

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

One of the more significant differences appears when an application needs the value generated by the database after an INSERT<\/code>.<\/p>\n\n\n\n

In MySQL, an application commonly retrieves the automatically generated ID using LAST_INSERT_ID()<\/code>:<\/p>\n\n\n\n

INSERT INTO customers (name)VALUES ('Aisha');
SELECT LAST_INSERT_ID();<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

PostgreSQL provides the RETURNING<\/code> clause, allowing the generated value to be returned as part of the INSERT<\/code> statement itself:<\/p>\n\n\n\n

INSERT INTO customers (name)VALUES ('Aisha')RETURNING customer_id;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The PostgreSQL approach can also return multiple columns or the entire newly inserted row:<\/p>\n\n\n\n

INSERT INTO customers (name)VALUES ('Aisha')RETURNING *;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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<\/code> clause instead.<\/p>\n\n\n\n

INSERT IGNORE<\/code> and ON DUPLICATE KEY UPDATE<\/code><\/h3>\n\n\n\n

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

The INSERT IGNORE<\/code> command tells the database to continue processing the statement when certain errors or warnings occur. For example:<\/p>\n\n\n\n

INSERT IGNORE INTO customers (customer_id, name)VALUES (1, 'Aisha');<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

If the value of customer_id<\/code> conflicts with an existing unique or primary key value, MySQL can ignore the conflicting row instead of terminating the statement with an error.<\/p>\n\n\n\n

MySQL also uses the ON DUPLICATE KEY UPDATE<\/code>. It basically tells the database to not<\/em> fail if the insert violates a PRIMARY KEY<\/code> or UNIQUE<\/code> constraint. Instead, it should update the existing row.<\/p>\n\n\n\n

INSERT INTO customers (customer_id, name)VALUES (1, 'Aisha')ON DUPLICATE KEY UPDATE    name = 'Aisha';<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

ON CONFLICT<\/code><\/h3>\n\n\n\n

PostgreSQL uses the ON CONFLICT<\/code> command for this:<\/p>\n\n\n\n

INSERT INTO customers (customer_id, name)VALUES (1, 'Aisha')ON CONFLICT (customer_id)DO UPDATESET name = EXCLUDED.name;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

PostgreSQL can also ignore the conflicting row:<\/p>\n\n\n\n

INSERT INTO customers (customer_id, name)VALUES (1, 'Aisha')ON CONFLICT (customer_id)DO NOTHING;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

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

What are the key differences in how TRUNCATING TABLES<\/code> works in MySQL compared to PostgreSQL?<\/h2>\n\n\n\n

TRUNCATE<\/code> is used to remove all rows from a table without<\/em> 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:<\/p>\n\n\n\n

TRUNCATE TABLE customers;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

However, the similarities become less obvious when you look at transactions<\/a>, generated identifiers, and relationships between tables.<\/p>\n\n\n\n

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

BEGIN;
TRUNCATE TABLE customers;
ROLLBACK;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

After the rollback, any rows removed by TRUNCATE<\/code> are restored.<\/p>\n\n\n\n

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

This difference can become significant when migrating application code that performs table cleanup as part of a larger transaction.<\/p>\n\n\n\n

How MySQL and PostgreSQL handle generated identifiers – the key differences explained<\/h2>\n\n\n\n

Another difference concerns automatically generated identifiers. Consider a PostgreSQL table using an identity column:<\/p>\n\n\n\n

CREATE TABLE customers (    customer_id INTEGER GENERATED ALWAYS AS IDENTITY,    name TEXT);<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

PostgreSQL allows the identity sequence to be reset when truncating the table:<\/p>\n\n\n\n

TRUNCATE TABLE customersRESTART IDENTITY;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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

TRUNCATE TABLE customersCONTINUE IDENTITY;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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

However, PostgreSQL gives you explicit control<\/em> over this behavior through RESTART IDENTITY<\/code> and CONTINUE IDENTITY<\/code>.  <\/p>\n\n\n\n

What are the key differences in how foreign key relationships work in MySQL compared to PostgreSQL?<\/h2>\n\n\n\n

Foreign key relationships<\/a> are handled differently in MySQL and PostgreSQL.<\/strong> 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. <\/p>\n\n\n\n

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<\/a><\/code> option:<\/p>\n\n\n\n

TRUNCATE TABLE customers CASCADE;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This can also truncate tables that depend on customers through foreign key relationships.<\/p>\n\n\n\n

Because CASCADE<\/code> can affect more tables than the one named in the statement, it should be used carefully, particularly in production environments.<\/p>\n\n\n\n

PostgreSQL also supports truncating several related tables explicitly:<\/p>\n\n\n\n

TRUNCATE TABLE customers, orders;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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

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

A statement such as:<\/p>\n\n\n\n

DELETE FROM customers;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

removes rows through the normal row-level deletion mechanism and can be combined with a WHERE<\/code> clause. TRUNCATE<\/code>, by contrast, removes all<\/em> rows from the table, and does not<\/em> support a WHERE<\/code> clause.<\/p>\n\n\n\n

Why this matters<\/h3>\n\n\n\n

The distinction matters during migration. Replacing one with the other can change transaction behavior, trigger<\/a> behavior, identity handling, and the way foreign key relationships are handled.<\/p>\n\n\n\n

The basic TRUNCATE TABLE<\/code> 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<\/a>, automatically- generated identifiers, foreign key dependencies, and cascading behavior.<\/p>\n\n\n\n

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

In summary<\/h2>\n\n\n\n

The important point during migration is not to treat the two commands as interchangeable – and never<\/em> assume that a TRUNCATE<\/code> statement copied from MySQL will have exactly the same effect in PostgreSQL.<\/strong><\/p>\n\n\n\n

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. <\/p>\n\n\n\n

The same becomes even more apparent when logic moves beyond individual SQL statements and into stored procedures, functions, and triggers.<\/p>\n\n\n\n

\n
\n
\n
\n

Simple Talk is brought to you by Redgate Software<\/h3>\n
\n Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI. <\/div>\n <\/div>\n Discover how Redgate can help you<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n
\n

FAQs<\/h2>\n\n

1. Why does a MySQL query with backticks fail in PostgreSQL?<\/h3>\n
\n

MySQL uses backticks to quote identifiers like table and column names, while PostgreSQL follows the SQL standard and uses double quotes instead. Backticks aren’t recognized as identifier delimiters in PostgreSQL, so any query copied over without modification will throw a syntax error.<\/p>\n <\/div>\n

2. Does PostgreSQL support MySQL's AUTO_INCREMENT?<\/h3>\n
\n

No. PostgreSQL doesn’t recognize the AUTO_INCREMENT<\/code> attribute. Instead, it offers the SERIAL<\/code> pseudo-type (a shorthand for a sequence-backed column) or the SQL-standard GENERATED ... AS IDENTITY<\/code> syntax, which is now the recommended approach for new schemas.<\/p>\n <\/div>\n

3. Can I just replace AUTO_INCREMENT with SERIAL and call it done?<\/h3>\n
\n

Not quite. While SERIAL<\/code> fixes the syntax error, it doesn’t behave identically to MySQL’s version; PostgreSQL’s SERIAL<\/code> relies on a separate sequence object and doesn’t automatically create a PRIMARY KEY<\/code> or UNIQUE<\/code> constraint, unlike MySQL’s SERIAL<\/code> alias.<\/p>\n <\/div>\n

4. Does MySQL's LIMIT clause work the same way in PostgreSQL?<\/h3>\n
\n

Mostly, yes. Basic LIMIT<\/code> and OFFSET<\/code> syntax is supported in both. However, MySQL’s shorthand LIMIT offset, count<\/code> isn’t supported in PostgreSQL and must be rewritten as LIMIT count OFFSET offset<\/code>, or using the SQL-standard FETCH FIRST<\/code> clause.<\/p>\n <\/div>\n

5. What's the PostgreSQL equivalent of MySQL's ON DUPLICATE KEY UPDATE?<\/h3>\n
\n

PostgreSQL uses ON CONFLICT<\/code> instead. You specify a conflict target (like a column with a unique constraint) and choose whether to update the existing row (DO UPDATE<\/code>), or skip it entirely (DO NOTHING<\/code>) – which also replaces MySQL’s INSERT IGNORE<\/code>.<\/p>\n <\/div>\n

6. Is TRUNCATE TABLE safe to use inside a transaction in PostgreSQL?<\/h3>\n
\n

Yes. PostgreSQL treats TRUNCATE<\/code> as fully transactional, so it can be rolled back. This differs from MySQL’s InnoDB engine, where TRUNCATE<\/code> causes an implicit commit and can’t be undone the same way.<\/p>\n <\/div>\n <\/section>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Migrating from MySQL to PostgreSQL? Learn the key SQL syntax differences – identifier quoting, auto-increment, LIMIT vs FETCH FIRST, and more.…<\/p>\n","protected":false},"author":341597,"featured_media":107513,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,145792,143534,143531],"tags":[159268,4168,4170,5854,158978,4150,4252],"coauthors":[158988],"class_list":["post-112395","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-mysql","category-postgresql","category-t-sql-programming-sql-server","tag-aishabukar_mysql_postgresql","tag-database","tag-database-administration","tag-mysql","tag-postgresql","tag-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112395","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341597"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=112395"}],"version-history":[{"count":20,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112395\/revisions"}],"predecessor-version":[{"id":112890,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112395\/revisions\/112890"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/107513"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=112395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=112395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=112395"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=112395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}