Postgresql13学习笔记

用户4508443712633
699 阅读11分钟

前言

这篇文章是我在查阅Postgresql13官方文档时写下的笔记，主要记录与Mysql不同的地方，可通过这篇文章快速了解Pgsql和Mysql的区别，具体学习还是要查阅官方文档。

安装

开启远程登录

su postgres 切换到postgres用户

cd ~ 进入用户目录，postgresql默认在这里

cd /13/data 进入data目录，找到postgresql.conf文件，修改listen_addresses = '*'，这个值默认是localhost

找到pg_hba.conf，添加一行 host all all 0.0.0.0/0 scram-sha-256

scram-sha-265如果改成trust，会导致任何密码或无密码都能登录

字符串转义问题

字符串用单引号包围，但如果字符串含有单引号、斜杠等需要转义的字符就会比较麻烦。在postgresql中可以使用字符串字符串来表达字符串，用$$包围的字符串一般不需要转义。

select $$123'/\$$ 查询结果：123'/\

生成列

相当于mysql的虚拟列，但postgresql目前只支持存储的生成列，不支持虚拟的生成列。

存储的生成列在写入（插入或更新）时进行计算，并像正常列一样占用存储空间。虚拟生成的列不占用任何存储空间，并且在读取时会对其进行计算。

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

copy语句

从文件导入大批数据到数据库，使用copy语句，效率高于insert

文件复制到表

copy users from '~/data_export'

文件格式如下：

admin   123456  1
test    123465  2
test    123456  3

与列顺序一致，用tab分隔

表复制到文件

copy users to '~/data_export'

相当于导出功能，导出格式同上

表复制到压缩文件

COPY users TO PROGRAM 'gzip > ~/users_data.gz';

自增字段

postgresql不像mysql有一个自增选项，它是通过数据类型来实现自增的。需要自增时选择以下几种数据类型即可。

伪类型存储大小范围
SMALLSERIAL2字节1 到 32,767
SERIAL4字节1 到 2,147,483,647
BIGSERIAL8字节1 到 922,337,2036,854,775,807

自增字段重复问题

插入数据时如果我们设置了自增字段的值，postgresql并不会把自增序列+1。比如我插入第二条记录，如果我没有设置自增字段的值，那么自增序列会+1变成2，但我设置了自增字段的值，比如设置为2，自增序列就不会+1（虽然字段值是2，但此时自增序列还是1）这就导致我们下次不设置自增字段的值时，自增序列+1变成2，但2已经被上一条记录使用了。

解决办法

首先是不要给自增字段设置值了，给空值让它自增，不然也违反了我们的自增设计。但如果不小心设置了值，就用下面的办法解决

select setval('users_id_seq',(select max(id) from users)) 使用这行sql设置自增序列的值

select max(id) from users 查找users表的最大id，用它作为自增序列的最新值

users_id_seq 用来指定某个自增序列，这个值可以在表的ddl语句中查看。

id bigint NOT NULL DEFAULT nextval('users_id_seq'::regclass) id是自增字段，nextval后面就有自增序列标识符users_id_seq

分组聚合

group by

如果使用了group by语句，则只能查询group by中的列。mysql8也是这样，只不过mysql能够通过修改配置文件避免这种情况，但也只能查组内第一条记录，实际使用我们会和多表连接一起用。

grouping sets

分组集，与group by相似，但可以实现更高级的效果。以下代码使用grouping sets统计品牌销量、尺码销量、所有销量。这个需求如果使用group by需要查询多次，但现在可以在一条sql上完成。

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

rollup

rollup也是用来生成分组集，可以认为是grouping sets的快捷方式。

rollup(c1,c2,c3)生成以下分组集

(c1, c2, c3)
(c1, c2)
(c1)
()

rollup通常用于有层级关系的统计需求，看个例子就懂了

select y,m,d,sum(sales) from a group by rollup(y,m,d) order by y,m,d

ymdsum说明
20213110
20213211
20213315
20213418
2021354三月份销量
20214225
2021435
202141031
2021461四月份销量
20211152021年销量
115全部销量

grouping

grouping用于区分行是来自哪个分组，这里用上面rollup的例子改造下。

select y,m,d,grouping(y,m,d),sum(sales) from a group by rollup(y,m,d) order by y,m,d

ymdsumgrouping说明
202131100
202132110
202133150
202134180
20213541三月份销量
202142250
20214350
2021410310
20214611四月份销量
202111532021年销量
1157全部销量

可以看到grouping分别返回了0/1/3/7，grouping中的每一列，如果都参与了分组，那么都是0，如果某一列没有参与分组，那一列就是1。这里的0/1是二进制，二进制转十进制就得到了0/1/3/7这些数字。

比如ymd分组：000=>0

ym分组：001=>1

y分组：011=>3

cube

cube与rollup类似，只不过分组集不一样，cube会生成所有组合结果

cube (c1,c2,c3)分组集如下

(c1, c2, c3)
(c1, c2)
(c2, c3)
(c1,c3)
(c1)
(c2)
(c3)
()

over

over可以理解为分组但不聚合，利用它可以实现mysql中无法直接实现的效果，比如mysql的group by虽然可以查询其他列，但查询出来的仅仅是组内的第一条记录。而over可以查询组内每一条数据，并且可以使用聚合函数，比如sum、avg、count等。

示例

  1. 按照用户等级分组，计算组内余额总和。

