PostgreSQL SQL 问题
Note
- comment 不能在创建表时指定
- column 的 generated 不能修改
- column 只支持 STORE 的生成列,不支持虚拟列
- JSON Functions and Operators
PRIMARY KEY
~=UNIQUE
+NOT NULL
- FK
- MATCH SIMPLE
- MATCH FULL
- 都不 null,或都 null
MATCH PARTIAL
- CONSTRAINTS
- DEFERRED | IMMEDIATE
- 创建时
- DEFERRABLE INITIALLY DEFERRED
- DEFERRABLE INITIALLY IMMEDIATE
- NOT DEFERRABLE
XML xpath 返回结果包含 CDATA
-- PG 12+ 返回 <![CDATA[text]]>
-- PG 11 返回 text
select unnest(xpath('/s/text()','<s><![CDATA[text]]></s>'));
-- 添加 string 转换返回正常
select unnest(xpath('string(/s)','<s><![CDATA[text]]></s>'::xml));
系统信息
select version();
show server_version;
show server_version_num;
show server_encoding;
静态数据行
SELECT *
FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num, letter);
分组聚合
- cube
- GROUPING SETS, CUBE, ROLLUP
rollup(a,b,c)
=>grouping sets((a,b,c),(a,b),(a),())
- cube((a),(b),(c)) grouping sets((a,b,c),(a,b),(a,c),(a),(b,c),(b),(c),())
GROUP BY a, b, c
-- 对等
ROLLUP ( a, b , c)
-- 对等
GROUPING SETS (
( a, b, c ),
( a, b ),
( a ),
( )
)
CUBE ( a, b, c )
-- 对等
GROUPING SETS (
( a, b, c ),
( a, b ),
( a, c ),
( a ),
( b, c ),
( b ),
( c ),
( )
)
CUBE ( (a, b), (c, d) )
-- 对等
GROUPING SETS (
( a, b, c, d ),
( a, b ),
( c, d ),
( )
)
function vs procedure
- function
- 不可以操作事物
- 使用 SELECT 调用
- procedure
- 没有返回值
- 有 INOUT 参数
- 可以 commit 和 rollback
- 使用 CALL 调用
- 不可以嵌套到其他 DDL - SELECT,INSERT,UPDATE,DELETE
plpgsql vs sql
- sql
- 简单
- 已经熟悉 SQL
- plpgsql
- 支持复杂语法
- 支持控制流 - 能做到单个 SQL 做不到的事情
- https://stackoverflow.com/a/24771561/1870054
函数返回 影响行
get diagnostics cnt = row_count;
return cnt;
正则
regexp_match(string, pattern [, flags ]) returns text[]
- flags
- g - global
SELECT (regexp_match('200万人民币', '[\d.]+'))[1];
SELECT (regexp_match('200万人民币', '\D+$'))[1];
生成带前缀的 UUID 主键
- 例如用于 GraphQL 通过 ID 判断类型
create table test
(
id text default 'test-' || public.gen_random_uuid() primary key,
value text
);
insert into test(value)
values ('test');
select *
from test;
推荐主键创建方式
- 对比 serial
- 有归属关系
- 更加规范
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
ALWAYS
- INSERT 指定 ID 不会生效,需要
OVERRIDING SYSTEM VALUE
- UPDATE 不允许修改 ID
- INSERT 指定 ID 不会生效,需要
BY DEFAULT
- 用户指定值优先级更高
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
-- 也可以针对生成设置更多参数
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY
(MINVALUE 0 START WITH 0 CACHE 20)
PRIMARY KEY,
);
Operator
-- 操作符也是函数
SELECT 3 OPERATOR(pg_catalog.+) 4;
分组里选择最后一条数据
- distinct - 推荐
select distinct on (id) id, date, another_info
from the_table
order by id, date desc;
- window
select data
from (
select data,
row_number()
over (partition by data ->> 'groupId' order by item_date desc) as rn
from pulled_items
) lt
where rn = 1
ON CONFLICT 多列 UNIQUE
- https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
- conflict_target can perform unique index inference
- 多列 UNIQUE 会有问题 - https://stackoverflow.com/a/38066008/1870054
- 建议通过 generate 列调整 uniqe 逻辑
- 或者再建一个包含所有 unique 列的索引
- 如果想要返回 EXCLUDED 可以使用 CTE 多步执行 https://stackoverflow.com/a/35953488/1870054
LATERAL JOIN
- 执行一次
- 可交叉引用其他 FROM
- https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL
不可以再 WHERE 使用别名
- 不可以再 WHERE 和 HAVING 使用别名
- 如果一定要,可以考虑 CTE 或 subquery
An output column's name can be used to refer to the column's value in
ORDER BY
andGROUP BY
clauses, but not in theWHERE
orHAVING
clauses; there you must write out the expression instead.
select username, profiles.age as profile_age
from users,
left join profiles on (profiles.user_id = users.id)
-- 不支持 引用
where profile_age > 18;
csv
COPY (SELECT * FROM foo) TO '/tmp/test.csv' WITH CSV DELIMITER ',' HEADER;
psql
\copy (SELECT * FROM foo) TO '/tmp/test.csv' WITH CSV DELIMITER ',' HEADER;
\copy (SELECT * FROM foo) TO STDOUT WITH CSV DELIMITER ',' HEADER;
create role if not exists
DO
$do$
BEGIN
IF EXISTS (
SELECT FROM pg_catalog.pg_roles
WHERE rolname = 'my_user') THEN
RAISE NOTICE 'Role "my_user" already exists. Skipping.';
ELSE
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
END IF;
END
$do$;
role cannot be dropped because some objects depend on it - privileges for table users
REASSIGN OWNED BY test TO postgres;
DROP OWNED BY test;
DROP USER test;
create role if not exists
DO
$do$
BEGIN
IF EXISTS (
SELECT FROM pg_catalog.pg_roles
WHERE rolname = 'my_user') THEN
RAISE NOTICE 'Role "my_user" already exists. Skipping.';
ELSE
CREATE ROLE my_user;
END IF;
END
$do$;
- 处理 EXCEPTION 比提前检测更慢
create policy if not exists
do
$do$
begin
if exists(select *
from pg_catalog.pg_policies
where (schemaname, tablename, policyname) = ('app', 'service_accounts', 'tid'))
then
raise notice 'policy already exists';
else
create policy tid on app.service_accounts using (tid = current_tenant_id());
end if;
end
$do$;
add foreign key if not exists
DO
$$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'flow_active_stage_id_fkey') THEN
alter table flow
add constraint flow_active_stage_id_fkey foreign key (active_stage_id) references flow_stage (id);
END IF;
END;
$$;