Postgresql Learn

 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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

CREATE TABLE demo_common(
	int_ int primary key, 
	/*有长度限制,最多n个字符,text 变长无长度限制*/
	varchar_ varchar(40), 
	text_ text,
	/*60.8888，高精度计算（eg:货币金额），速度币decimal慢，精度允许的最大值为1000*/
	numeric_ numeric(6,4), 
	decimal_ decimal,
	/*
	自增整数 smallserial,serial和bigserial不是真正的类型，仅标识作用，相当于AUTO_INCREMENT 属性
	*/
	serial_  serial,
	/*
	money值可以被转换为numeric而不丢失精度。 转换为其他类型可能丢失精度
	*/
	money_ money,
	/*存储二进制串*/
	bytea_ bytea,

	boolean_ boolean, /*真：true yes on 1 / 假：false no off 0*/

	bit_ bit(8),/*位串 一个位串值对于每8位的组需要一个字节 */

	uuid_ uuid, /*uuid a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 (核心数据库不包含任何用于产生UUID的函数)*/

	json_ json,

	jsonb_ jsonb,/*jsonb json 输入格式一直，区别jsonb输入会慢一点，处理速度比json快，不需要解析（输入的时候提前做好）,jsonb列直接支持索引（json只能通过建函数索引），在jsonb上面建gin索引比btree好*/

	array_ int2[] /*text[]  text[][]*/
);


drop table demo_common
select * from demo_common

/*money*/
SELECT '12.34'::float8::numeric::money;
SELECT '52093.89'::money::numeric::float8;

SELECT NULL::boolean


SELECT E'\\xDEADBEEF';
INSERT INTO demo_common 
(int_, varchar_ ,text_ , numeric_, decimal_, money_,  bytea_ ,boolean_ ,bit_       ,uuid_,
json_,
jsonb_,
array_
)
VALUES
(2000,'varchar' ,'text','60.8888','88.6666','￥12.34','\\x10',true     ,B'01000000','a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
'{"bar": "baz", "balance": 7.77, "active":false}',
'{"bar": "baz", "balance": 7.77, "active":false}',
'{1,2,3,4}'  /*ARRAY[1, 2, 3, 4]*/
)

 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
42
43
44

select jsonb '{"a":1,"b":2}' col1,'{"a":1,"b":2}'::jsonb col2

/*json操作符*/
/*1. -> 取出json数组一个对象*/
select '[1,2,3]'::json-> 1  /*输出 2 */
/*2. -> 通过key取出json一个子对象*/
select '{"a":1,"b":2}'::json->'a' /*输出 1 */
/*于上面一致，但返回值是一个text类型*/
select '[1,2,3]'::json->>1
select '{"a":1,"b":2}'::json->>'a' 
/*3. #> 通过指定路径取出json中一个对象*/
select '{"a":{"b":{"c":1}}}'::json#>'{a,b}' /*输出 {"c":1} */
select '{"a":{"b":{"c":1}}}'::json#>'{a,b,c}' /*输出 1 */
select '{"a":{"b":{"c":1}}}'::json#>>'{a,b,c}' /*输出 1 (区别返回值是一个text类型)*/


/*jsonb值比较*/
/*
1. 两个json对象是否相同
*/
select jsonb '[1,2]' = jsonb '[1,2]'   /* 输出 t*/
select jsonb '[1,2]' = jsonb '[2,1]'   /* 输出 f*/
/*
2. @> 左边对象是否包含右边对象
*/
select jsonb '{"a":1,"b":2}' @> jsonb '{"b":2}'   /* 输出 t*/
select jsonb '[1,2]' @> jsonb '[2]'  /* 输出 t*/
/*
3. ? 指定的字符串是否存在于json对象
*/ 
select jsonb '{"a":1,"b":2}' ? 'b'  /* 输出 t*/
/*
4. ?| 右值是一个数组，是否任意一个元素存在于json对象 (json中的元素需要式字符串类型)
*/ 
select jsonb '{"a":1,"b":2}' ?| array['b','c'] /* 输出 t*/
select jsonb '["1","2"]' ?| array ['2','3'] /* 输出 t*/
select jsonb '[1,2]' ?| array ['2','3'] /* 输出 f*/
/*
5. ?& 右值是一个数组，所有元素是否都存在于json对象 (json中的元素需要式字符串类型)
*/
select jsonb '{"a":1,"b":2}' ?& array['b','c'] /* 输出 f*/
select jsonb '["1","2"]' ?& array ['1','2'] /* 输出 t*/
select jsonb '[1,2]' ?& array ['2','3'] /* 输出 f*/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

