PostgreSQL 扩展语法

2019-03-25  本文已影响0人  越过山丘xyz

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;

批量插入

insert into table1(id, info)  select id, info from table2;
insert into table1(id, info) values (1002, '{}'), (...), ...;
-- 导出
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;

数据抽样

select * from table4 tablesample system(0.01); -- 1%
select * from table4 tablesample bernoulli(0.01); -- 1%
上一篇 下一篇

猜你喜欢

热点阅读