Migrate SpringBoot MyBatis from MySQL to PostgreSQL: A Complete Guide

0. Introduction

Original project uses SpringBoot + MybatisPlus + MySQL.

1. Migration Steps

1.1 Add PostgreSQL driver dependency

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

1.2 Modify JDBC connection information

Switch driver class and URL 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 schema concept; a database can contain multiple schemas. The schema name is equivalent to the previous MySQL database name; default is public if not specified.

2. Pitfall Records

2.1 TIMESTAMPTZ type vs LocalDateTime mismatch

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

2.2 Parameter values must use single quotes

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

2.3 Field identifiers must not be wrapped with backticks

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

2.4 JSON field extraction 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
select xx FROM user force index(idx_audit_time)
-- PostgreSQL
-- remove force index clause

2.7 ifnull function does not exist

Replace with COALESCE in PostgreSQL.

2.8 date_format function does not exist

Use to_char with appropriate pattern.

// %Y => YYYY
// %m => MM
// %d => DD
// %H => HH24
// %i => MI
// %s => SS
to_char(time, 'YYYY-MM-DD')   ==>   DATE_FORMAT(time, '%Y-%m-%d')
to_char(time, 'YYYY-MM')       ==>   DATE_FORMAT(time, '%Y-%m')
to_char(time, 'YYYYMMDDHH24MISS') ==> DATE_FORMAT(time, '%Y%m%d%H%i%s')

2.9 GROUP BY requirements

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

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

2.10 Transaction abort issues

If an error occurs within a transaction, subsequent commands are ignored until the transaction ends. Avoid controlling logic by catching database exceptions; instead, manually check conditions.

2.11 Type conversion errors (smallint ↔ boolean)

PostgreSQL is strict about type matching; mismatched types cause errors. Solutions:

Manually align field types in code and database.

Create implicit conversion functions.

Example functions to convert between smallint and boolean and corresponding casts are provided.

-- 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
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
CREATE CAST (BOOLEAN AS SMALLINT) WITH FUNCTION boolean_to_smallint AS IMPLICIT;

To remove these functions and casts, use DROP FUNCTION and DROP CAST statements.

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 CURRENT_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, ensure field types correspond correctly.

Convert tinyint to smallint, not boolean.

Avoid using TIMESTAMPTZ for LocalDateTime fields; use timestamp instead.

PostgreSQL does not automatically convert between tinyint and boolean. Either add implicit conversion functions (requiring execution after each deployment) or adjust code and schema to match types.