/*单位解析度 1微妙 除date(日)*/
create table demo_time(
	sn    serial,
	date_ date, /*日期 推荐格式:1999-01-08*/
	timestamp_ TIMESTAMP default now(),/*包括日期和时间（无时区）*/
	timestamptz_ timestamptz default CURRENT_TIMESTAMP,/*包括日期和时间（有时区）*/
	time_ time, /*一天中的时间 00:00:00 - 24:00:00*/
	timetz_ timetz, /*一天中的时间(带时区) 00:00:00+0800 - 24:00:00-0800*/
	interval_ interval  /*时间间隔 1 12:59:10（1 day 12 hours 59 min 10 sec）*/
)

DROP TABLE demo_time 
SELECT * FROM demo_time

INSERT INTO demo_time
(date_ ,       timestamp_ ,            timestamptz_,               time_,         timetz_,       interval_)
VALUES
('1999-01-08','2021-05-12 09:59:31.36','2021-05-12 09:59:31.36+08','20:00:00.22','12:00:00.22+08','1 12:59:10')

SELECT CURRENT_TIMESTAMP

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

create table demo_net(
    sn serial,
    cidr_ cidr, /*互联网域路由（Classless Internet Domain Routing） 保存一个 IPv4 或 IPv6 网络地址声明*/
    inet_ inet,/*inet和cidr类型之间的本质区别是inet接受右边有非零位的网络掩码， 而cidr不接受 192.168.0.1/24  (192.168.0.1 AND 255.255.255.0 = 0.0.0.1)*/
    macaddr_ macaddr,/*mac 6字节（16进制）*/
    macaddr8_ macaddr8/*mac 8字节（16进制） 从macaddr6 转 到macaddr8(第4和第5字节分别设置为FF和FE)*/
)

DROP TABLE demo_net 
SELECT * FROM demo_net

INSERT INTO demo_net
(cidr_,               inet_ ,           macaddr_ ,           macaddr8_ )
VALUES
('192.168.100.128/25','192.168.0.1/24','E0-D5-5E-6F-A4-88','E2:D5:5E:FF:FE:6F:A4:88')
/*'E0-D5-5E-6F-A4-88' == 'E2:D5:5E:FF:FE:6F:A4:88'*/
SELECT macaddr8_set7bit('E0-D5-5E-6F-A4-88');  /* 输出 e2:d5:5e:ff:fe:6f:a4:88 */

 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

/*全文搜索*/
create table demo_text(
	sn serial,
	title  text,
	tsvector_ tsvector  /*tsvector类型表示一个为文本搜索优化的形式下的文档，tsquery类型表示一个文本查询*/
)
DROP TABLE demo_text 
SELECT * FROM demo_text

INSERT INTO  demo_text
(title,tsvector_ )
VALUES
('a fat cat','a fat cat sat on a mat and ate a fat rat')
/*
全文搜索是基于匹配操作符@@实现
*/
select * from demo_text where tsvector_ @@ to_tsquery('cat | mouse') /* true */

select * from demo_text where tsvector_ @@ to_tsquery('cat & mouse') /* false */

select * from demo_text where tsvector_ @@ to_tsquery('english', 'cat | cat')/* true */

select title || ' # ' || tsvector_ as Document FROM demo_text

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

/*
表继承
1.alter table 修改父表结构，也会同时修改子表结构（唯一约束、外键的使用不会扩大到子表）
2.INHERITS本身永远不会继承索引。它只会继承列
*/
create table demo_inherits_parent (
	id int4 primary key,
	col1 text,
	col2 boolean
);
create table demo_inherits_child(
	col3 int
)INHERITS(demo_inherits_parent);

