From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: DATESTYLE and 0000-00-00
Date: Thu, 6 Oct 2005 14:15:12 -0400
Message-ID: <20051006181512.GG28948@phlogiston.dyndns.org> (raw)
In-Reply-To: <Pine.LNX.4.63.0510060900120.11396@localhost.localdomain>
References: <Pine.LNX.4.63.0510060900120.11396@localhost.localdomain>
On Thu, Oct 06, 2005 at 09:01:22AM -0400, Joshua Kramer wrote:
> I have my DATESTYLE set to ISO MDY.
>
> When I try to create a table with a default date of 0000-00-00, psql says
> that this is an invalid date. Why, and can (or how can I) get it to
> accept 0000-00-00 as a valid date?
You can't. There's no year 0. As the docs say, if you don't like
that, please complain to the Vatican; we can't help you.
If you are trying to say, "Date unknown," you can use NULL (and maybe
add another field to indicate whether the NULL means "no date" or
"date unknown").
Note that your datestyle has nothing to do with this: Postgres won't
let you put an invalid date into a date field. This also has the
happy consequence that you can't accidentally create leap years where
there aren't any:
andrewtest=# SELECT '2005-02-29'::date;
ERROR: date/time field value out of range: "2005-02-29"
If you really think you have to have such dates, you are probably
mistaken. If you have to have them because some application you
can't control relies on them, you could store them in a text column,
and coerce them to dates when you select (but be prepared for the
errors you'll get). But my suggestion is that if you really think
you have to have such dates, you should redesign (which might mean
"replace") your application. Anything that uses dates with a year 0
is so fundamentally mistaken about how dates work that I wouldn't
trust it.
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: DATESTYLE and 0000-00-00
In-Reply-To: <20051006181512.GG28948@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox