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

• MySQL
• PostgreSQL
• SQLynx

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:

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:

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.

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

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.

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.

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

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.

1. Preview the data to be imported. After confirmation, click Submit to execute the 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.

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.

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.

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(other_column);

• Data Cleaning

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.