/*1.在继承表（demo_inherits_child）上面插入，从父表（demo_inherits_parent）可以查到，从父表上面插入，继承表上面查不到*/
select * from demo_inherits_parent
select * from demo_inherits_child
insert into demo_inherits_child VALUES(1,'item1',false,1);
insert into demo_inherits_parent VALUES(2,'item2',false);
update demo_inherits_child set col3=100 where id =1 
update demo_inherits_parent set col2=true where id =1 

/*如果只想查询父表记录，查询加 only 关键字*/
select * from only demo_inherits_parent

 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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

/*
分区表
- 通过表继承实现分区表 （一般父表不存数据，只存在子表中）
- 删除分区数据快
- 查询或更新分区记录快
- 
*/
create table track(
	simnum text,  
	lon    decimal,
	lat    decimal,
	/*track_date date not null,*/
	track_time timestamp not null,
	alarm_status int8
);
create table track_20210501 (CHECK (track_time >= DATE '2021-05-1' AND track_time < DATE '2021-05-2'))INHERITS(track);
create table track_20210502 (CHECK (track_time >= DATE '2021-05-2' AND track_time < DATE '2021-05-3'))INHERITS(track);
create table track_20210503 (CHECK (track_time >= DATE '2021-05-3' AND track_time < DATE '2021-05-4'))INHERITS(track);
create table track_20210504 (CHECK (track_time >= DATE '2021-05-4' AND track_time < DATE '2021-05-5'))INHERITS(track);
create table track_20210505 (CHECK (track_time >= DATE '2021-05-5' AND track_time < DATE '2021-05-6'))INHERITS(track);

/*单独在分区表上面建立索引*/
create INDEX idx_track_20210501_track_time on  track_20210501 (simnum,track_time)

drop table track,track_20210501,track_20210502,track_20210503,track_20210504,track_20210505

INSERT INTO track_20210501 VALUES('18860183050',116.46,39.92,'2021-05-01 11:50:00',0);
INSERT INTO track_20210502 VALUES('18860183050',116.46,39.92,'2021-05-02 10:50:00',0);
SELECT * from track
SELECT * from track_20210501

/*查看扫描分区情况*/
EXPLAIN SELECT * from track where track_time >'2021-05-1' and track_time <'2021-05-2'
/*如果要扫描每张分区子表，要把constraint_exclusion = 'off'*/
set constraint_exclusion='on';
EXPLAIN SELECT * from track where track_time >'2021-05-1' and track_time <'2021-05-2';

/*
在父表上面插入数据映射到子表
1. 直接insert 对应 日期的子表
2. 基于触发器
3. 基于 postgresql RULE  规则 :
- 相比触发器，有更显著开销，每次插入检查都有一次开销，适合批量插入的情况，其他更多时候基于触发器更好
- COPY插入不会触发规则，触发器却可以
- 如果插入数据在规则之外，数据会插入到主表而不会报错
*/
/*基于 postgresql RULE*/
CREATE RULE track_20210501 AS ON INSERT TO track WHERE (track_time >= DATE '2021-05-1' AND track_time < DATE '2021-05-2')
DO INSTEAD INSERT INTO track_20210501 VALUES(NEW.*);
CREATE RULE track_20210502 AS ON INSERT TO track WHERE (track_time >= DATE '2021-05-2' AND track_time < DATE '2021-05-3')
DO INSTEAD INSERT INTO track_20210502 VALUES(NEW.*);
CREATE RULE track_20210503 AS ON INSERT TO track WHERE (track_time >= DATE '2021-05-3' AND track_time < DATE '2021-05-4')
DO INSTEAD INSERT INTO track_20210503 VALUES(NEW.*);
CREATE RULE track_20210504 AS ON INSERT TO track WHERE (track_time >= DATE '2021-05-4' AND track_time < DATE '2021-05-5')
DO INSTEAD INSERT INTO track_20210504 VALUES(NEW.*);
CREATE RULE track_20210505 AS ON INSERT TO track WHERE (track_time >= DATE '2021-05-5' AND track_time < DATE '2021-05-6')
DO INSTEAD INSERT INTO track_20210505 VALUES(NEW.*);

INSERT INTO track VALUES('18860183050',116.46,39.92,'2021-05-04 11:50:00',0);
SELECT * from track_20210504

