Migrating SpringBoot MyBatisPlus from MySQL to PostgreSQL: Common Pitfalls & Fixes

Introduction

The original project is built with SpringBoot, MyBatis‑Plus and MySQL.

Migration steps

1. Add the PostgreSQL driver

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

2. Change the JDBC configuration

spring:
  datasource:
    # driver class for PostgreSQL
    driver-class-name: org.postgresql.Driver
    # connection URL – replace the placeholders with your own values
    url: jdbc:postgresql://${db_address}/${db_name}?currentSchema=${schema_name}&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

PostgreSQL introduces the concept of a schema . A single database can contain many schemas; the default schema is public. After updating the driver and URL the basic switch is done, but many SQL‑syntax differences still need to be addressed.

Common pitfalls and their fixes

1. TIMESTAMPTZ vs. LocalDateTime

PostgreSQL cannot map a TIMESTAMPTZ column to a Java java.time.LocalDateTime field. Use either a timestamp column in PostgreSQL or map the Java field to java.util.Date.

2. String literals

PostgreSQL uses single quotes for string literals. Replace double‑quoted literals such as WHERE name = "jay" with WHERE name = 'jay'.

3. Identifier quoting

Backticks are MySQL‑specific. Write identifiers without backticks, e.g. WHERE name = 'jay' instead of WHERE `name` = 'jay'.

4. JSON field extraction

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

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

MySQL uses -> '$.property' while PostgreSQL uses ->> 'property' to retrieve a JSON sub‑property.

5. convert() function

PostgreSQL does not provide convert(). Replace it with CAST:

-- MySQL
SELECT convert(name, DECIMAL(20,2))

-- PostgreSQL
SELECT CAST(name AS DECIMAL(20,2))

6. Force index

The MySQL hint FORCE INDEX(idx_audit_time) is not supported in PostgreSQL. Remove the clause.

-- MySQL
SELECT * FROM user FORCE INDEX(idx_audit_time)

7. ifnull() function

Replace MySQL's ifnull() with PostgreSQL's standard COALESCE().

SELECT COALESCE(column, 0) FROM table

8. Date formatting

MySQL's date_format() must be rewritten with to_char(). Map format tokens as follows:

%Y → YYYY

%m → MM

%d → DD

%H → HH24

%i → MI

%s → SS

-- MySQL
DATE_FORMAT(time, '%Y-%m-%d')

-- PostgreSQL
to_char(time, 'YYYY-MM-DD')

9. GROUP BY requirements

PostgreSQL enforces that every selected column appears in the GROUP BY clause or is wrapped in an aggregate function.

-- Incorrect (MySQL permits this)
SELECT name, age, COUNT(*) FROM user GROUP BY age, score;

-- Correct options
SELECT name, age, COUNT(*) FROM user GROUP BY name, age, score;
-- or
SELECT MIN(name) AS name, age, COUNT(*) FROM user GROUP BY age, score;

10. Transaction abort handling

If any statement inside a transaction fails, PostgreSQL aborts the whole transaction and subsequent statements return the error "current transaction is aborted". Do not use exception flow to control business logic; instead validate conditions before executing statements.

11. Strict type matching

PostgreSQL does not perform implicit type conversion. Examples:

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

Updating a smallint column with a boolean value raises a type‑mismatch error.

Two approaches to resolve the issue:

Align Java field types with PostgreSQL column types (e.g., map tinyint → smallint, not boolean).

Create explicit conversion functions and casts if you need MySQL‑style implicit conversion.

Example of implicit conversion functions (use with caution – adding many implicit casts can lead to operator‑ambiguity errors):

-- smallint → boolean
CREATE OR REPLACE FUNCTION smallint_to_boolean(i int2)
RETURNS bool AS $$
BEGIN
  RETURN (i)::bool;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE CAST (SMALLINT AS BOOLEAN) WITH FUNCTION smallint_to_boolean AS ASSIGNMENT;

-- boolean → smallint
CREATE OR REPLACE FUNCTION boolean_to_smallint(b bool)
RETURNS int2 AS $$
BEGIN
  RETURN (b)::int2;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE CAST (BOOLEAN AS SMALLINT) WITH FUNCTION boolean_to_smallint AS IMPLICIT;

Helper scripts for PostgreSQL

1. Batch conversion of timestamptz to timestamp

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

2. Batch setting of default timestamps

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

Important notes

Ensure a one‑to‑one mapping of column types when migrating. For example, tinyint in MySQL should become smallint in PostgreSQL, not boolean.

Avoid using TIMESTAMPTZ for Java fields of type LocalDateTime; use timestamp instead.

If you rely on MySQL's automatic tinyint ↔ boolean conversion, either add explicit conversion functions (as shown above) or modify the Java model and database schema to match the strict PostgreSQL types.

Source: juejin.cn/post/7356108146632163339