Oracle转Postgresql

一、去O数据库选择PostgreSQL

PostgreSQL官网: https://www.postgresql.org/
PostgreSQL教程: https://www.runoob.com/postgresql/postgresql-tutorial.html
菜鸟教程 https://www.w3cschool.cn/qysrc/qysrc-jwxg3758.html
W3Cschool
通用数据库工具：dbeaver https://dbeaver.io/

二、SpringMVC+MyBatis整合PostgreSQL

在xxx-svc模块的pom.xml添加postgresql依赖

	<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
	<dependency>
	    <groupId>org.postgresql</groupId>
	    <artifactId>postgresql</artifactId>
	    <version>42.3.2</version>
	</dependency>

三、修改app.properties中jdbc配置文件

修改数据库链接配置

<!--修改数据库链接配置-->
#数据库类型
jdbc.dbType=postgresql
#数据库驱动
jdbc.driverClassName=org.postgresql.Driver
#数据库地址
jdbc.url=jdbc:postgresql://11.xxx.xx.xxxx:5432/xxxx（调整为各自的数据库服务名）
jdbc.urlo=jdbc:postgresql://11.xxx.xx.xxx:5432/xxxx
#账户信息
jdbc.username=xxxxx
jdbc.password=xxxxxxx

四、修改configuration.xml中数据库方言处理方式

<plugins>
	<plugin interceptor="com.paas.common.mybatis.OffsetLimitInterceptor">
		<property name="1" value="com.paas.common.mybatis.dialect.PostgreSQLDialect"/>
	</plugin>
</plugins>

五、PostgreSQL 时间/日期函数和操作符

https://www.runoob.com/postgresql/postgresql-datetime.html

六、PostgreSQL 常用函数

https://www.runoob.com/postgresql/postgresql-functions.html

七、PostgreSQL与Oracle基本数据类型差异

Oracle	PostgreSQL
CHAR	CHAR
NCHAR	CHAR
VARCHAR2	varchar
NVARCHAR2	varchar
number	numeric
date	timestamp/date/time
不支持boolean，可通过0/1代替	支持boolean
null	null

特别说明

number(0,0) 等同于 numeric(0,0)

八、PostgreSQL与Oracle的DDL语法差异

Oracle和PostgreSQL操作表结构语法基本一致，只有更改列的数据类型写法有些差异

**Oracle：**ALTER TABLE table_name modify column_name datatype;

PostgreSQL：ALTER TABLE table_name ALTER column_name TYPE datatype;

九、PostgreSQL与Oracle的DML语法差异

oracle和pgSQL增删改查语法基本一致，只有upsert有差异
**Oracle：**有自带的merge into功能

PostgreSQL：不支持merge操作，可以使用on conflict() do

十、PostgreSQL与Oracle的查询语句差异

1、查询表中最新n条数据

Oracle有rownum，PostgreSQL有limit
Oracle：
写法一：select t._ from (select _ from nwd.tc_inte_bid_record order by create_time desc) t where rownum <= n;
写法二：select _ from(select t._, row_number() over(order by create_time desc) rn from nwd.tc_inte_bid_record t) where rn <=n;

postgreSQL：
select * from abc.xxx order by create_time desc limit n;

注意：limit必须用于 order by 之后

2、子查询

postgresql子查询要求比较严格，必须具有别名才可以

十一、PostgreSQL与Oracle对应的函数

Oracle	PostgreSQL	备注
nvl	COALESCE	COALESCE函数是返回参数中的第一个非null的值，它要求参数中至少有一个是非null的，如果参数都是null会报错
trunc	date_trunc	截断成指定的精度
sysdate	current_date、current_time、current_timestamp
dual表	没有dual表	可以直接select 1、select user、select xxx
where rownum < …	select * from persons limit A offset B;	A就是需要多少行；B就是查询的起点位置。A、B是bigint类型的值
to_number(int)	to_number(int, text)	例: to_number(123, “666666”) : text表示精度
to_char(int)	to_char(int, text)	例如 to_char(123,“666666”) : text表示精度
to_date(text)	to_date(text, text)
cast	cast
DECODE	无	使用CASE……WHEN……ELSE ……END
instr(‘str1’,‘str2’)	strpos(‘str1’,‘str2’)
ROWNUM	无	1.限制结果集数量，用于翻页等：
SELECT * FROM T LIMIT 5 OFFSET 0
2.生成行号：	ROW_NUMBER() OVER()
xmlagg(xmlparse(content (表字段) || ‘,’ wellformed )).getclobval ()	string_agg(表字段,‘,’)（参考：https://www.cnblogs.com/lurenjia1994/p/9535899.html）	行转列：处理字段合并

