PostgreSQL，同样作为关系型数据库的一员。由于其优秀的性能，现在已经越来越广泛地被使用。这里简单介绍下在使用PostgreSQL过程中需要注意的Tips

abstract.png

选择数据库

和MySQL一样，PostgreSQL在本地安装后也提供了一个命令行客户端——psql。通过psql不仅可以执行SQL命令，还可以执行PostgreSQL的元命令(以反斜杠开头的命令)。比如在PG下选择数据库时，其只支持元命令(c database)并没有相应的SQL命令。不像MySQL可以通过SQL命令实现(use database)切库。与此同时，在开发过程中更多的会选择GUI客户端来操作数据库。这里我用的即是Jetbrains的DataGrip。而在DataGrip下无法通过执行PG的元命令选择数据库

事实上，可以通过Console File右上角的下拉框来选择我们的所需的database、schema

figure 1.jpeg

选择Schema

在SpringBoot中，若JDBC URL中未指定Schema则默认使用数据库的public Schema。故如果期望连接、使用自定义的Schema可在JDBC URL中使用「currentSchema」参数实现。示例如下所示

# 设置数据源为 db1数据库的sh1 schema
spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/db1?currentSchema=sh1

左模糊查询: char、varchar类型

WHERE语句中可以使用LIKE进行模糊查询，这里需要特别注意char、varchar类型的左模糊查询问题。先建立一张表并插入一些数据

-- 建表
create table student_info (
    id int primary key not null ,
    name char(100) null,
    remark varchar(100) null
);

-- 插入数据
insert into
    student_info (id, name, remark)
values
    (1,'Aaron','Good'),
    (2,'An','Bad'),
    (3,'Tom','Ok'),
    (4,'Tony','Ok');

表中数据如下所示

figure 2.jpeg

现在分别对name、remark字段进行左模糊查询

-- 对remark字段进行左模糊查询，查询remark字段以d结尾的数据
select * from student_info where remark like '%d';

结果符合预期

figure 3.jpeg

-- 对name字段进行左模糊查询，查询name字段以n结尾的数据
select * from student_info where name like '%n';

查询结果却为空，这显然不符合我们的预期

figure 4.jpeg

其原因就在name字段为char(100)类型。即name字段固定占100个字符，不足会在尾部补「空白」。在进行LIKE匹配时，这个填充空白并没有移除掉。上表中所有记录的name字段显然都是以填充的空白结尾的，故导致我们的查询结果为空；而remark字段为varchar(100)类型，即变长类型。其不会填充空白

现在我们修改查询语句，来查询name字段含n的数据。如下所示

-- 对name字段进行模糊查询，查询name字段含n的数据
select * from student_info where name like '%n%';

结果符合预期

figure 5.jpeg

自增主键

MySQL下可以通过「AUTO_INCREMENT」属性实现主键自增。而在PostgreSQL下如果想要实现主键自增则可以通过「序列」来实现。如下代码所示，我们先定义一个名为t1_seq的序列，然后将主键的默认值设为该序列的下一个值，这样即可实现所谓的主键自增。其中， 「::」 是PostgreSQL的类型转换符

-- 定义一个序列
CREATE SEQUENCE t1_seq
  START WITH 1  -- 序列起始值
  INCREMENT BY 1 -- 序列步长
  NO MINVALUE   -- 序列最小值
  NO MAXVALUE   -- 序列最大值
  CACHE 1;

-- 创建表，并将主键的默认值设为t1_seq的下一个值
create table t1 (
    id int default nextval('t1_seq'::regclass) not null,
    username varchar(255) null ,
    sex varchar(255) null,
    primary key (id)
);

测试效果如下，符合预期

figure 6.jpeg

事实上我们也可以在建表的时候，直接将主键字段的类型设为「serial」，以实现主键自增。其实serial类型并不是真正的数据类型，其本质上依然还是创建了一个序列

-- 创建表，并将主键类型设为serial，实现主键自增
create table t4 (
    id serial not null,
    username varchar(255) null ,
    sex varchar(255) null,
    primary key (id)
);

继承

PostgreSQL中特别地提供了表的继承。这样在查询父表的数据同时，还可以将子表的数据也一并查询出来。示例如下

-- 建立学生信息总表: stu_info
CREATE SEQUENCE stu_info_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
CREATE TABLE stu_info (
     id    integer default nextval('stu_info_id_seq' :: regclass) not null,
     name varchar(255) null,
     major varchar(255) null,
     primary key (id)
);

-- 建立学生信息分表1: stu_info_1
-- LIKE stu_info INCLUDING ALL:  通过复制stu_info表结构的方式来建stu_info_1表
-- INHERITS (stu_info): 设置stu_info_1表继承自stu_info表
CREATE TABLE stu_info_1 (LIKE stu_info INCLUDING ALL) INHERITS (stu_info);

-- 建立学生信息分表2: stu_info_2
CREATE TABLE stu_info_2 (LIKE stu_info INCLUDING ALL) INHERITS (stu_info);

-- 向各表插入数据
INSERT INTO stu_info (name, major)
VALUES ('孙尚香', null), ('刘备',null);

INSERT INTO stu_info_1 (name, major)
VALUES ('Aaron','金融工程'), ('Bob','生物工程');

INSERT INTO stu_info_2 (name, major)
VALUES ('张三','自动化'), ('李四','计算机科学');

现在我们查询总表的数据

-- 查询总表
SELECT * FROM stu_info;

测试结果如下，符合预期

figure 7.jpeg

当然也可以只查询某个分表

-- 查询分表
SELECT * FROM stu_info_1;

测试结果如下，符合预期

figure 8.jpeg

由于PG此特性，可以大大简化分表场景下的数据查询工作。即数据只存储在各分表中，而父表不存储数据仅用于查询。当然有时我们不仅需要从总表查询数据，还需要确定相关数据在哪个分表中。可以查看记录的隐藏字段tableoid实现

-- tableoid 字段表示数据所在表的对象id (OID)
select tableoid, * from student_info where name = '张三';

可以看到，该记录的tableoid字段值为16525

figure 9.jpeg

进一步地，通过系统表pg_class确定该oid所表示的表

-- 通过pg_class查询该OID相应的表名
select pg_class.relname as table_name from pg_class
where  pg_class.oid = 16525;

结果符合预期，如下所示

figure 10.jpeg

表间数据复制

INSERT INTO SELECT 语句

PG支持INSERT INTO SELECT语句进行表间的数据复制，要求目标表必须存在。示例如下所示

-- 将t_people_old表中符合条件的数据记录(所有字段) 复制到 t_people_new表中
INSERT INTO t_people_new
SELECT * FROM t_people_old WHERE id>=16;

-- 将t_people_old表中符合条件的数据记录(指定字段) 复制到 t_people_new表中
INSERT INTO t_people_new(username, sex, mark)
SELECT username, sex, mark FROM t_people_old WHERE id>=16;

SELECT INTO 语句

对于表间的数据复制，PG还提供了对SELECT INTO 语句的支持。其要求目标表不存在, PG其会根据插入的字段自动创建目标表。示例如下所示

-- 将t_people_old表中符合条件的数据记录(所有字段) 复制到 t_people_new表中
select * into t_people_new
from t_people_old where id>=15 and id<22;

-- 将t_people_old表中符合条件的数据记录(指定字段) 复制到 t_people_new表中
select username, sex, mark into t_people_new
from t_people_old where id>=15 and id<22;