How to add a default value for update in postgresql?

user_654203 0
Viewed 2629

I have a boolean column where I have added a default value of false. It is my understanding that the default value will kick in when I add new rows to the table. However, I would like a default value of false when the rows are updated as well. Is that possible?

For example:

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'
postgresql
2 Answers

You can create a trigger as follows:

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();
user_62344720

It's possible. But only if you add a trigger. There is no special clause in the create table statement to do this.

user_676060
Related