PostgreSQL 随笔 未归档

0. 日后再整理了

explain

1. distinct on

按照Order by的第一位参数排序，并在分组内保留第一位
distinct on (分组字段) 去重字段(可以是*)

2. values

一行数据 的 集合
可以实现union相同的效果
可以作为一个常量表来select from (values(),(),())

3. generate_series()

序列生成函数，重载参数类型很多，很强大
甚至支持生成连续日期的列，甚至可以带一个interval作为间隔变量

4. 模式

4.1 概述

模式类似于操作系统的目录
一个库可以访问多个不同模式的不同对象(表/视图、数据类型、函数、操作符)
访问其他模式的内容，往往需要该用户有足够的权限

4.2 public

公共模式，所有用户多可以访问其内容
表、函数等对象被默认创建时 ，都是在public模式下的
sql标准中，这种不同用户共享的模式是不存在的，并且它本省也是不安全的，建议不要使用(postregsql支持删去)。

4.3 pg_catalog

pgsql独有的、内置的模式
系统表、视图都是以pg_开头的

4.4 information_shema

信息模式，有一组视图构成
被sql标准所支持的，包含当前库的所有信息
这个模式的拥有者是集簇中的初始数据库用户，并且该用户自然地拥有这个模式上的所有特权
information_shema 和 pg_catalog 可以用来做某些 表、列、类型等特殊查询操作。

5.create type

pgsql 12 官方文档 create type
可以用于指定组合属性的数据类型、排序规则，而避免去创建一张空表

5.1 pgAdmin

当我们需要查看自定义的type时候，通过sql方式查询schema的话，需要对schema相关的视图很熟悉，因此建议使用官方的桌面UI软件——pgAdmin
支持中文、免费
可以很直观的看到数据库服务器的运行状态
数据库中定义的各种数据对象
魔法冲浪下网速可观

6.集合运算

query1 xxx query2
xxx:
	UNION [all]	取并集(默认去重,all不去重)
	INTERSECT	取交集
	EXCEPT 		取差集
queryX:
	其实就是select语句	

7. upsert

insert into ... on conflict(column,,,) do [nothing/update set ,,,]

8. 慢查询

explain
	analyze
		实际运行的过滤语句
		筛除掉的行数
		过滤需要花费的时间
		查询规划器是否利用索引？
			使用索引前 seq scan（并行的扫描）
			使用索引后 index scan	

9. 索引

9.1 索引策略

b-tree
	主流，pgsql查询规划器默认(缺省)的
	大小(等)于、等于(between也等同),不支持不等于
	也支持模式(比如like %、比如正则~、in)匹配的，但注意不支持后缀匹配
Hash
	仅支持简单的=
	索引尺寸、制作时间较差，因此并不推荐
GiST
	不是特定一种索引类型，是一套索引策略的架构，支持用户定义的策略
	甚至还支持二维数据类型
GIN	
	反转索引
	同GiST是一套索引策略的架构,允许用户自定义策略
	pgsql标准发布包支持一维的数据类型
...

9.2 补充

数据量小时没有必要建索引，可能更慢
索引同主键一样，可以复合使用
作为分组的字段，效果更加明显