Date: Thu, 6 Oct 2005 14:15:12 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: DATESTYLE and 0000-00-00
Message-ID: <20051006181512.GG28948@phlogiston.dyndns.org>
References: <Pine.LNX.4.63.0510060900120.11396@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.LNX.4.63.0510060900120.11396@localhost.localdomain>
User-Agent: Mutt/1.5.9i

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