iT邦幫忙

2025 iThome 鐵人賽

DAY 8
1

昨天介紹過如何使用 Check Constraints , CHECK 使用上雖然很方便,但要注意的是,CHECK 最好只針對單一筆資料做條件的判斷,不要跨多筆資料,不然當其他筆資料變動時, CHECK 所檢查的條件會跟之前的不一樣,而且使用者並不知道。

舉例來說, employees 記錄公司有哪些員工和薪資,今天公司想要提升薪資水平,所以新人和員工變動之後的薪資,不得低於現有平均,於是使用 CHECK (salary >= avg_salary()) 檢查。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    salary NUMERIC
);
CREATE FUNCTION avg_salary() RETURNS NUMERIC AS $$
    SELECT AVG(salary) FROM employees;
$$ LANGUAGE SQL;

ALTER TABLE employees ADD CHECK (salary >= avg_salary())

今天有兩名新進員工的薪水平均是5500。

INSERT INTO employees (name, salary) VALUES ('Alice', 5000);
INSERT INTO employees (name, salary) VALUES ('Bob', 6000);

後來在招聘了 Charlie ,給的薪水是5499,因為 5499 < 5500,輸入的時候會被拒絕。

但後來在人資調整了 Bob 的薪資之後,給 Charlie 5499就又可以了,這意味著 CHECK (salary >= avg_salary()) 這個條件無時無刻都在變動,想像一下,如果需要用pg_dump/pg_restore備份和還原資料庫,因為 avg_salary() 隨時在變動,而且條件是綁在table上,很有可以能會在還原的過程中Insert失敗。

如果真的得這麼做,建議使用trigger, trigger function 裡可以加判斷,例如偵測某個 session 或 flag,來決定是否暫時跳過檢查,或者在還原期間關掉所有trigger,避免還原時失敗。

set -euo pipefail

DB="${1:?target DB name required}"
DUMP_FILE="${2:?path to .dump/.tar required}"

pg_restore \
  --dbname="$DB" \
  --single-transaction \
  --disable-triggers \
  --no-owner --no-privileges \
  "$DUMP_FILE"

上一篇
Day 7: Check Constraints簡介
下一篇
Day 9: 陣列資料型態
系列文
我所不知道的PostgreSQL 30天30
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言