Oracle转Postgresql_oracle数据库如何转成postgres

一、去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>
1
2
3
4
5
6

三、修改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
1
2
3
4
5
6
7
8
9
10
11

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

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

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

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

六、PostgreSQL 常用函数

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

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

特别说明

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对应的函数

十二、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';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

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；
1
2
3
4
5

2、子查询

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

3、分页

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