Migrate SpringBoot MySQL to PostgreSQL: Step‑by‑Step Guide & Common Pitfalls

Original project framework: SpringBoot + MybatisPlus + MySQL.

1. Switching Process

1.1 Add PostgreSQL driver package

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

1.2 Modify JDBC connection information

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 the concept of schemas; a database can contain multiple schemas. The schema name is equivalent to the previous MySQL database name. If not specified, the default is public.

The basic switch is finished by changing the connection info, but many pitfalls remain because the two databases differ in syntax.

2. Pitfall Records

2.1 TIMESTAMPTZ type does not match LocalDateTime

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

2.2 Parameter values cannot use double quotes

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

2.3 Field names cannot be wrapped with backticks

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

2.4 JSON field handling 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(idx_audit_time); PostgreSQL does not, so 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. Example mappings: %Y → YYYY, %m → MM, %d → DD, %H → HH24, %i → MI, %s → SS.

2.9 GROUP BY syntax issue

In PostgreSQL every selected column must appear in GROUP BY or be aggregated. Adjust queries accordingly.

2.10 Transaction exception

If an error occurs inside a transaction, all subsequent commands are ignored until the transaction ends. Do not control business logic by catching database exceptions; instead perform explicit checks.

2.11 Type conversion exceptions (major)

PostgreSQL enforces strict type matching. Resolve by either adjusting Java field types to match PostgreSQL column types or creating implicit conversion functions (e.g., smallint_to_boolean, boolean_to_smallint) and corresponding casts.

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 = '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 Batch set default timestamp for create_time / 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 from MySQL to PostgreSQL, ensure field types correspond; avoid automatic type changes.

MySQL tinyint usually maps to PostgreSQL smallint, not boolean.

Java LocalDateTime should use PostgreSQL timestamp, not TIMESTAMPTZ.

MySQL tinyint ↔ Java Boolean works with automatic conversion; PostgreSQL requires explicit casts or implicit conversion functions.

If you prefer not to add implicit conversion functions, modify all Java field types or database column types to match PostgreSQL strictly.