Migrating a SpringBoot + MyBatisPlus + MySQL Project to PostgreSQL: Steps, Pitfalls, and Scripts

0. Preface: The original project uses SpringBoot, MyBatisPlus and MySQL.

1. Switch Process

1.1 Add PostgreSQL driver dependency:

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

1.2 Modify JDBC connection information to use the PostgreSQL driver and URL, and note that PostgreSQL introduces schema concepts.

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

2. Pitfall Records

2.1 TIMESTAMPTZ type does not match java.time.LocalDateTime; use timestamp or map to Date.

2.2 Parameter values must use single quotes, not double quotes.

2.3 Column identifiers must not be wrapped with backticks.

2.4 JSON field extraction syntax differs: MySQL uses ->'$.name', PostgreSQL uses ->>'name'.

2.5 MySQL convert() function does not exist in PostgreSQL; use CAST.

2.6 FORCE INDEX syntax is not supported in PostgreSQL; remove it.

2.7 ifnull() function is absent; replace with COALESCE.

2.8 date_format() function is absent; replace with to_char and map format patterns.

2.9 GROUP BY requirements are stricter in PostgreSQL; selected columns must appear in GROUP BY or be aggregated.

2.10 Transaction abort behavior: after an error the whole transaction is aborted; avoid using DB exceptions for control flow.

2.11 Type conversion errors: PostgreSQL enforces strict type matching; create implicit cast functions between SMALLINT and BOOLEAN if needed, but be careful of ambiguous operator selection.

-- example of creating conversion functions and casts
CREATE OR REPLACE FUNCTION "smallint_to_boolean"(i int2)
RETURNS "pg_catalog"."bool" AS $BODY$
BEGIN
  RETURN (i::int2)::integer::bool;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

create cast (SMALLINT as BOOLEAN) with function smallint_to_boolean as ASSIGNMENT;

CREATE OR REPLACE FUNCTION "boolean_to_smallint"(b bool)
RETURNS "pg_catalog"."int2" AS $BODY$
BEGIN
  RETURN (b::boolean)::bool::int;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

create cast (BOOLEAN as SMALLINT) with function boolean_to_smallint as implicit;

3. PostgreSQL helper scripts

3.1 Batch script to change columns of type 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 = 'schema_name'
                 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 script to set default CURRENT_TIMESTAMP for create_time and update_time columns of type timestamp without time zone:

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

1) Ensure field types correspond when migrating; avoid changing *.

2) Tinyint in MySQL should become smallint in PostgreSQL, not boolean.

3) Do not map LocalDateTime to TIMESTAMPTZ; use timestamp instead.

4) MySQL's automatic tinyint‑boolean conversion is not present in PostgreSQL; either add implicit cast functions or adjust code and schema accordingly.