【国产化信创实践】PostgreSQL数据库替换涉及内容及常见问题

1.修改pom文件，添加PostgreSQL数据库依赖

 

<dependency>

    <groupId>org.postgresql</groupId>

    <artifactId>postgresql</artifactId>

    <version>42.2.19</version>

</dependency>

2.修改yml文件，添加PostgreSQL数据库连接

 

spring:

  datasource:

    driver-class-name: org.postgresql.Driver

    url: jdbc:postgresql://IP地址:5432/postgres_test

    username: 账号

    password: 密码

注意

1.用navicat连接postgres时，建议使用navicat 15，navicat 12将无法看见数据表

2.PostgreSQL对表名、字段名区分大小写；但是sql语句不区分大小写，大写会自动转小；如果要查询含有大写字母的表或者列时，使用双引号""：select "ID" from dic_user;

3.常用方法、函数较MySQL不同

• group_count()

pg：方法一：string_agg()

 

例：SELECT string_agg(name,';') from sql_user_test GROUP BY age;

方法二：array_to_string(ARRAY_AGG() ,':')

例：select array_to_string(ARRAY_AGG(NAME) ,':') from sql_user_test GROUP BY age;

方法三：自定义group_count()函数，不推荐使用，效率没有string_agg()高

CREATE AGGREGATE GROUP_CONCAT(anyelement) (     sfunc=array_append,     stype=anyarray,     initcond='{}' );

• 正则匹配：REGEXP

pg：在pg使用正则表达式的时候需要使用关键字 “~”，若匹配规则不区分大小写,可以使用 “~*”，不匹配这个表达式则使用 “!~”，若表达式包含转义符，则需要在表达式前面添加关键字 ”E“。

例：SELECT * from sql_user_test where name !~ '^l';     SELECT * from sql_user_test where code ~* '^L';

• 起始日期加上一个时间段：DATE_ADD(d,INTERVAL expr unit)

pg：±

例：SELECT birthday,birthday + INTERVAL '1 YEAR' as newbirthday from sql_user_test order by id ;     SELECT birthday,birthday - INTERVAL '1 year 1 day' as newbirthday from sql_user_test order by id ;

• 计算d1，d2之间相隔的天数 DATEDIFF(d1,d2)

pg：- 或者age()，推荐使用 '-'，age()将会返回INTERVAL类型数据，使用‘-’时，如果含有非date类型字段，返回值也将为interval类型

例：SELECT now()::date-birthday from sql_user_test order by id ;      SELECT age(regtime, birthday) from sql_user_test order by id; -- 419 years 11 mons 24 days

• 日期转字符串DATE_FORMAT()

pg：to_char()

例：SELECT to_char( now(),'yyyy-mm-dd');  -- 2021-07-21

• 字符串转日期 STR_TO_DATE()

pg：TO_DATE()

 展开原码

• ifnull（）

pg：coalesce()

 折叠原码

例：SELECT coalesce(null,4); --4

• if(expr,v1,v2)

pg：不支持if，可以使用case when替代

• uuid()

pg：默认不支持uuid()函数，解决办法如下

解决办法一：自定义uuid()函数 create or replace function uuid() returns text as $body$     select array_to_string(array(SELECT substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopgrstuvwxyz' from (ceil (random()*62))::int for 1) from generate_series(1, 32)),''); $body$ language sql volatile; 解决办法二：     linux环境下：         从安装目录下share/contrib下找uuid-ossp.sql导入即可。     windows环境下：        1.开始菜单——PostgreSQL——SQL Shell,打开命令行客户端；        2.根据提示依次输入数据库服务器地址、数据库名称、端口号、用户名、密码；        3.执行命令 create extension "uuid-ossp"，安装uuid_generate_v4()扩展函数；        4.执行命令 select uuid_generate_v4(),如果返回uuid则成功

• format()

pg：round()

例：SELECT round(10/3::numeric,2); -- 3.33

• 除法 /

pg：/ :如果是int类型的相除会取整，则需要转换

例：SELECT round(10/3::numeric,2); -- 3.33

4.项目中mysql替换pg写法

1. CONVERT ( CONCAT( YEAR (xxx ), '' ), SIGNED ) 或者 CONVERT ( DATE_FORMAT( a.cysj , '%Y%m' ), SIGNED ) 替换：to_char(xxx,'yyyy')::int4  或者 to_number(to_char(birthday,'yyyyMM'),'999999') 2. format( ifnull( aa.zySsChoiceRate, 0 )/ zz.zySsChoiceRate * 100, 2 ) 替换：round(coalesce(aa.zySsChoiceRate,0)/zz.zySsChoiceRate*100,2)   注意：如果/前后都为int类型,/后也会为int型，需要转换类型        round(coalesce(aa.zySsChoiceRate,0)/zz.zySsChoiceRate::decimal*100,2) 3.YEAR(z.cysj) 替换：to_char(z.cysj,'yyyy') 4.now() 替换：now()::timestamp(0)     pgsql：now()默认是时间戳 5.DATE_FORMAT( DATE_SUB( now(), INTERVAL 1 DAY ), '%Y%m%d' ) 替换：to_char( now()+ INTERVAL '1 DAY ', 'yyyyMMdd' )

5.对于MySQL数据库自增id，如果pg数据库表已经创建如何修改id为自增

1. 第一步需要创建一个序列：一般序列命名由表名+主键字段+seq组成，如下表名为monitor，主键字段为id：
   
   创建序列语句如下：

   create SEQUENCE monitor_id_seq
   START WITH 1
   INCREMENT BY 1
   NO MINVALUE
   NO MAXVALUE
   CACHE 1;

2. 设置表字段自增sql
   ALTER table monitor alter column id set default nextval('monitor_id_seq'::regclass);

5.PG数据库替换mysql常见问题及对策

序号	问题	原因	解决方案	备注
1	对于MySQL数据库自增id，如果pg数据库表已经创建如何修改id为自增	已创建好的pg表无法直接通过表设计来修改主键为自增字段	第一步需要创建一个序列：一般序列命名由表名+主键字段+seq组成，如下表名为monitor，主键字段为id： 创建序列语句如下： create SEQUENCE monitor_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; 设置表字段自增sql ALTER table monitor alter column id set default nextval('monitor_id_seq'::regclass);
2	如果pg数据库表id为自增且已有部分从1开始的数据，新增数据出现主键冲突问题	新增数据id会从1开始递增，但已有id为1的数据，新增则报错	把当前最大id当做id的自增起始数，使用如下sql： select setval('your_table_id_seq',(select max(id) from 表名)) 替换your_table和表名为自己需要更改的表