PostgreSQL and days that don't exist?
While updating data in my table i am getting below error.
date/time field value out of range: "2017-2-29"
I want to skip this error and want update statement to continue further.
How can we do this with update statement.
postgresql update
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
While updating data in my table i am getting below error.
date/time field value out of range: "2017-2-29"
I want to skip this error and want update statement to continue further.
How can we do this with update statement.
postgresql update
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
5
By not trying to store an invalid date?
– a_horse_with_no_name
Oct 30 '17 at 14:48
Are you trying to find out if theUPDATEstatement would generate other errors? In a dev/test environment, change the date to something valid (or NULL, if all else fails), or modify the statement not to update that column. However, the simplest solution is to figure out how to fix that bad data (either in the data you're bringing in, or in the update statement itself).
– RDFozz
Oct 30 '17 at 17:12
1
Feb 29 is the date version of 1/0.
– Evan Carroll
Oct 30 '17 at 18:23
1
A general method here to clean data is to load it all into a generic load table with a bunch of text columns, and then find and fix / remove the bad rows. Then insert into the real table from the load table and drop the load table.
– Scott Marlowe
Oct 30 '17 at 18:57
add a comment |
While updating data in my table i am getting below error.
date/time field value out of range: "2017-2-29"
I want to skip this error and want update statement to continue further.
How can we do this with update statement.
postgresql update
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
While updating data in my table i am getting below error.
date/time field value out of range: "2017-2-29"
I want to skip this error and want update statement to continue further.
How can we do this with update statement.
postgresql update
postgresql update
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
edited Oct 30 '17 at 18:21
Evan Carroll
32.9k1072225
32.9k1072225
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
asked Oct 30 '17 at 14:26
user2274074user2274074
15319
15319
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
5
By not trying to store an invalid date?
– a_horse_with_no_name
Oct 30 '17 at 14:48
Are you trying to find out if theUPDATEstatement would generate other errors? In a dev/test environment, change the date to something valid (or NULL, if all else fails), or modify the statement not to update that column. However, the simplest solution is to figure out how to fix that bad data (either in the data you're bringing in, or in the update statement itself).
– RDFozz
Oct 30 '17 at 17:12
1
Feb 29 is the date version of 1/0.
– Evan Carroll
Oct 30 '17 at 18:23
1
A general method here to clean data is to load it all into a generic load table with a bunch of text columns, and then find and fix / remove the bad rows. Then insert into the real table from the load table and drop the load table.
– Scott Marlowe
Oct 30 '17 at 18:57
add a comment |
5
By not trying to store an invalid date?
– a_horse_with_no_name
Oct 30 '17 at 14:48
Are you trying to find out if theUPDATEstatement would generate other errors? In a dev/test environment, change the date to something valid (or NULL, if all else fails), or modify the statement not to update that column. However, the simplest solution is to figure out how to fix that bad data (either in the data you're bringing in, or in the update statement itself).
– RDFozz
Oct 30 '17 at 17:12
1
Feb 29 is the date version of 1/0.
– Evan Carroll
Oct 30 '17 at 18:23
1
A general method here to clean data is to load it all into a generic load table with a bunch of text columns, and then find and fix / remove the bad rows. Then insert into the real table from the load table and drop the load table.
– Scott Marlowe
Oct 30 '17 at 18:57
5
5
By not trying to store an invalid date?
– a_horse_with_no_name
Oct 30 '17 at 14:48
By not trying to store an invalid date?
– a_horse_with_no_name
Oct 30 '17 at 14:48
Are you trying to find out if the
UPDATE statement would generate other errors? In a dev/test environment, change the date to something valid (or NULL, if all else fails), or modify the statement not to update that column. However, the simplest solution is to figure out how to fix that bad data (either in the data you're bringing in, or in the update statement itself).– RDFozz
Oct 30 '17 at 17:12
Are you trying to find out if the
UPDATE statement would generate other errors? In a dev/test environment, change the date to something valid (or NULL, if all else fails), or modify the statement not to update that column. However, the simplest solution is to figure out how to fix that bad data (either in the data you're bringing in, or in the update statement itself).– RDFozz
Oct 30 '17 at 17:12
1
1
Feb 29 is the date version of 1/0.
– Evan Carroll
Oct 30 '17 at 18:23
Feb 29 is the date version of 1/0.
– Evan Carroll
Oct 30 '17 at 18:23
1
1
A general method here to clean data is to load it all into a generic load table with a bunch of text columns, and then find and fix / remove the bad rows. Then insert into the real table from the load table and drop the load table.
– Scott Marlowe
Oct 30 '17 at 18:57
A general method here to clean data is to load it all into a generic load table with a bunch of text columns, and then find and fix / remove the bad rows. Then insert into the real table from the load table and drop the load table.
– Scott Marlowe
Oct 30 '17 at 18:57
add a comment |
1 Answer
1
active
oldest
votes
February has 28 days.
February in 2017 doesn't have 29 days. It has 28. End of story. If you need to store a 29th day, then your definition of date is different than PostgreSQL's and it will not be able to do date math or use date functions with your definition.
What is '2017-02-29'::date + interval '1 day' in your system though? February 30, March 1, or March 2? And, what would '2017-02-29'::date + interval '1 day' - interval '1 day' be?
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
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%2fdba.stackexchange.com%2fquestions%2f189670%2fpostgresql-and-days-that-dont-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
February has 28 days.
February in 2017 doesn't have 29 days. It has 28. End of story. If you need to store a 29th day, then your definition of date is different than PostgreSQL's and it will not be able to do date math or use date functions with your definition.
What is '2017-02-29'::date + interval '1 day' in your system though? February 30, March 1, or March 2? And, what would '2017-02-29'::date + interval '1 day' - interval '1 day' be?
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
add a comment |
February has 28 days.
February in 2017 doesn't have 29 days. It has 28. End of story. If you need to store a 29th day, then your definition of date is different than PostgreSQL's and it will not be able to do date math or use date functions with your definition.
What is '2017-02-29'::date + interval '1 day' in your system though? February 30, March 1, or March 2? And, what would '2017-02-29'::date + interval '1 day' - interval '1 day' be?
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
add a comment |
February has 28 days.
February in 2017 doesn't have 29 days. It has 28. End of story. If you need to store a 29th day, then your definition of date is different than PostgreSQL's and it will not be able to do date math or use date functions with your definition.
What is '2017-02-29'::date + interval '1 day' in your system though? February 30, March 1, or March 2? And, what would '2017-02-29'::date + interval '1 day' - interval '1 day' be?
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
February has 28 days.
February in 2017 doesn't have 29 days. It has 28. End of story. If you need to store a 29th day, then your definition of date is different than PostgreSQL's and it will not be able to do date math or use date functions with your definition.
What is '2017-02-29'::date + interval '1 day' in your system though? February 30, March 1, or March 2? And, what would '2017-02-29'::date + interval '1 day' - interval '1 day' be?
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
answered Oct 30 '17 at 18:18
Evan CarrollEvan Carroll
32.9k1072225
32.9k1072225
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
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%2fdba.stackexchange.com%2fquestions%2f189670%2fpostgresql-and-days-that-dont-exist%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
5
By not trying to store an invalid date?
– a_horse_with_no_name
Oct 30 '17 at 14:48
Are you trying to find out if the
UPDATEstatement would generate other errors? In a dev/test environment, change the date to something valid (or NULL, if all else fails), or modify the statement not to update that column. However, the simplest solution is to figure out how to fix that bad data (either in the data you're bringing in, or in the update statement itself).– RDFozz
Oct 30 '17 at 17:12
1
Feb 29 is the date version of 1/0.
– Evan Carroll
Oct 30 '17 at 18:23
1
A general method here to clean data is to load it all into a generic load table with a bunch of text columns, and then find and fix / remove the bad rows. Then insert into the real table from the load table and drop the load table.
– Scott Marlowe
Oct 30 '17 at 18:57