Migrating MySQL Database to PostgreSQL with SQL Tools

Review all blog information to know more about databases and our products.

Migrating MySQL Database to PostgreSQL with SQL Tools

In daily works, sometimes we may need to convert our database, and migrate the date to new database. In this article, I will introduce a method to convert and migrate database from mysql to postgresql with sql tools.

1. Required Tools

2. Differences Between MySQL and PostgreSQL

2.1 Data Structure

Types MySQL PostgreSQL
String Data Types CHAR, VARCHAR, TEXT, BLOB CHAR, VARCHAR, TEXT, BYTEA
Numeric Data Types TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
Date and Time Data Types DATE, DATETIME, TIMESTAMP, TIME, YEAR DATE, TIMESTAMP, TIMESTAMPTZ, TIME, TIMETZ, INTERVAL
Timestamps Types TIMESTAMP does not include timezone information TIMESTAMPTZ includes timezone information
Boolean Data Type TINYINT(1) BOOLEAN
JSON Data Type JSON JSON, JSONB

2.2 Basic Syntax

Types MySQL PostgreSQL
String Concatenation CONCAT(str1, str2) str1||str2
Substring SUBSTRING(str, pos, len) SUBSTRING(str FROM pos FOR len)
String Search LOCATE(substring, string) POSITION(substring IN string)
Random Order ORDER BY RAND() ORDER BY RANDOM()
Boolean Query SELECT * FROM table WHERE boolean_column = 1; SELECT * FROM table WHERE boolean_column IS TRUE;
Start Transaction START TRANSACTION BEGIN
Round Function ROUND(column, decimals) ROUND(column::numeric, decimals)
Null Value Check Supports IFNULL(), NVL(), COALESCE() Supports COALESCE()
String Constants Supports single and double quotes Supports double quotes
Comments # or -- --
Database Name Length No enforced limit Recommended total length for database and table names ≤ 63 characters

2.3 Other

Types MySQL PostgreSQL
Tablespaces and Schemas Uses databases and tablespaces, does not support multiple schemas Supports multiple schemas within a single database
Sequences and Auto-Increment Columns Uses AUTO_INCREMENT to create auto-increment columns Uses SERIAL or BIGSERIAL data types, or SEQUENCE
Views and Materialized Views Supports views, does not support materialized views Supports both views and materialized views
Index Types Supports B-tree indexes, full-text indexes, hash indexes (Memory storage engine) Supports B-tree indexes, hash indexes, GIN, GiST, SP-GiST, BRIN
Partitioned Tables Supports horizontal partitioning (RANGE, LIST, HASH, KEY) Supports horizontal partitioning (RANGE, LIST, HASH), with more flexibility and features

3. Table Creation and Data Migration

3.1 DDL Statements for Table Creation and Data Migration

3.1.1 Table Creation with DDL Statements

Refer to the MySQL DDL statements for the source tables and modify them to fit PostgreSQL syntax. Execute the modified DDL statements at the target location to create new tables in PostgreSQL.

Here is a reference for converting MySQL statements to PostgreSQL:

MySQL Statement Convert to PostgreSQL
AUTO_INCREMENT Change to SERIAL
IFNULL Use COALESCE() function
BINARY Can be removed (PostgreSQL is case-sensitive)
DATE_FORMAT() Change to TO_CHAR
IF() Change to CASE WHEN condition THEN value ELSE value END
SYSDATE() Change to NOW()
Single Quotes for Column Names Change to Double Quotes
ENGINE=, DEFAULT CHARSET= Remove (PostgreSQL does not require)
COMMENT Aggregate all comments at the end and use COMMENT ON
DATETIME Change to TIMESTAMP
TINYINT(1) Change to BOOLEAN

For example:
MySQL Source Table:

1
2
3
4
5
6
7
8
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'order id',
order_date DATETIME NOT NULL COMMENT 'order date',
customer_id INT NOT NULL COMMENT 'customer id',
total DECIMAL(10, 2) COMMENT 'total amount',
status TINYINT(1) COMMENT 'status',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='order table';

Converted to PostgreSQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_date TIMESTAMP NOT NULL,
customer_id INT NOT NULL,
total NUMERIC(10, 2),
status BOOLEAN,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);

COMMENT ON TABLE orders IS 'Order Table';
COMMENT ON COLUMN orders.order_id IS 'Order ID';
COMMENT ON COLUMN orders.order_date IS 'Order Date';
COMMENT ON COLUMN orders.customer_id IS 'Customer ID';
COMMENT ON COLUMN orders.total IS 'Total Amount';
COMMENT ON COLUMN orders.status IS 'Status';
COMMENT ON COLUMN orders.created_at IS 'Creation Time';

3.1.2 Data Migration
Use SQLynx for data migration.

  1. Right-click the source table name and select Data Migration.

data migration

  1. In the popup, choose the target table location.

data migration

  1. Select the data migration mode, such as Stop on Error, Continue on Error, Transaction Execution, and whether to clear existing data in the target table.

data migration

  1. Confirm and click Submit to execute the migration.

3.2 Create Table by Import

You can export data from MySQL source tables to local files and import them into PostgreSQL with SQLynx.

  1. Select the PostgreSQL target location, right-click the table, and choose Create Table by Import.

Create Table by Import

  1. In the popup, select the local source file.

Create Table by Import

  1. In Table Mapping, column names are automatically recognized. The default data type is varchar, but you can modify it to other types such as timestamp, int, etc., as needed.

Create Table by Import

  1. Preview the data to be imported. After confirmation, click Submit to execute the import.

Create Table by Import

  1. Add constraints to the table, such as primary keys, unique keys, foreign keys, etc.

4. Data Validation

Verify the data in the target tables by executing queries to ensure that the import is completed as expected.

5. Considerations

5.1 Data Cleaning
Before adding constraints to existing data tables, check and clean the data.

  • Primary Keys: Ensure that all values in the column are unique and non-null. If there are duplicates or nulls, clean the data first.

    1
    ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (column_name);
  • Unique Keys: Ensure all values in the column are unique. Clean the data if duplicates exist.

    1
    ALTER TABLE table_name ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);
  • Foreign Keys: Ensure data consistency. Values in foreign key columns must exist in the referenced table.

    1
    ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(other_column);
  • Data Cleaning

    1
    2
    DELETE FROM table_name WHERE column_name IS NULL;
    DELETE FROM table_name t1 USING table_name t2 WHERE t1.id > t2.id AND t1.column_name = t2.column_name;

5.2 Performance Impact

  1. Adding a primary key involves a full table scan, which may impact performance, especially for large tables. It is advisable to perform this operation during low load, off-peak hours.
  2. In high-load environments, consider adding constraints in batches.