指定另一个字段应具有与INSERT上的id字段相同的值 [英] Specify that another field should have the same value as the id field on INSERT

问题描述

在Postgres表中，我有一个 id 字段（系统生成的标识符）和一个 other_id 字段。在插入期间，如果未提供 other_id 字段，我希望 other_id 接受id的值领域。这可能吗？如果是，怎么办？

In a Postgres table I have an id field (system generated identifier) and an other_id field. During insert, if the other_id field is not supplied I'd like the other_id to take on the value of the id field. Is this possible? If so, how?

推荐答案

我会考虑一个设计，其中 other_id 保持 NULL ，而不是重复存储相同的值。您可以使用以下查询在查询中模拟当前设计：

I would consider a design, where other_id stays NULL instead of storing the same value redundantly. You can emulate your current design in queries with:

COALESCE(other_id, id) As other_id

如果这不是一个选择，请在插入之前创建触发器 ，这可能会因为违反插入规则而引发错误，或者只会覆盖 other_id 。真的很简单。或者，根据以下内容：如果未提供other_id字段，则仅当该列仍为 NULL 时才这样做。

If that's not an option, create a trigger BEFORE INSERT, that either throws an error for violating inserts or just overwrites other_id regardless. That's pretty simple, really. Or, according to this: if the other_id field is not supplied, do that only if the column is still NULL.

对于名为 tbl 的表：

CREATE FUNCTION trg_tbl_ins_bef()
  RETURNS trigger AS
$func$
BEGIN
IF NEW.other_id IS NULL THEN -- only if still NULL
   NEW.other_id := NEW.id;   -- or just overwrite without IF ...
END IF;
RETURN NEW;

END
$func$ LANGUAGE plpgsql;

并触发：

CREATE TRIGGER ins_bef
BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_ins_bef()

这篇关于指定另一个字段应具有与INSERT上的id字段相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！