Migrating a SpringBoot MySQL Project to PostgreSQL: Process, Pitfalls, and Helper Scripts

Author: Li Bai's Phone

0. Introduction

Original project stack: SpringBoot + MybatisPlus + MySQL.

1. Migration Process

1.1 Add PostgreSQL driver dependency

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

1.2 Modify JDBC connection information

spring:
  datasource:
    # change driver class
    driver-class-name: org.postgresql.Driver
    # change URL
    url: jdbc:postgresql://
host
/
database
?currentSchema=
schema
&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

PostgreSQL introduces the concept of schemas; a database can contain multiple schemas. The schema name corresponds to the former MySQL database name. If not specified, the default is public .

The basic switch is done by changing the connection settings, but many hidden issues remain because the two databases differ in syntax and behavior.

2. Pitfall Records

2.1 TIMESTAMPTZ type does not match LocalDateTime

Exception:

PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type java.time.LocalDateTime.

If a PostgreSQL column is TIMESTAMPTZ but the Java field is LocalDateTime , conversion fails. Use timestamp in the table or change the Java field to Date .

2.2 Parameter values cannot use double quotes

Wrong:

WHERE name = "jay"   =>   WHERE name = 'jay'

2.3 Field names must not be wrapped with backticks

WHERE `name` = 'jay'   =>   WHERE name = 'jay'

2.4 JSON field extraction syntax differs

-- MySQL
WHERE keywords_json->'$.name' LIKE CONCAT('%', ?, '%')

-- PostgreSQL
WHERE keywords_json->>'name' LIKE CONCAT('%', ?, '%')

2.5 convert function does not exist

-- MySQL
SELECT convert(name, DECIMAL(20,2))

-- PostgreSQL
SELECT CAST(name AS DECIMAL(20,2))

2.6 force index syntax does not exist

MySQL supports force index ; PostgreSQL does not. Remove it.

2.7 ifnull function does not exist

Replace with COALESCE in PostgreSQL.

2.8 date_format function does not exist

Replace with to_char and map format symbols:

// %Y => YYYY
// %m => MM
// %d => DD
// %H => HH24
// %i => MI
// %s => SS

to_char(time, 'YYYY-MM-DD')   =>   DATE_FORMAT(time, '%Y-%m-%d')

2.9 GROUP BY requirements

PostgreSQL requires every selected column to appear in GROUP BY or be aggregated, unlike MySQL.

2.10 Transaction abort issues

If any statement in a transaction fails, subsequent statements are ignored until the transaction ends. MySQL usually does not exhibit this behavior.

2.11 Type conversion errors (smallint ↔ boolean)

MySQL auto‑converts types; PostgreSQL is strict. Solutions: manually align Java field types with PostgreSQL column types, or create implicit cast functions.

3. PostgreSQL Helper Scripts

3.1 Bulk change TIMESTAMPTZ to timestamp

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT table_name, column_name, data_type
               FROM information_schema.columns
               WHERE table_schema = 'your_schema'
                 AND data_type = 'timestamp with time zone'
    LOOP
        EXECUTE 'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' TYPE timestamp';
    END LOOP;
END $$;

3.2 Bulk set default value for timestamp columns named create_time or update_time

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT table_name, column_name, data_type
               FROM information_schema.columns
               WHERE table_schema = 'your_schema'
                 AND data_type = 'timestamp without time zone'
                 AND column_name IN ('create_time','update_time')
    LOOP
        EXECUTE 'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' SET DEFAULT CURRENT_TIMESTAMP';
    END LOOP;
END $$;

4. Important Notes

When migrating tables, keep field types consistent; avoid using * wildcard changes.

Convert tinyint to smallint , not to boolean .

Do not map Java LocalDateTime to PostgreSQL TIMESTAMPTZ ; use timestamp instead.

MySQL’s tinyint ↔ Java Boolean auto‑conversion does not exist in PostgreSQL; you can add implicit cast functions, but they must be executed after each PostgreSQL deployment.

If you prefer not to add implicit casts, modify all Java field types or PostgreSQL column types to match exactly.

Feel free to discuss, ask questions, or contact the author for further clarification.