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和表名为自己需要更改的表