十二、PostgreSQL 日期处理

12.1、extract函数

extract （field from source）
extract函数是从日期或者时间数值里面抽取子域，比如年、月、日等。source必须是timestamp、time、interval类型的值表达式。field是一个标识符或字符串，是从源数据中的抽取的域。

计算时间差天数
select extract(day FROM (age('2017-12-10'::date , '2017-12-01'::date)));
计算时间差秒数
select extract(epoch FROM (now() - (now()-interval '1 day') ));

1. century （世纪）
select extract (century from timestamp '2017-07-31 22:18:00');

2. year （年）
select extract (year from timestamp '2017-07-31 22:18:00');

3. decade （得到年份除10的值）
select extract (decade from timestamp '2017-07-31 22:18:00');

4. millennium（得到第几个千年，0-1000第一个，1001-2000第二个，2001-3000第三个）
select extract (millennium from timestamp '2017-07-31 22:18:00');

5. quarter （季度）
select extract (quarter from timestamp '2017-07-31 22:18:00');

6. month （月份）
select extract (month from timestamp '2017-07-31 22:18:00');

select extract (month from interval '2 years 11 months');

7. week （返回当前是几年的第几个周）
select extract (week from timestamp '2017-07-31 22:18:00');

8. dow （返回当前日期是周几，周日：0，周一：1，周二：2，...）
select extract (dow from timestamp '2017-07-31 22:18:00');

9. day （本月的第几天）
select extract (day from timestamp '2017-07-31 22:18:00');

10. doy （本年的第几天）
select extract (doy from timestamp '2017-07-31 22:18:00');

11. hour （小时）
select extract (hour from timestamp '2017-07-31 22:18:00');

12. min （得到时间中的分钟）
select extract (min from timestamp '2017-07-31 22:18:00');

13. sec （返回时间中的秒）
select extract (sec from timestamp '2017-07-31 22:18:00');


新纪元时间 Epoch 是以 1970-01-01 00:00:00 UTC 为标准的时间，将目标时间与 1970-01-01 00:00:00
时间的差值以秒来计算 ，单位是秒，可以是负值; 有些应用会将时间存储成epoch 时间形式，以提高读取效率，
下面演示下 pg 中 epoch 时间的使用换算方法。

1. 将 time stamp 时间转换成 epoch 时间
select extract(epoch from timestamp without time zone '1970-01-01 01:00:00');

select extract(epoch from timestamp without time zone '1970-01-01 02:00:00');

select extract(epoch from interval '+1 hours');

select extract(epoch from interval '-1 hours');

2. 将epoch 时间转换成 time stamp 时间
select timestamp without time zone 'epoch' + 3600 * interval '1 second';

select timestamp without time zone 'epoch' + 7200 * interval '1 second';

12.2、age()函数

https://www.sjkjc.com/postgresql-ref/age/

12.3、计算两个日期时间差

https://blog.csdn.net/linph174/article/details/106902479

12.4、整数加日期

案例
max(c.sync_date)/ (1000 * 60 * 60 * 24) + TO_DATE(‘1970-01-01 08:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) account_date

解决
转换时间戳，再转换成日期
to_timestamp((c.SYNC_DATE/ (1000)) + EXTRACT(epoch FROM CAST(TO_DATE(‘1970-01-01’, ‘YYYY-MM-DD’) AS TIMESTAMP)))

十三、数据类型格式化函数 如：to_number(‘12454.8’,‘999999G999999D999S’)

https://www.cnblogs.com/w-j-q/p/13922315.html

十三、PostgreSQL使用注意事项

1、SUM函数

sum函数不能统计String、Char类型的数字或者金额，需要使用cast转换格式，建议转换成decimal，例如：

select sum(cast(mrri.amount as decimal)) from mm_po_item mrri；
或者
select sum(mrri.amount::decimal) from mm_po_item mrri；

2、子查询

PostGreSQL的子查询相比较Oracle而言更严格，必须使用别名

3、分页

PostGreSQL数据分页是利用limit关键字 的，搭配子查询，Limit放在order by后面