/*基于触发器*/
CREATE OR REPLACE FUNCTION track_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
	IF (NEW.track_time >= DATE '2021-05-1' AND NEW.track_time < DATE '2021-05-2' )
	THEN INSERT INTO track_20210501 VALUES(NEW.*);
	ELSEIF (NEW.track_time >= DATE '2021-05-2' AND NEW.track_time < DATE '2021-05-3' )
	THEN INSERT INTO track_20210501 VALUES(NEW.*);
	ELSEIF (NEW.track_time >= DATE '2021-05-3' AND NEW.track_time < DATE '2021-05-4' )
	THEN INSERT INTO track_20210501 VALUES(NEW.*);
	ELSEIF (NEW.track_time >= DATE '2021-05-4' AND NEW.track_time < DATE '2021-05-5' )
	THEN INSERT INTO track_20210501 VALUES(NEW.*);
	ELSEIF (NEW.track_time >= DATE '2021-05-5' AND NEW.track_time < DATE '2021-05-6' )
	THEN INSERT INTO track_20210501 VALUES(NEW.*);
	ELSE
	RAISE EXCEPTION 'Date out of range . Fix the track_insert_trigger() function!';
	END IF;
	RETURN NULL;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER track_insert_trigger 
BEFORE INSERT ON track 
FOR EACH ROW EXECUTE PROCEDURE track_insert_trigger();


DROP TRIGGER track_insert_trigger on track

INSERT INTO track VALUES('18860183050',116.46,39.92,'2021-05-07 11:50:00',0)  /*触发报错 表不存在*/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

create /*UNIQUE8*/ index /*CONCURRENTLY*/ idx_demo_common_varchar_ 
on demo_common ( varchar_ ASC /*DESC */ NULLS FIRST /*空值排在非空值前面LAST*/)
/*WITH storage_parameter = value (FILLFACTOR = 50) 填充因子*/
/*TABLESPACE tablespace_name*/
/*WHERE predicate */

create table demo_fillfactor(id int)WITH(FILLFACTOR=50) 
/*\d+ idx_demo_common_varchar_*/



/*
1. 数组、jsonb 使用 @>等比较的，使用GIN索引更高效;
2. 建索引时，对表做全表扫描，导致创建过程CURD操作会被阻塞，直到索引创建完毕，通过CONCURRENTLY，可以并行建索引（需要时间更长）;
3. 重建索引不支持CONCURRENTLY，通过在同列上面建新的索引（CONCURRENTLY），建完以后删除旧索引;
4. fillfactor会降低insert的性能,但是update和delete性能将有提升,如果填充率为100%，更新的时候，当前数据页满，会创建一个新页；
*/

select * from pg_indexes where tablename='demo_common';

/*删除索引，默认RESTRICT 有对象依赖这个索引，删除会失败，使用  CASCADE，联通依赖一起删除 ，例如外键约束 */
drop index idx_demo_common_varchar_ RESTRICT 

1
2
3
4
5

脏读 ：事务A 更新余额 1000->500  事务B 可能在A为提交情况，读取到500 ，产生脏读

不可重复读（non-repeatable reads）：事务A 有两次查询 第一次查 1000， 事务B开始更新 -500，并提交；事务A再一次查询，查询到结果500，不一致；

幻读（phantom read）：当前事务中重复执行相同的查询,返回的记录数因另一个事物插入或删除而得到不同的结果；

1

提交覆盖 ：事务A +100 （1100）  事务B -100 （900）；最终有可能是1100， 很容易发生更新丢失，需要做好并发处理

1

事务A更新  记录 A，B  ；事务B更新记录 B，A；这个时候A锁定了记录A ，B锁定了记录B，两个事务互相等待对方释放锁； PostgreSQL将检测这样的情况并中断其中一个事务。

1

防止死锁的最好方法通常是保证所有使用一个数据库的应用都以一致的顺序在多个对象上获得锁;例如两个事务更新顺序都是 A，B

1

行为与FOR SHARE类似，不过锁较弱：SELECT FOR UPDATE会被阻塞；

1
2
3

