Thursday, September 08, 2005

PostgreSQL and datetime

I have just been looking at getting an old copy of Bodington to install. I wasn't even after having a working version, just a complete database schema so that I could see how the schema had changed between versions. I tried installing a WAR on my desktop box but it seemed to be failing when trying to create a table with a column type of datetime. Looking in the PostgreSQL 7.4 documentation for date types I found that datetime was no longer a supported date type (is was in 6.x). However after a bit of Googling I found that you can alias a type by creating a domain, eg:
upgrade=# \h CREATE DOMAIN
Command:     CREATE DOMAIN
Description: define a new domain
CREATE DOMAIN name [AS] data_type
    [ DEFAULT expression ]
    [ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

upgrade=# CREATE DOMAIN datetime as timestamp;
Creating this domain means that the database now thinks a datetime is the same as a timestamp and doesn't complain when it is asked to create a table containing one. Later versions of Bodington have the SQL changed so that it doesn't attempt to create timestamps, but people will probably see this bug if they try a new copy of Bodington with a very old PostgreSQL database.