PostgreSQL

由于某些原因，将Oracle换成了PostgreSQL，sql也要修改为pg的语法，虽然pg有很多函数名和oracle的一样，但是用起来就是两个不同的东西，这将导致sql查出来的内容乱七八糟或是报错。

时区

pg里的timestamp是带时区的，众所周知，中国在+8区，这个+8小时是基于utc时间来计算的，虽然你建表的时候把字段类型设置成了timestamp with time zone，但实际上，那个字段只是会把你插入的时间当成utc的存储罢了
当咩咩在使用timezone='8'并且timestamp with time zone作为时间字段的时候遇到了一个查询上的问题，使用时间计算，算出2022-06-07 19:15:00+08作为查询条件，可以从数据库中查到显示为2022-06-07 11:15:00.000000 +00:00的这条数据，但是如果使用2022-06-07 19:15:00作为查询条件，那肯定是查不到的，因为是通过timestamp计算的，直接导致了在使用on conflict的时候数据错乱，所以应该使用类型timestamp

-- 设置为utc时间也就是+00:00 如果库里全是 timestamp with time zone 类型要用这个
set timezone='0';
-- 设置为中国时间也就是+08:00
set timezone='8';

-- 查询当前时间 代替oracle的sysdate，输出的最后可以看到当前时间
select now();
-- current_date 在oracle中为utc时间，在pg中是当前日期，如果pg设置的utc时间可以用now()，否则减8小时
select now() - interval '8' hour

trunc()

这在oracle里用来截取时间，比如把精确秒的时间截到日，他在pg里使用date_trunc代替

-- 当前时间截取到天 对应 TRUNC(SYSDATE)
date_trunc('day', now())
-- 当前时间截取到分钟 对应 TRUNC(SYSDATE, 'MI')
date_trunc('minute', now())

interval

这是用来时间加减计算的，oracle里时间与数字计算单位默认是天，pg里要写清楚

-- 加2天 对应 SYSDATE + 2
now() + interval '2' day
-- 加num天 对应 SYSDATE + num
now() + interval '1' day * num
-- 加1小时 对应 SYSDATE + 1/24
now() + interval '1' hour
-- 这样写也行 加180天
now() + INTERVAL '180 days'

mod()

用来计算除法的时候的余数的，在oracle里除以0就会返回左边的数字，而pg里直接炸给你看，如果被除数是0都会返回0

-- 解决方案就是写个case在除数b为0的时候返回被除数a
case when b=0 then a else MOD(a, b) end

类型转换

pg不像oracle和mysql那样有着比较随便的数据类型转换，字符串或是时间和数字是不能直接进行运算的，那就会用到字段或值::类型的方式进行类型转换以保证sql正常运行

-- 取一个时间字段c中的分钟数字 类型名称都比较接近自然语言能猜到是什么
extract(minute from cast(c as timestamp))::numeric

to_date()

这是用于字符串与时间转换的，当你把oracle的sql放到pg里的时候，你会发现你的时间查询坏掉了，因为在pg中to_date()转出来的只能精确到日，不能像oracle那样精确到秒

-- 错误示范 转出来是 2022-05-31 00:00:00
to_date('2022-05-31 09:43:29', 'yyyy-mm-dd hh24:mi:ss')
-- 正确的方法
to_timestamp('2022-05-31 09:43:29', 'yyyy-mm-dd hh24:mi:ss')

merge into

pg不支持这个，但是他有个代替品

-- 在 conflict 中的字段需要给他创建一个key才能使用这个功能，这样这些字段在全表中的组合必须是唯一的
alter table table1
add constraint table1_conflict_abc
unique (a,b,c);
-- 大量数据的时候
insert into table1 (a,b,c) values
(1,2,3),
(4,5,6)
on conflict(a,b) do update set
c=excluded.c
-- 从其他表插入
insert into table1 (a,b,c)
select a,b,c from table2
on conflict(a,b) do update set
c=excluded.c

时区