跳到主要内容

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​

函数返回影响行​

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
    • 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;

分组里选择最后一条数据​

  1. distinct - 推荐
select distinct on (id) id, date, another_info
from the_table
order by id, date desc;
  1. 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​

LATERAL JOIN​

不可以再 WHERE 使用别名​

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING 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;
$$;