CODEWITHSUNDEEP
postgresql
Anthony
Anthony

Reputation: 35938

How to add a default value for update in postgresql?

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'

Upvotes: 5

Views: 7246

Answers (2)

vyruss
vyruss

Reputation: 51

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

Upvotes: 3

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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

Upvotes: 0

Related Questions