Migrating a SpringBoot + MyBatisPlus + MySQL Project to PostgreSQL: Common Pitfalls and Helper Scripts

The original project is built with SpringBoot, MyBatisPlus, and MySQL.

1. Switch Process

1.1 Add PostgreSQL driver

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

1.2 Modify JDBC connection

spring:
  datasource:
    # modify driver class
    driver-class-name: org.postgresql.Driver
    # modify URL
    url: jdbc:postgresql://<数据库地址>/<数据库名>?currentSchema=模式名&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

PostgreSQL introduces schema concepts; a database can contain multiple schemas, and the default schema is public if not specified.

2. Pitfall Records

2.1 TIMESTAMPTZ vs LocalDateTime

PostgreSQL TIMESTAMPTZ cannot be mapped directly to Java LocalDateTime . Use timestamp in the DB or map to java.util.Date .

2.2 Parameter quoting

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

2.3 Backticks around identifiers

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

2.4 JSON field extraction

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

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

2.5 convert() function

-- MySQL:
select convert(name, DECIMAL(20,2))

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

2.6 force index

MySQL supports force index , PostgreSQL does not; remove this clause.

2.7 ifnull()

Replace MySQL ifnull with PostgreSQL COALESCE .

2.8 date_format()

Replace MySQL date_format with PostgreSQL to_char and map format symbols accordingly.

2.9 GROUP BY rules

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

2.10 Transaction abort

If any statement in a transaction fails, subsequent statements are ignored until the transaction ends; avoid controlling logic via DB exceptions.

2.11 Type conversion errors

PostgreSQL enforces strict type matching; create implicit cast functions if needed, but be cautious of ambiguous operator resolution.

3. PostgreSQL Helper Scripts

3.1 Batch modify 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 = '要处理的模式名'
        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 timestamps

DO $$
DECLARE rec RECORD;
BEGIN
  FOR rec IN SELECT table_name, column_name, data_type
      FROM information_schema.columns
      WHERE table_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, keep field types consistent; tinyint should become smallint, not boolean. Avoid using TIMESTAMPTZ with Java LocalDateTime . If automatic type conversion is required, add implicit cast functions, but remember they must be re‑executed after each PostgreSQL deployment.