Migrating a SpringBoot Project from MySQL to PostgreSQL: Common Pitfalls and Solutions

0. Introduction

Original project uses SpringBoot, MybatisPlus, and MySQL.

1. Migration Steps

1.1 Add PostgreSQL driver

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

1.2 Modify JDBC connection information

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://<数据库地址>/<数据库名>?currentSchema=<模式名>&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

PostgreSQL introduces the concept of schemas; a database can contain multiple schemas. The schema name is equivalent to the old MySQL database name and defaults to public if omitted.

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.

Solution: Use timestamp column type in PostgreSQL or change the Java field type to java.util.Date.

2.2 Parameter values cannot use double quotes

Wrong: WHERE name = "jay" Correct:

WHERE name = 'jay'

2.3 Field identifiers must not be wrapped with backticks

Wrong: WHERE `name` = 'jay' Correct:

WHERE name = 'jay'

2.4 JSON field extraction syntax differs

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

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

MySQL uses -> '$.xxx' while PostgreSQL uses ->> 'xxx' to select a JSON attribute.

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 has no force index hint; the clause should be removed.

2.7 ifnull function does not exist

Replace ifnull(a,b) with COALESCE(a,b) in PostgreSQL.

2.8 date_format function does not exist

Replace DATE_FORMAT(time, '%Y-%m-%d') with to_char(time, 'YYYY-MM-DD'). Mapping of format specifiers: %Y →

YYYY

%m

→

MM

%d

→

DD

%H

→

HH24

%i

→

MI

%s

→

SS

2.9 GROUP BY syntax differences

PostgreSQL requires every selected column to appear in the GROUP BY clause or be wrapped in an aggregate function, unlike MySQL which allows non‑aggregated columns to be selected arbitrarily.

-- Wrong in PostgreSQL
SELECT name, age, COUNT(*) FROM user GROUP BY age, score;

-- Fix: either add <code>name</code> to GROUP BY or use an aggregate, e.g. <code>MIN(name)</code>

2.10 Transaction abort behavior

When an error occurs inside a transaction, PostgreSQL aborts the whole transaction and subsequent commands fail with ERROR: current transaction is aborted. The fix is to avoid using database exceptions for control flow and to check operation results manually.

2.11 Type conversion errors (major)

MySQL performs implicit type conversion, but PostgreSQL enforces strict type matching. Examples:

Comparing a smallint column with a boolean literal causes operator does not exist: smallint = boolean.

Updating a smallint column with a boolean literal causes

column "name" is of type smallint but expression is of type boolean

.

Two remediation approaches:

Manually align Java field types and PostgreSQL column types.

Create implicit conversion functions (e.g., smallint_to_boolean and boolean_to_smallint) and register them as assignment casts.

-- Create function: smallint to boolean
CREATE OR REPLACE FUNCTION "smallint_to_boolean"(i int2)
RETURNS bool AS $$
BEGIN
  RETURN (i::int2)::bool;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- Register 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 int2 AS $$
BEGIN
  RETURN (b::bool)::int2;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- Register cast
CREATE CAST (BOOLEAN AS SMALLINT) WITH FUNCTION boolean_to_smallint AS ASSIGNMENT;

Adding multiple implicit casts can lead to ambiguous operator selection errors such as "Could not choose a best candidate operator" or "operator is not unique".

3. PostgreSQL Helper Scripts

3.1 Batch convert 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 $$;

3.2 Batch set default timestamp values for create_time / update_time columns

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 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. Precautions

When migrating tables, ensure field types match; avoid unintended type changes.

Convert MySQL tinyint columns to PostgreSQL smallint, not to boolean, to prevent mismatches.

If Java fields use LocalDateTime, avoid PostgreSQL TIMESTAMPTZ columns.

MySQL automatically converts tinyint to Java Boolean. PostgreSQL does not; you can add implicit conversion functions, but they must be re‑executed after each PostgreSQL deployment, or you must adjust the Java code and database schema to align types.

Author: 李白的手机 Source: juejin.cn/post/7356108146632163339