RETURNING

pg 中支持 returning 子句，可以实现相应的功能。目前 PostgreSQL 支持 insert、delete、update 的 returning。

insert returning 返回的是新插入的值。

delete returning 返回的是被删除的值。

update returning 返回的是更新后的值，不能返回更新前的值。

-- 创建示例表
CREATE TABLE test(id serial, name varchar(20));
-- 插入
insert into test(name) values('aa') returning name;
-- 修改
update test set name='bb' where id=1 returning name;
-- 删除
DELETE FROM test_re WHERE id = 1 RETURNING name;

UPSERT

支持 INSERT 语句定义 ON CONFLICT DO UPDATE/IGNORE 属性，当插入 SQL 违反约束的情况下定义动作，而不抛出错误

-- 创建测试数据表
create table t (id int constraint idx_t_id primary key,name varchar(20) constraint cst_name not null);
insert into t values(1,'rudy');
-- 根据字段，当 id 冲突时更新 name 值
insert into t values(1,'rudy1') 
    ON CONFLICT(id) 
    do update set name=EXCLUDED.name;
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  2 | rudy3
-- 也可以直接指定约束名，此时不需要字段，在实际应用中，最好使用字段名
insert into t values(2,'rudy3') 
    ON CONFLICT ON CONSTRAINT idx_t_id 
    do update set name=EXCLUDED.name ; 
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  2 | rudy3
(2 rows)
-- 根据where条件选择性更新，由于id没有大于10的数据，故更新0条数据     
insert into t values(2,'rudy4') 
    ON CONFLICT ON CONSTRAINT idx_t_id
    do update set name=EXCLUDED.name where t.id>10 ;
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  2 | rudy3
-- 只插入满足条件的数据行 
insert into t values(2,'rudy4'),(3,'rudy3') 
    ON CONFLICT(id)
    do nothing ; 
postgres=# select * from t;
 id | name  
----+-------
  1 | rudy1
  2 | rudy3
  3 | rudy3

TABLESAMPLE

SELECT *
  FROM tablename
[ TABLESAMPLE sampling_method ( argument [, …] ) [ REPEATABLE ( seed ) ] ]

TABLESAMPLE 子句表示应该用指定的 sampling_method 来检索表中行的子集。主要包括两种采样 方法：BERNOULLI 和 SYSTEM。

原始抽样方式

select * from test01 order by random() limit 2;

SYSTEM 抽样方式

SYSTEM 抽样方式为随机抽取表上数据块上的数据，理论上每个块被检索的概率是相同的，被选中块中的所有行都会被返回。

-- 抽样银子为 0.01，返回 1% 的数据
-- ctid 第一位表示逻辑数据块编号，第二位表示逻辑块上的数据的逻辑编号
SELECT ctid,* FROM test01 TABLESAMPLE SYSTEM(0.01);
-- 验证
-- 查看块数量
SELECT relname, relpages
  FROM pg_class
 WHERE relname='test01';
-- 数据量/relpages = 每个块记录数
-- 返回量：每个记录数*块数

BERNOULLI

BERNOULLI 为随机抽取表的数据行数据，抽样级别为数据行级别。比 SYSTEM 数量更准确，但性能上低很多

-- 抽样银子为 0.01，返回 1% 的数据
-- ctid 第一位表示逻辑数据块编号，第二位表示逻辑块上的数据的逻辑编号
SELECT ctid,* FROM test01 TABLESAMPLE BERNOULLI(0.01);
-- 验证
-- ctid 显示数据位于不同块上

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员