MySQL to PostgreSQL

About MySQL to PostgreSQL Converter

MySQL to PostgreSQL Converter helps you move a database. Paste your MySQL script and the PostgreSQL version shows in the Result pane as you type. Press Convert to jump to it, then Copy or Download it.

Open Changes in the Result header to see every change next to its line. Changes marked By hand are things the converter cannot rewrite safely, with a short note on what to do. Format tidies the MySQL before you convert it.

The converter uses fixed rules and runs in your browser. Your SQL is not sent anywhere.

Customization

Open Options to keep names as written or make them lower case, and to choose IDENTITY or SERIAL for auto-increment columns. The tool remembers your choices.

Common MySQL to PostgreSQL conversions

Comments

MySQL accepts # for single-line comments; PostgreSQL uses --. Both use /* */ for comments over several lines.

Date and time

To get the current year, MySQL uses YEAR(CURDATE()), while PostgreSQL uses EXTRACT(YEAR FROM CURRENT_DATE).

Update statements

MySQL writes UPDATE ... INNER JOIN ... ON ... SET ... WHERE, while PostgreSQL writes UPDATE ... SET ... FROM ... WHERE. The converter marks these for you to rewrite.

Regular expressions

MySQL matches with REGEXP, PostgreSQL with ~.

String functions

MySQL joins strings with CONCAT, PostgreSQL usually with ||. Both give NULL when one part is NULL.

Putting it all together

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  YEAR(CURDATE()) - YEAR(birth_date) AS age
FROM visitors
WHERE membership = 'gold' # gold members get a discount

SELECT
  first_name || ' ' || last_name AS full_name,
  EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM birth_date) AS age
FROM visitors
WHERE membership = 'gold' -- gold members get a discount

About MySQL

A brief history of MySQL

MySQL is named after My, the daughter of co-founder Michael Widenius, and SQL (Structured Query Language). It was released in 1995 by the Swedish company MySQL AB, which was acquired by Sun Microsystems in 2008. Oracle Corporation bought Sun Microsystems in 2010. MySQL is still open source, with a free community edition and a paid enterprise edition.

What makes MySQL different?

MySQL has features and syntax that differ from other SQL databases. One example is the AUTO_INCREMENT attribute, which gives each new row a unique number:

CREATE TABLE employees (
  id INT AUTO_INCREMENT,
  name VARCHAR(100),
  PRIMARY KEY (id)
);

PostgreSQL does not have AUTO_INCREMENT. It uses an identity column, or the older SERIAL type:

CREATE TABLE employees (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY,
  name VARCHAR(100),
  PRIMARY KEY (id)
);

Another MySQL feature is the ENUM column, which only accepts values from a list. In PostgreSQL, you write the same rule with a CHECK constraint, or create an enum type with CREATE TYPE.

CREATE TABLE shirts (
  name VARCHAR(40),
  size ENUM('small', 'medium', 'large')
);

CREATE TABLE shirts (
  name VARCHAR(40),
  size TEXT CHECK (size IN ('small', 'medium', 'large'))
);

Why is MySQL so popular?

• Open source. Anyone can read and improve the code, and the community edition is free.
• Easy to start with. It is included in most web hosting plans and works well with PHP, Python and Node.js.
• Storage engines. Its pluggable storage-engine architecture lets you choose a table type for each use, with InnoDB as the default.
• Replication. MySQL has built-in replication, so a database can be copied to other servers for backup or scale.

About PostgreSQL

The origins of PostgreSQL

PostgreSQL began as the POSTGRES project at the University of California, Berkeley, led by Professor Michael Stonebraker from 1986. In 1994, Andrew Yu and Jolly Chen added an SQL interpreter and released it as Postgres95. In 1996 it was renamed PostgreSQL, starting at version 6.0. Many people still call it Postgres.

How does PostgreSQL stand apart?

• Standards. PostgreSQL follows the SQL standard closely.
• Extensible. You can add your own data types, functions, operators and index types, and extensions such as PostGIS.
• Procedural languages. Functions can be written in PL/pgSQL, PL/Python, PL/Perl and more.
• ACID. Transactions are atomic, consistent, isolated and durable, also for schema changes.
• Indexes. B-tree, hash, GiST, SP-GiST, GIN and BRIN indexes cover many kinds of data.

Arrays, JSONB and window functions

PostgreSQL columns can hold arrays, and JSONB stores JSON in a binary form that is fast to search:

CREATE TABLE wizards (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  spells TEXT[],
  traits JSONB
);

Common table expressions (CTEs) and window functions make complex queries readable:

WITH sales_by_product AS (
  SELECT product, SUM(sales) OVER (PARTITION BY product) AS product_sales
  FROM sales
)
SELECT DISTINCT product, product_sales
FROM sales_by_product
WHERE product_sales > 10000;

Who is behind PostgreSQL?

No single company owns PostgreSQL. The PostgreSQL Global Development Group, a community of developers and companies, develops it under the permissive PostgreSQL License. Many companies offer commercial support and hosting.