postgresql - 如何在 postgresql 中为更新添加默认值？

Question

我有一个 bool 值列，我在其中添加了默认值 false。据我了解，当我向表中添加新行时，默认值将生效。但是，我也希望在更新行时使用默认值 false。这可能吗？

例如:

ALTER TABLE mytable ALTER COLUMN mycolumn SET DEFAULT false;

-- In below query the mycolumn should default to false 
update mytable set somecolumn = 'somevalue' 
where somecolumn = 'anothervalue'

-- In below query mycolumn should be true since the value is specified
update mytable set somecolumn = 'somevalue' mycolumn = true 
where somecolumn = 'anothervalue'

Answer 1

您可以按如下方式创建触发器:

CREATE FUNCTION mytriggerfunction()
RETURNS TRIGGER AS '                                        
BEGIN
  IF NEW.mycolumn IS NULL OR NEW.mycolumn='''' THEN
    NEW.mycolumn := ''somedefaultvalue'';
  END IF;
  RETURN NEW;
END' LANGUAGE 'plpgsql';

CREATE TRIGGER mytrigger
BEFORE UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE mytriggerfunction();