select id,account,password,level,sum(money) over(partition by level) from users

idaccountpasswordlevelsum
1xiaoming1234561189
2zhangsan1234651189
3lisi123456270
4wangwu123465"399

可以看到，不仅统计出了余额总和，还查询出了组内每一条数据。

  1. 上面的语句再加一个order by，结果会有点意思

select id,account,password,level,sum(money) over(partition by level order by money) from users

idaccountpasswordlevelsum
1xiaoming123456189
2zhangsan1234651189
3lisi123456270
4wangwu123465"399

sum列的结果，变成了每一步相加的结果的排序结果

  1. 按用户等级分组，统计组内余额排名

select id,account,password,level,money,rank() over(partition by level order by money desc) from users

idaccountpasswordlevelmoneyrank
1xiaoming12345611001
2zhangsan1234651892
3lisi1234562701
4wangwu1234653991
  • rank() 如果有余额相同的情况，会并列排名，假如并列第二名，那么第三名会取消，下一个排名从第四开始。
  • dense_rank() 也可以并列排名，但不会取消下一名。像上面这种情况，第三名不会被取消。
  • row_number() 不并列排名，余额相同的情况下按照先后顺序排名
  • 上面几个排名方法，会把空值当成最大的，所以查询时需要注意一下。可以用where语句过滤空值
  1. over()不加partition by的情况

select id,account,password,level,money,sum(money) over() from users

idaccountpasswordlevelmoneysum
1xiaoming1234561100358
2zhangsan123465189358
3lisi123456270358
4wangwu123465399358

效果就是把整个表作为一个分组，不加partition by的时候，还是可以加order by

  1. 复用over()

select sum(money) over w,avg(money) over w from users where money is not null window w as (partition by level)

window w as (partition by level)定义了over，名字是w

sum(money) over w,avg(money) over w进行了复用

表继承

表继承可以让子表继承父表的结构和部分约束（检查约束、非空约束），唯一约束、主键、外键不会被继承，表继承实际上是把面向对象的思想引入表设计中。

语法

INHERITS (父表)

CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);

数据存放在哪？

  • 往子表添加数据时，数据是存放在子表的
  • 往父表添加数据时，数据存放在父表，子表查不到这些数据
  • 查询父表时，也能查到子表的数据，实际上他是先查子表，再跟父表合并实现的，类似多表连接，父表中并没有存放子表的数据。
  • 查询子表不需要先查父表，因为数据本身就存放在子表

应用场景

  1. 每个表都有通用字段，把通用字段放到父表，例如is_delete、create_time、modify_time
  2. postgresql的分区表似乎使用表继承来实现的，后面再介绍分区表

权限控制

postgresql用户、组都是role，role如果有多个成员，那它就是组

create role prod 创建名为prod的角色

grant select,insert,update,delete on all tables in schema public to prod 授权所有表使用增删查改功能

grant select,insert,update,delete on table users to prod 授权users表使用增删查改功能

revoke select on users from prod 撤销users表的查询权限

revoke all on all tables in schema public from test 撤销所有表权限

grant usage on all sequences in schema public to prod 授权所有序列使用currval和nextval功能，否则自增字段无法工作

grant USAGE on sequence users_id_seq to prod授权users_id_seq序列使用currval和nextval功能

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT select,insert,update,delete ON TABLES TO prod 授权新建表使用增删查改功能

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT usage ON sequences TO prod 授权新建序列使用currval和nextval功能

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM prod 撤销新建表的查询权限

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE usage ON sequences FROM prod 撤销新建序列的使用权限

grant prod to prod_user将用户prod_user加入权限组prod，prod_user就拥有权限组的权限

用户相关

ALTER USER postgres PASSWORD 'Pg123456###'修改用户密码

DROP user username 删除用户，删除前需要撤销此用户所有权限

命令行登录

su postgres 切换到postgres用户

psql 直接登录

挖矿漏洞

postgresql有远程执行漏洞，密码太简单很有可能会被挖矿病毒入侵。我已亲身体验一番~

returning语句

语法与select类似，可用在insert、update、delete语句 update users set password='13246546' where account='sad' returning * 返回修改后的行

insert into users(account,password) values('sad','sad132')returning id 返回新插入行的id

lateral语句

lateral使子查询可以引用前面的from项目提供的列。（没有lateral，每个子查询都是独立评估的，因此不能交叉引用任何其他from项目。

SELECT * FROM foo,LATERAL(SELECT * FROM bar WHERE bar.id = foo.bar_id)ss

交集、并集、差异

select * from a except all select * from b 查询在a表中存在但在b表中不存在的记录

select * from a union all select * from b 查询a表和b表的并集，也就是返回两个表的记录

select * from a intersect all select * from b 查询a表和b表中都有的记录

上面几个sql都加了all，不加all会去除重复记录，根据自己需求来

排序

使用order by排序时，我们会发现空值永远比其他值大，很多场景下这种排序规则都不太合理，以往我的解决方法是过滤空值，空值不参与排序，现在postgresql提供了其他方法。

select * from a order by sales desc nulls first 空值最大，默认就是这种

select * from a order by sales desc nulls last 空值最小

with as

with as 用于简化复杂查询，网上有用这个语句优化查询速度的博客，所以可以考虑用这个语句来构建复杂查询。

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

还可以在with语句中修改数据

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
avatar
文章
阅读
粉丝