一、介绍
此次更新时间：2020-10-28，现在是上班时间，偷更一下。其实使用IDEA的话无需配置Maven什么的，如果你们公司不是强制要求使用Eclipse的话，只需要有个JDK的环境即可，IDEA自带了一个版本的Maven，还是挺新的，目前IDEA最新版2.2.3的版本。我们也不用按照下面这个步骤去下载Spring Initializr，我们在IDEA中新建项目选择到Maven就行了，干净简洁。
目前在Resources目录下的application大多数是使用yml语法了。现在已经太长时间没有接触过 postgresql了，长期使用MySQL，只要数据量在几百万，加加索引，优化SQL工作量还是不大，而且外面的公司还是比较愿意使用MySQL的，我面试别人的时候，也不会问什么乱七八糟的数据库，考虑到公司的业务和生成报表相关，对于SQL优化非常重视，所以数据库只问MySQL。
这篇文章是初次接触 PostgreSQL 时所写，从MyBatis 1.3.2 的版本大家也能看的出来，本来写了个简单的CRUD的DEMO，结果时间太久找不到了。
1、情况说明
在这里我使用SpringBoot配置JPA连接到PostgreSql数据库的。源码也会提供给大家（此文末尾），效果如下
数据库：
运行效果：
2、安装软件及依赖包
完整搭建SpringBoot及依赖包：https://blog.csdn.net/qq_41647999/article/details/83214100
需要的SpringBoot代码从Spring官网下载：https://start.spring.io/
Mybaits官网：http://www.mybatis.org/mybatis-3/
最关键的地方是在依赖包那里，需要引用Mybaits和PostgreSql的包。
什么是JPA 和 Mybaits?
两者都是基于JDBC做了连接持久化的两个开源框架，jdbc不陌生吧，每次连接都要创建实例，执行完sql之后还要关闭连接实例，做了持久化之后，就会大大降低IO的开销。JPA不用自己写SQL，mybatis按理来说是需要自己写SQL的（使用通用Mapper或者Mybatis plus 也能不写SQL），各有各的好处。
将下载的项目解压出来。
二、配置
如果您的环境配置和我搭建SpringBoot的博文一样的话，用eclipse打开项目。
连接数据库
resources
1
2
3
4
5
6
7
8
9
|
//数据库的地址以及端口号
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
//账号（默认为postgres）
spring.datasource.username=postgres
//密码
spring.datasource.password=123456ok
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.hbm2ddl.auto=update
|
yml
1
2
3
4
5
6
7
8
9
10
11
12
|
spring:
datasource:
# primary:
url: jdbc:postgresql://localhost:5432/postgres
# url: jdbc:mysql://localhost:3306/springboot?useSSL=false
# jdbc-url: jdbc:mysql://localhost:3306/springboot?useSSL=false
username: postgres
password: ********
# username: root
# password: ********
driver-class-name: org.postgresql.Driver
# driver-class-name: com.mysql.cj.jdbc.Driver
|
其他情况
当然如果您的项目是按照上面的链接搭建的，也可以通过在pom.xml中添加代码，如图。
这里需要注意的一点是，要清楚您使用的Mybaits的依赖包是否符合！
1
2
3
4
5
|
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
|
重置自增序列
分为从43开始、从43开始、从42开始
1
2
3
4
|
SELECT setval('myseq', 42); #Next nextval will return 43
SELECT setval('myseq', 42, true); #Same as above
SELECT setval('myseq', 42, false); #Next nextval will return 42
alter sequence dataset_id_seq start with 1 ; #只能增大 不能减小
|
schemas问题
PGSql默认的是public,如果用MybatisPlus的内置方法的话,是需要指定连接的CurrentSchema的,否则只会默认查询public,自己写Sql可以在前面加上schemas
但是使用内置方法没有,必须在连接url指定schemsa,否则会报ERROR: relation “item” does not exist表不存在
postgresql—-IN&&EXISTS
一.IN && NOT IN
WHERE expression IN (subquery)
右边圆括号内是返回一个字段的子查询结果集，左边的表达式（或字段）对查询结果每一行进行一次运算和比较，如果结果集中存在相等的行，则IN结果为’TRUE'，否则为’FALSE';
WHERE expression NOT IN (subquery)
NOT IN与IN正相反，如果结果集中不存在相等的行结果为’TRUE'，否则为’FALSE'。
测试表：
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
|
test=# \d tbl_test
Table "public.tbl_test"
Column | Type | Modifiers
--------+---------+-----------
f | integer |
test=# \d tbl_insert
Table "public.tbl_insert"
Column | Type | Modifiers
--------+-----------------------+-----------
a | integer |
b | integer |
c | character varying(12) |
test=# select * from tbl_test ;
f
---
1
3
5
(3 rows)
test=# select * from tbl_insert;
a | b | c
---+---+-------
1 | 1 | 11
2 | 2 | 22
3 | 3 | 33
4 | 4 | 44
5 | 5 | 51
6 | 6 | 1
6 | 6 | 61
6 | 6 | 661
7 | 7 | 3%1
8 | 8 | 3%_1
8 | 8 | 3_%_1
7 | 7 | abc
7 | 7 | ABc
7 | 7 | aBC
(14 rows)
|
示例1.查询tbl_insert表,且a字段值在tbl_test表字段f中的行
1
2
3
4
5
6
7
|
test=# select * from tbl_insert where a in (select f from tbl_test);
a | b | c
---+---+----
1 | 1 | 11
3 | 3 | 33
5 | 5 | 51
(3 rows)
|
示例2.查询tbl_insert表,且a字段值比tbl_test表字段f小1的行
1
2
3
4
5
6
|
test=# select * from tbl_insert where a+1 in (select f from tbl_test);
a | b | c
---+---+----
2 | 2 | 22
4 | 4 | 44
(2 rows)
|
示例3.查询tbl_insert表,且a字段值不在tbl_test表字段f中的行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
test=# select * from tbl_insert where a not in (select f from tbl_test);
a | b | c
---+---+-------
2 | 2 | 22
4 | 4 | 44
6 | 6 | 1
6 | 6 | 61
6 | 6 | 661
7 | 7 | 3%1
8 | 8 | 3%_1
8 | 8 | 3_%_1
7 | 7 | abc
7 | 7 | ABc
7 | 7 | aBC
(11 rows)
|
示例4.查询tbl_insert表,且a字段值等于5或7的行
1
2
3
4
5
6
7
8
9
|
test=# select * from tbl_insert where a in (5,7);
a | b | c
---+---+-----
5 | 5 | 51
7 | 7 | 3%1
7 | 7 | abc
7 | 7 | ABc
7 | 7 | aBC
(5 rows)
|
二.XISTS && NOT EXISTS
WHERE EXISTS (subquery)
括号内同样是一个子查询，如果子查询有返回结果，则EXISTS结果为’TRUE'，否则为’FALSE'。
WHERE NOT EXISTS(subquery)
NOT EXISTS与EXISTS正好相反，如果子查询没有返回结果，为’TRUE'，否则’FALSE'。
示例1.查询tbl_insert表,且a字段值在tbl_test表字段f中的行
1
2
3
4
5
6
7
|
test=# select * from tbl_insert where exists (select null from tbl_test where tbl_test.f=tbl_insert.a);
a | b | c
---+---+----
1 | 1 | 11
3 | 3 | 33
5 | 5 | 51
(3 rows)
|
示例2.查询tbl_insert表,且a字段值不在tbl_test表字段f中的行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
test=# select * from tbl_insert where not exists (select null from tbl_test where tbl_test.f=tbl_insert.a);
a | b | c
---+---+-------
2 | 2 | 22
4 | 4 | 44
6 | 6 | 1
6 | 6 | 61
6 | 6 | 661
7 | 7 | 3%1
8 | 8 | 3%_1
8 | 8 | 3_%_1
7 | 7 | abc
7 | 7 | ABc
7 | 7 | aBC
(11 rows)
|
PS:NOT IN的效率非常低，如果可以的话建议使用NOT EXISTS。
PostgreSql 查看退出 Replication Slot
查看Replication Slot：
1
|
SELECT * FROM pg_replication_slots;
|
删除一个Replication Slot：
1
|
select pg_drop_replication_slot('debezium');
|
PostgreSql 查询所有表名
1
2
3
4
|
SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE 'pg%'
AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;
|
或
1
|
select tablename from pg_tables where schemaname='public'
|
PostgreSql数据库表实现ID自增
postgresql数据库可以创建主键，但是没有像mysql那样直接指定主键自增的auto_increment关键字，因此如果在postgresql中创建表指定主键自增使用auto_increment会报错。那么如何实现主键自增，postgresql通过序列来实现。序列可以实现自动增长，而且表字段可以指定默认值，因此可以通过指定默认值的方式来实现主键自增。
postgresql提供了三种serial类型：smallserial,serial,bigserial，他不是真正的类型，而是在创建唯一标识符列的标志以方便使用。bigserial会创建一个bigint类型的自增，serial用以创建一个int类型的自增，依次类推。
下面通过实例演示自增ID的实现：
通过建表语句，可以看到，因为使用了serial类型，所以会创建一个关联的sequece，名为aaa_id_seq。
接着查看表结构，并向表中插入数据：
表aaa实现了id自增功能，表的id默认值是nextval(‘aaa_id_seq’::regclass)。
按照这个思路，我们可以这么来干，一般的主键表，没有使用serial类型，那么我们可以通过创建序列，并修改表ID默认值字段，指定默认值字段为序列的nextval。
我们创建一个普通的int类型ID，并设置为主键。建表不会出现创建序列的notice内容。
这个表的插入，如果不指定id,那么就会出现错误：
接着我们创建一个序列：
1
|
create sequence bbb_id_seq start with 1 increment by 1 no minvalue no maxvalue cache 1;
|
设置表bbb的ID字段的默认值为nextval(bbb_id_seq)
1
|
alter table bbb alter column id set default nextval('bbb_id_seq');
|
再次向bbb中插入记录，这次不用关心ID字段了。
通过查看表结构，看看两种方式产生的表：
postgresql查找索引的方法也是和mysql不一样，postgresql查看索引的语法是：
1
|
select * from pg_indexes where tablename='bbb';
|
或者
1
|
select * from pg_statio_all_indexes where relname='bbb';
|
可以看到，postgresql默认索引也是通过btree来创建的。
最后再补充一点内容，查看序列和查看表结构是一样的，都是使用\d来查看，比如\d aaa_id_seq。
pg_catalog.“default"作用
字段使用lc_collate作为排序标准
查看default collate
创建表时修改collate
1
2
3
4
5
|
CREATE TABLE collate_test
(
default_collate text, --Default collation
custom_collate text COLLATE pg_catalog."C" --Custom collation
);
|
文档
The collation feature allows specifying the sort order and character classification (…)
