PostgreSQL如何預設分區標識列？

PostgreSQL 11

為分區表上的標識列生成預設值的最佳方法是什麼。

例如

CREATE TABLE data.log
(
 id              BIGINT GENERATED ALWAYS AS IDENTITY
                 (
                   INCREMENT BY 1
                   MINVALUE -9223372036854775808
                   MAXVALUE 9223372036854775807
                   START WITH -9223372036854775808
                   RESTART WITH -9223372036854775808
                   CYCLE
                 ),
 epoch_millis    BIGINT NOT NULL,
 message         TEXT NOT NULL

) PARTITION BY RANGE (epoch_millis);

CREATE TABLE data.foo_log
PARTITION OF data.log
(
 PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);

如果我做：

INSERT INTO data.foo_log (epoch_millis, message)
VALUES (1000000, 'hello');

我得到：

錯誤：“id”列中的空值違反非空約束

細節：失敗行包含（null，1000000，hello）。

SQL 狀態：23502

因為預設生成的值不會應用於分區，除非我將它插入到根表中，如下所示：

INSERT INTO data.log (epoch_millis, message)
VALUES (1000000, 'hello');

有時出於性能原因（例如進行批量複製），我想直接插入特定分區。

我可以讓它工作的唯一方法是創建分區，同時知道為標識列隱式創建的序列，如下所示：

CREATE TABLE data.foo_log
PARTITION OF data.log
(
 id DEFAULT nextval('data.log_id_seq'),
 PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);

有沒有更好的方法來做到這一點，如果有，怎麼做？

一般來說，我不知道有更好的解決方案。不過有一些小事：

pg_get_serial_sequence()

如果您不知道父級的隱式序列的名稱，請使用pg_get_serial_sequence().

SELECT pg_get_serial_sequence('data.log', 'id');

您甚至可以CREATE TABLE直接在腳本中使用表達式，但這會帶來非常小的額外成本來計算預設值的實際名稱（我認為每個事務一次），因為這是關於性能優化……

COPY覆蓋GENERATED ALWAYS，但不觸發

id將列定義為GENERATED ALWAYS AS IDENTITY不允許id在INSERT語句中為列提供使用者值，除非添加“覆蓋”子句，例如：

INSERT INTO data.log (epoch_millis, message) **OVERRIDING USER VALUE**
VALUES (1000000, 'hello');

手冊：

OVERRIDING USER VALUE

如果指定了此子句，則為標識列提供的任何值都將被忽略，並應用預設的序列生成值。

例如，在表之間複製值時，此子句很有用。寫入INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 將從tbl1所有不是身份列的列中 複製，而身份列的tbl2值tbl2將由與關聯的序列生成tbl2。

**COPY**在任何情況下都覆蓋。手冊：

對於標識列，該COPY FROM命令將始終寫入輸入數據中提供的列值，例如INSERToption OVERRIDING SYSTEM VALUE。

但是，在使用您的解決方案直接寫入分區時，INSERT也會覆蓋，因此您有責任避免id直接為列提供使用者值。另一種方法是使用觸發器而不是分區中的預設值：

CREATE OR REPLACE FUNCTION trg_log_default_id()
 RETURNS trigger AS
$func$
BEGIN
  NEW.id := nextval('data.log_id_seq')
  RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insbef_default_id
 BEFORE INSERT ON data.foo_log  -- the partition
 FOR EACH ROW
 EXECUTE PROCEDURE trg_log_default_id();

無論如何，這都會從序列中分配一個數字，更接近地模擬GENERATED ALWAYS父級的行為 - 更嚴格，甚至還可以防止COPY違反您的規則。手冊：

COPY FROM將呼叫任何觸發器並檢查目標表上的約束。

觸發器比普通的預設值貴一點。並且它會為通過父表的正常插入每行刻錄一個額外的序列號。（應該可以在觸發器中區分大小寫，現在沒試過。）

引用自：https://dba.stackexchange.com/questions/225392