PostgreSQL 扩展语法

WITH 查询

WITH 是在复杂查询中定义的一个辅助语句，相当于一个一次性的视图：

with t1 as (
  select * from table1
), t2 as (
  select * from table2
)
select * from t1, t2 where t1.id = t2.id;

WITH 可以通过递归属性 RECURSIVE 来实现递归查询操作：

-- example 1
with recursive t(x) as(
  select 1 -- 这个作为初次参数，找下一个
  union
  select x + 1 -- (x + 1) 作为下一次的参数, 如果输出为空就停止递归
  from t
  where x < 5 -- 对输入的参数进行判定
)
select * from t;

-- example 2
create table public.Area(
  id int4,
  name text,
  fatherid int4
);
insert into
  public.Area
values
       (1, '中国', 0),
       (2, '辽宁', 1),
       (3, '山东', 1),
       (4, '沈阳', 2),
       (5, '大连', 2),
       (6, '济南', 3),
       (7, '和平区', 4),
       (8, '沈河区', 4);
select * from Area;
with recursive area_t as(
  select * from Area where id = 7 -- (7, 和平区, 4) 作为参数传入
  union all
  select a.* from Area as a, area_t as at where a.id = at.fatherid
)
select *
from area_t
order by id asc;

批量插入

• 使用 SELECT

insert into table1(id, info)  select id, info from table2;

• 使用 VALUES

insert into table1(id, info) values (1002, '{}'), (...), ...;

• 使用 COPY

  COPY 可以将一定格式的数据导入到数据库中：

-- 导出
copy table1 to '/home/aladdin/laboratory/table1.txt';
-- 导入
copy table1 from '/home/aladdin/laboratory/table1.txt';

RETURNING 使用

RETURNING 可以返回更新操作后的值：

-- 插入
insert into table1(id, info) values (1003, '{"name": "bilib", "addr": "in china"}') returning *;
-- 更新
update table1 set info = '{"name": "coop", "addr": "in usa"}' where id = 1003 returning *;
-- 删除
delete from table1 where id = 1003 returning *;

UPSERT 使用

一般情况下，当我们批量插入数据的时候，如果其中某一条违反了主键约束(重复的主见)，那么就会造成整个插入操作的事务回滚。

遇到这种情况，我们完全可以对重复主键的数据进行更新或者 Do Nothing 操作：

create table table4(
  id int4 primary key,
  name text
);
insert into table4(id, name) values (1001, 'aladdin'), (1002, 'bilib'), (1003, 'coop');

-- UPSERT 操作
insert into table4(id, name) values (1001, 'apple')
on conflict (id)
-- 可以选择什么也不做
-- do nothing
do update set name = EXCLUDED.name;

数据抽样

• SYSTEM 抽样

  SYSTEM 是数据块级别的随机抽样，如果每个数据块大小为 100 条数据，我们想抽取 150 条数据，SYSTEM 抽取到的可能是 200 条数据也可能是 100 条数据，因为这样抽取一个数据块也行，两个数据块也行。

  虽然 SYSTEM 抽样是块级别的，抽取的数量不是很准确，但是速度很快：

select * from table4 tablesample system(0.01); -- 1%

• BERNOULLI 抽样

  BERNOULLI 抽样是随机抽取表中的数据行，并返回指定百分比的数据，是数据级别的抽样：

select * from table4 tablesample bernoulli(0.01); -- 1%