Essential Tips for Migrating MySQL to PostgreSQL with SpringBoot

Preface

Original project uses SpringBoot + MyBatisPlus + MySQL. It needs to be migrated to PostgreSQL, and this article shares the pitfalls encountered during the process.

Migration Process

Introduce PostgreSQL driver

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

Modify JDBC connection

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

PostgreSQL adds a schema concept; a database can contain multiple schemas. If not specified, the default is public.

After changing the connection, many SQL syntax differences cause further issues.

Pitfalls

TIMESTAMPTZ type does not match LocalDateTime

PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type java.time.LocalDateTime.

Solution: use timestamp type or change the Java field to Date.

Parameter values cannot use double quotes

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

Fields cannot be wrapped with backticks

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

JSON field syntax differences

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

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

convert function does not exist

Replace with CAST.

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

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

force index syntax does not exist

Remove the force index clause; PostgreSQL does not support it.

ifnull function does not exist

Replace with COALESCE.

date_format function does not exist

Replace with to_char and map format symbols.

// %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')

GROUP BY syntax differences

PostgreSQL requires selected columns to appear in the GROUP BY clause or be used in an aggregate function, unlike MySQL.

Transaction abort behavior

In PostgreSQL, an error aborts the whole transaction; subsequent statements are ignored until the transaction ends. Avoid using database exceptions for control flow.

Type conversion errors (biggest)

MySQL automatically converts mismatched types; PostgreSQL is strict and requires exact type matches. Align Java field types with PostgreSQL column types or create implicit cast functions.

Boolean and smallint conversion errors

Examples of SELECT and UPDATE failures when a smallint column is compared or assigned a boolean value.

-- SELECT error
SELECT ... WHERE enable = true
-- UPDATE error
UPDATE ... SET name = false WHERE name = true

Solution: add explicit cast functions or adjust code.

-- Create function smallint_to_boolean(i int2) returns bool ...
CREATE OR REPLACE FUNCTION "smallint_to_boolean"(i int2)
RETURNS 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(b bool) returns int2 ...
CREATE OR REPLACE FUNCTION "boolean_to_smallint"(b bool)
RETURNS int2 AS $BODY$
BEGIN
  RETURN (b::bool)::integer::int2;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;
CREATE CAST (BOOLEAN AS SMALLINT) WITH FUNCTION boolean_to_smallint AS IMPLICIT;

PostgreSQL Helper Scripts

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 $$;

Batch set default CURRENT_TIMESTAMP for create_time and update_time

DO $$
DECLARE rec RECORD;
BEGIN
  FOR rec IN SELECT table_name, column_name
      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 $$;

Precautions

Ensure field types correspond when migrating; avoid mismatched types.

Convert tinyint to smallint, not to boolean.

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

Implicit conversion functions can cause ambiguous operator errors; use them cautiously.