How to add a default value for update in postgresql?
1
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
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
add a comment |
1
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
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
Would you allow to explicitly set it toNULLin anUPDATE?
– sticky bit
Jan 3 at 15:18
add a comment |
1
1
1
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
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
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
postgresql
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
asked Jan 3 at 14:43
AnthonyAnthony
10.2k2294185
10.2k2294185
Would you allow to explicitly set it toNULLin anUPDATE?
– sticky bit
Jan 3 at 15:18
add a comment |
Would you allow to explicitly set it toNULLin anUPDATE?
– sticky bit
Jan 3 at 15:18
Would you allow to explicitly set it to
NULL in an UPDATE?– sticky bit
Jan 3 at 15:18
Would you allow to explicitly set it to
NULL in an UPDATE?– sticky bit
Jan 3 at 15:18
add a comment |
2 Answers
2
active
oldest
votes
1
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();
answered Jan 3 at 15:24
vyrussvyruss
112
@Anthony does this help?
– vyruss
Jan 9 at 13:56
add a comment |
0
It's possible. But only if you add a trigger. There is no special clause in the create table statement to do this.
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
How would I add a trigger to accomplish this?
– Anthony
Jan 3 at 14:49
@Anthony: a trigger knows the old value and the new value for every column. If old.col=true and new.col=true you don't know wetherSET col=truewas in the UPDATE. If you care about that case, the trigger is not your solution.
– Daniel Vérité
Jan 3 at 18:25
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54024518%2fhow-to-add-a-default-value-for-update-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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();
answered Jan 3 at 15:24
vyrussvyruss
112
@Anthony does this help?
– vyruss
Jan 9 at 13:56
add a comment |
1
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();
answered Jan 3 at 15:24
vyrussvyruss
112
@Anthony does this help?
– vyruss
Jan 9 at 13:56
add a comment |
1
1
1
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();
answered Jan 3 at 15:24
vyrussvyruss
112
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();
answered Jan 3 at 15:24
vyrussvyruss
112
answered Jan 3 at 15:24
vyrussvyruss
112
answered Jan 3 at 15:24
vyrussvyruss
112
answered Jan 3 at 15:24
vyrussvyruss
112
112
@Anthony does this help?
– vyruss
Jan 9 at 13:56
add a comment |
@Anthony does this help?
– vyruss
Jan 9 at 13:56
@Anthony does this help?
– vyruss
Jan 9 at 13:56
@Anthony does this help?
– vyruss
Jan 9 at 13:56
add a comment |
0
It's possible. But only if you add a trigger. There is no special clause in the create table statement to do this.
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
How would I add a trigger to accomplish this?
– Anthony
Jan 3 at 14:49
@Anthony: a trigger knows the old value and the new value for every column. If old.col=true and new.col=true you don't know wetherSET col=truewas in the UPDATE. If you care about that case, the trigger is not your solution.
– Daniel Vérité
Jan 3 at 18:25
add a comment |
0
It's possible. But only if you add a trigger. There is no special clause in the create table statement to do this.
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
How would I add a trigger to accomplish this?
– Anthony
Jan 3 at 14:49
@Anthony: a trigger knows the old value and the new value for every column. If old.col=true and new.col=true you don't know wetherSET col=truewas in the UPDATE. If you care about that case, the trigger is not your solution.
– Daniel Vérité
Jan 3 at 18:25
add a comment |
0
0
0
It's possible. But only if you add a trigger. There is no special clause in the create table statement to do this.
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
It's possible. But only if you add a trigger. There is no special clause in the create table statement to do this.
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
answered Jan 3 at 14:47
Pablo Santa CruzPablo Santa Cruz
136k27202254
136k27202254
How would I add a trigger to accomplish this?
– Anthony
Jan 3 at 14:49
@Anthony: a trigger knows the old value and the new value for every column. If old.col=true and new.col=true you don't know wetherSET col=truewas in the UPDATE. If you care about that case, the trigger is not your solution.
– Daniel Vérité
Jan 3 at 18:25
add a comment |
How would I add a trigger to accomplish this?
– Anthony
Jan 3 at 14:49
@Anthony: a trigger knows the old value and the new value for every column. If old.col=true and new.col=true you don't know wetherSET col=truewas in the UPDATE. If you care about that case, the trigger is not your solution.
– Daniel Vérité
Jan 3 at 18:25
How would I add a trigger to accomplish this?
– Anthony
Jan 3 at 14:49
How would I add a trigger to accomplish this?
– Anthony
Jan 3 at 14:49
@Anthony: a trigger knows the old value and the new value for every column. If old.col=true and new.col=true you don't know wether
SET col=true was in the UPDATE. If you care about that case, the trigger is not your solution.– Daniel Vérité
Jan 3 at 18:25
@Anthony: a trigger knows the old value and the new value for every column. If old.col=true and new.col=true you don't know wether
SET col=true was in the UPDATE. If you care about that case, the trigger is not your solution.– Daniel Vérité
Jan 3 at 18:25
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54024518%2fhow-to-add-a-default-value-for-update-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Would you allow to explicitly set it to
NULLin anUPDATE?– sticky bit
Jan 3 at 15:18