从MySQL切换PostgreSQL后，后悔莫及……

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

spring:  datasource:    # 修改驱动类    driver-class-name: org.postgresql.Driver    # 修改连接地址    url: jdbc:postgresql://数据库地址/数据库名?currentSchema=模式名&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

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

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

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

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

-- MySQL语法: select convert(name, DECIMAL(20, 2))-- postgreSQL语法:select CAST(name as DECIMAL(20, 2))

-- MySQL语法select xx FROM user force index(idx_audit_time)

cause: org.postgresql.util.PSQLException: ERROR: function ifnull(numeric, numeric) does not exist

Cause: org.postgresql.util.PSQLException: ERROR: function date_format(timestamp without time zone, unknown) does not exist

// %Y => YYYY // %m  =>   MM// %d   =>  DD// %H => HH24// %i => MI// %s => SSto_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')

Cause: org.postgresql.util.PSQLException: ERROR: column  "r.name" must appear in the GROUP BY clause or be used in an  aggregate function

select name, age, count(*)from user group by age, score

# Cause: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block; uncategorized SQLException; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = boolea

SELECT   xx fom xx    WHERE   enable = ture

Cause: org.postgresql.util.PSQLException: ERROR: column "name" is of type smallint but expression is of type boolea

update from xx set name = false  where  name = true

-- 创建函数1  smallint到boolean到转换函数CREATE OR REPLACE FUNCTION "smallint_to_boolean"("i" int2)  RETURNS "pg_catalog"."bool" AS $BODY$ BEGIN  RETURN (i::int2)::integer::bool; END; $BODY$LANGUAGE plpgsql VOLATILE-- 创建赋值转换1create cast (SMALLINT as BOOLEAN) with function smallint_to_boolean as ASSIGNMENT;-- 创建函数2    boolean到smallint到转换函数CREATE OR REPLACE FUNCTION "boolean_to_smallint"("b" bool)  RETURNS "pg_catalog"."int2" AS $BODY$ BEGIN  RETURN (b::boolean)::bool::int; END; $BODY$LANGUAGE plpgsql VOLATILE-- 创建隐式转换2create cast (BOOLEAN as SMALLINT) with function boolean_to_smallint as implicit;

-- 删除函数drop function smallint_to_boolean-- 删除转换drop  CAST (SMALLINT as BOOLEAN)

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

-- 注意 || 号拼接的后面的字符串前面要有一个空格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 $$;