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 顯示資料位於不同塊上