行为与FOR NO KEY UPDATE类似，不过它在每个检索到的行上获得一个共享锁而不是排他锁；
一个共享锁会阻塞其他事务在这些行上执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE；
但是它不会阻止它们执行SELECT FOR SHARE或者SELECT FOR KEY SHARE

1

行为与FOR UPDATE类似，不过获得的锁较弱；允许 SELECT FOR KEY SHARE；

1

其他尝试UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE这些行的事务将被阻塞，直到当前事务结束

1

操作 ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）命令获取

1

来自事务命令、REFRESH MATERIALIZED VIEW CONCURRENTLY获得

1

由CREATE INDEX（不带CONCURRENTLY）取得;这种模式保护一个表不受并发数据改变的影响。

1

命令UPDATE、DELETE和INSERT在目标表上取得这种锁模式,通常，这种锁模式将被任何修改表中数据的命令取得。

1

SELECT FOR UPDATE和SELECT FOR SHARE命令在目标表上取得一个这种模式的锁 ;

1

SELECT命令在被引用的表上获得一个这种模式的锁。通常，任何只读取表而不修改它的查询都将获得这种锁模式。

1
2
3
4
5
6
7
8

/*二阶段提交*/
/*1. 修改配置 postgresql.conf max_prepared_transactions =10 */
/*
begin;
insert into demo_common values(1);
prepare transaction 'osdba_global_trans_0001'; //分配全局事务ID ,由事务协调器实现
COMMIT prepared 'osdba_global_trans_0001';
*/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

/*执行计划
1. 因为数据集太小而需要快速确认查询是否有使用索引 set enable_seqscan=false （建了索引不一定会走索引，查询优化器会根据实际情况评估）
2. 索引类型
	- seq scan (全表扫描)
	- index scan(索引扫描)
	- bitmap index scan(位图扫描)
3. 条件过滤
	- Filter
	- 如果条件的列上有索引，可能会走索引，不走过滤
4. Nestloop Join (嵌套循环连接)
	- 驱动表小（<10000）,inner表有有效的（Index），这样响应最快
5. Hash Join (散列连接)
	- 使用较小的表 建立散列表，再扫描较大的表 （from a,b where a.id = b.id）
	- 两表（尽然小）可以放在内存，否则需要部分写到磁盘，总成本= 访问两表的成本和
6. Merge Join  (合并连接)
	- 通常性能 散列连接 > 合并连接，如果两表都有索引，且索引扫描的列已经排序，此时合并连接更好
*/
set enable_seqscan=false
EXPLAIN /*(format json)*/ SELECT * from track where simnum='18860183050' and track_time >'2021-04-30' and track_time <'2021-05-5'
EXPLAIN  ANALYZE VERBOSE  SELECT * from track where track_time >'2021-05-1' and track_time <'2021-05-2'
EXPLAIN  ANALYZE VERBOSE  SELECT * from track_20210501 where track_time >'2021-04-30' and track_time <'2021-05-1' and simnum='18860183050'

/*查看表大小，关联的时候 ，小表驱动大表*/
select pg_relation_size('track_20210501')

1
2
3
4
5
6
7

/*手工手机统计信息
- ANALYZE [VERBOSE] table (column)
手动手机表的 统计信息到 pg_statistic ，优化器使用这些统计信息来确定最友的执行计划
*/
ANALYZE track_20210501
select * from pg_statistic
select * from pg_stat_activity

1
2
3
4
5

/*序号*/
create SEQUENCE seqtest ;
select nextval('seqtest');
select currval('seqtest');
select setval('seqtest',1,FALSE)

1
2
3

smart 不允许新连接，等待连接主动关闭以后，服务才会停止
fast   不允许新连接，强制立即关闭，服务才会停止
immediate 立即关闭，子进程立即退出，下次启动数据库回放 WAL日志（紧急时使用）

1
2
3
4
5
6
7
8

SessionA 执行查询
select pg_sleep(600);

SessionB 关闭查询
select pg_backend_pid();
select pg_cancel_backend(11380);
或者
.\pg_ctl.exe kill INT 11380

1
2
3
4
5
6
7

6.配置运行远程局域网连接
修改postgresql.conf  
listen_addresses = '*'

修改pg_hba.conf   
# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
host    all       all   0.0.0.0/0     md5（password、trust）