How to Seamlessly Migrate SpringBoot MySQL Projects to PostgreSQL: Pitfalls & Scripts

0. Introduction

Original project uses SpringBoot + MybatisPlus + MySQL.

1. Migration Process

1.1 Add PostgreSQL driver

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

1.2 Modify JDBC connection

Replace MySQL driver class and URL with PostgreSQL equivalents.

spring:
  datasource:
    ## modify driver class
    driver-class-name: org.postgresql.Driver
    ## modify connection URL
    url: jdbc:postgresql://<database_address>/<database_name>?currentSchema=<schema_name>&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT+8&useSSL=false

PostgreSQL introduces a schema concept; the schema name replaces the previous MySQL database name, and the default schema is public.

2. Common Pitfalls

2.1 TIMESTAMPTZ vs LocalDateTime

PostgreSQL TIMESTAMPTZ cannot be mapped to java.time.LocalDateTime; use timestamp or change the Java type.

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

2.2 Use single quotes for string literals

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

2.3 Do not quote column names with backticks

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

2.4 JSON field syntax differences

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

2.5 convert() function missing

Replace MySQL convert() with CAST in PostgreSQL.

-- MySQL
select convert(name, DECIMAL(20,2))
-- PostgreSQL
select CAST(name AS DECIMAL(20,2))

2.6 force index not supported

PostgreSQL does not have FORCE INDEX; remove it from queries.

2.7 ifnull() function missing

Use COALESCE instead of IFNULL.

2.8 date_format() function missing

Replace DATE_FORMAT with to_char and adjust format patterns.

// %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

All selected columns must appear in GROUP BY or be aggregated in PostgreSQL.

select name, age, count(*)
from user
group by age, score   -- error: column "name" must appear in the GROUP BY clause

2.10 Transaction abort behavior

If any statement in a transaction fails, subsequent statements are ignored until the transaction ends.

ERROR: current transaction is aborted, commands ignored until end of transaction block

2.11 Type conversion errors

PostgreSQL enforces strict type matching; mismatched boolean/smallint or other types cause errors. Solutions include adjusting Java field types, adding explicit casts, or creating implicit conversion functions.

ERROR: operator does not exist: smallint = boolean
ERROR: column "name" is of type smallint but expression is of type boolean

3. PostgreSQL Helper Scripts

3.1 Batch convert 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 = 'target_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 Batch set default CURRENT_TIMESTAMP for create_time / update_time

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT table_name, column_name
        FROM information_schema.columns
        WHERE table_schema = 'target_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

Ensure field types match when migrating from MySQL to PostgreSQL.

Convert tinyint to smallint; avoid using boolean for tinyint equivalents.

Do not use TIMESTAMPTZ for Java LocalDateTime fields.

If automatic type conversion is needed, you can create implicit cast functions, but they must be maintained after each deployment.