How to Seamlessly Switch a SpringBoot MyBatisPlus Project from MySQL to PostgreSQL

Migration Steps

Add the PostgreSQL JDBC driver to the Maven/Gradle dependencies:

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

Update the Spring Boot datasource configuration to use the PostgreSQL driver and URL. The currentSchema parameter replaces the MySQL database name; if omitted PostgreSQL uses the default public schema.

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://<em>database_host</em>/<em>database_name</em>?currentSchema=<em>schema_name</em>&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT+8&useSSL=false

Common Pitfalls When Switching from MySQL to PostgreSQL

TIMESTAMPTZ vs. LocalDateTime Mapping a TIMESTAMPTZ column to a Java LocalDateTime throws PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type java.time.LocalDateTime . Use timestamp columns in PostgreSQL or map to java.util.Date instead.

String literals PostgreSQL requires single quotes for string literals. Convert WHERE name = "jay" to WHERE name = 'jay' .

Identifier quoting Do not wrap identifiers with backticks. Change WHERE `name` = 'jay' to WHERE name = 'jay' .

JSON field extraction MySQL uses -> '$.name' ; PostgreSQL uses ->> 'name' .

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

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

CAST vs. CONVERT Replace MySQL convert(...) with PostgreSQL CAST(... AS ...) .

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

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

FORCE INDEX PostgreSQL does not support FORCE INDEX . Remove the clause.

IFNULL Replace IFNULL with COALESCE .

-- MySQL
IFNULL(col, 0)

-- PostgreSQL
COALESCE(col, 0)

DATE_FORMAT PostgreSQL uses to_char with different format symbols.

// %Y => YYYY
// %m => MM
// %d => DD
// %H => HH24
// %i => MI
// %s => SS

to_char(time, 'YYYY-MM-DD')   -- equivalent to DATE_FORMAT(time, '%Y-%m-%d')
to_char(time, 'YYYY-MM')      -- equivalent to DATE_FORMAT(time, '%Y-%m')
to_char(time, 'YYYYMMDDHH24MISS') -- equivalent to DATE_FORMAT(time, '%Y%m%d%H%i%s')

GROUP BY rules PostgreSQL requires every selected column to appear in the GROUP BY clause or be aggregated. MySQL permits non‑aggregated columns.

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

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

Transaction abort handling If any statement in a transaction fails, PostgreSQL aborts the whole transaction and ignores subsequent statements until a ROLLBACK . MySQL does not exhibit this behavior.

-- Example error
ERROR: current transaction is aborted, commands ignored until end of transaction block

Boolean ↔ SMALLINT conversion PostgreSQL does not implicitly cast between boolean and smallint . Define explicit cast functions if you need seamless conversion.

CREATE OR REPLACE FUNCTION smallint_to_boolean(i int2)
  RETURNS boolean AS $$
BEGIN
  RETURN (i::int2)::integer::bool;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE CAST (SMALLINT AS BOOLEAN) WITH FUNCTION smallint_to_boolean AS ASSIGNMENT;

CREATE OR REPLACE FUNCTION boolean_to_smallint(b boolean)
  RETURNS int2 AS $$
BEGIN
  RETURN (b::boolean)::bool::int;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE CAST (BOOLEAN AS SMALLINT) WITH FUNCTION boolean_to_smallint AS IMPLICIT;

To remove the casts later:

DROP FUNCTION smallint_to_boolean;
DROP CAST (SMALLINT AS BOOLEAN);

PostgreSQL Helper Scripts

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

Batch set default value 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 = '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 $$;

Precautions

When migrating tables, ensure that column types in PostgreSQL match the original MySQL types; avoid unintended automatic type changes.

Convert tinyint to smallint (not boolean) to prevent mismatches with Java Boolean fields.

Do not use TIMESTAMPTZ for Java LocalDateTime; prefer plain timestamp columns.

MySQL’s permissive type conversion (e.g., tinyint ↔ boolean) does not exist in PostgreSQL. Either add explicit cast functions as shown above or adjust the Java entity definitions and database schema accordingly.