Essential Guide to Migrating SpringBoot MySQL Projects to PostgreSQL: Pitfalls & Scripts

Switching Process

Add PostgreSQL driver

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

Modify JDBC connection information

spring:
  datasource:
    # change driver class
    driver-class-name: org.postgresql.Driver
    # change connection URL
    url: jdbc:postgresql://<host>/<database>?currentSchema=<schema>&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

PostgreSQL uses schemas ; a single database can contain multiple schemas. The schema name replaces the old MySQL database name, and if omitted the default is public.

After updating the driver and URL the basic switch is complete, but many SQL syntax differences still need to be addressed.

Pitfalls

TIMESTAMPTZ type vs. LocalDateTime

If a PostgreSQL column is defined as TIMESTAMPTZ but the Java field is LocalDateTime, conversion fails. Use timestamp in PostgreSQL or change the Java field to Date.

Parameter values must use single quotes

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

Do not wrap column names with backticks

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

JSON field extraction syntax differs

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

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

convert function does not exist

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

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

force index syntax does not exist

PostgreSQL does not support force index; simply remove it from the query.

ifnull function does not exist

Replace ifnull with COALESCE in PostgreSQL.

date_format function does not exist

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

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

GROUP BY requirements

PostgreSQL requires every selected column to appear in the GROUP BY clause or be wrapped in an aggregate function, unlike MySQL which may return arbitrary values.

Transaction abort behavior

If any statement in a transaction fails, PostgreSQL aborts the whole transaction and ignores subsequent commands until the transaction is ended. Avoid using database exceptions to drive business logic.

Type conversion errors (smallint ↔ boolean)

PostgreSQL enforces strict type matching. To emulate MySQL's automatic conversion you can either adjust the Java and column types to match, or create implicit cast functions:

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

Remove these functions if they cause ambiguous operator resolution errors.

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 = '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 timestamps

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

Important Notes

When migrating, ensure MySQL column types map correctly to PostgreSQL types; avoid using * wildcard in SELECT without explicit column mapping.

Convert tinyint to smallint, not to boolean, to prevent mismatches.

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

MySQL’s automatic tinyint ↔ boolean conversion is not supported by PostgreSQL; you can add implicit cast functions, but they must be re‑executed after each PostgreSQL deployment.

If you cannot modify the source code of a framework that directly accesses the database, you may need to adjust the database schema to match the expected types.