Migrating MySQL to PostgreSQL with SpringBoot: Common Pitfalls & Scripts

0. Preface

Original project framework: SpringBoot + MybatisPlus + MySQL

1. Switch Process

1.1 Add PostgreSQL driver dependency

Because we need to connect to a new database, we add the PostgreSQL driver dependency similar to the MySQL driver.

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

1.2 Modify JDBC connection information

Change driver class and URL from MySQL to PostgreSQL.

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%2B8&useSSL=false

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

After these changes the switch is basically done, but many pitfalls remain because the two databases differ in syntax.

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 type or change the Java field to Date.

2.2 Parameter values cannot use double quotes

Wrong example: WHERE name = "jay" → correct:

WHERE name = 'jay'

2.3 Field names cannot be wrapped with backticks

Wrong example: WHERE `name` = 'jay' → correct:

WHERE name = 'jay'

2.4 JSON field handling syntax differs

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

MySQL uses ->'$.xxx', PostgreSQL uses ->>'xx' to select JSON attributes.

2.5 convert function does not exist

PostgreSQL lacks convert; use CAST instead.

-- MySQL syntax:
select convert(name, DECIMAL(20, 2))
-- PostgreSQL syntax:
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 ifnull with COALESCE in PostgreSQL.

2.8 date_format function does not exist

Replace date_format with to_char and map 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 syntax issue

PostgreSQL requires selected columns to appear in GROUP BY or be aggregated. MySQL allows non‑aggregated columns.

2.10 Transaction exception

When an error occurs in a transaction, PostgreSQL aborts the whole transaction. Do not rely on database exceptions for control flow; manually check conditions.

2.11 Type conversion exceptions

MySQL auto‑converts types (e.g., tinyint ↔ Boolean). PostgreSQL is strict; mismatched types cause errors. Solutions: adjust Java and column types to match, or create implicit cast functions.

-- Create function smallint_to_boolean
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 function boolean_to_smallint
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;

Remove these functions and casts if not needed.

3. PostgreSQL Helper Scripts

3.1 Batch modify timestamptz to timestamp

Convert all timestamptz columns to timestamp to match LocalDateTime.

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 set default timestamp values

Set default CURRENT_TIMESTAMP for columns create_time or update_time of type 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 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. Notes

When migrating tables, ensure field types correspond; avoid arbitrary changes. tinyint in MySQL should be mapped to smallint in PostgreSQL, not boolean.

Do not use TIMESTAMPTZ for Java LocalDateTime fields; use timestamp instead.

MySQL’s automatic tinyint ↔ Boolean conversion is not supported by PostgreSQL; you can add implicit cast functions, but they must be executed after each deployment.

If you prefer not to add casts, modify code and database schemas to keep types consistent.