项目从 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 => SS
to_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 = boolean

SELECT   xx fom xx    WHERE   enable = ture

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

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
-- 创建赋值转换1
create 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
  
-- 创建隐式转换2
create 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 $$;