db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Dudziak <tom...@gmail.com>
Subject Re: Two errors with create/alter database SQL in PostgreSQL 8.1
Date Tue, 14 Feb 2006 20:36:54 GMT
On 2/14/06, Vignesh Swaminathan <vswamina@cordys.com> wrote:

> My scenario is to take a db model (initially generated out of a MySQL
> 5.1 db) and use it to create/alter databases in PostgreSQL 8.1 and MySQL
> 5.1. I have attached the db model. During the test there were two errors
> that came up,
>
> First,
> Generated db model contained default value (removed in the attached
> file) for field of type timestamp. This default value is not valid for
> PostgreSQL 8.1 and throws an SQL error. See detail below,
>
> Database XML output using DatabaseIO class creates default value
> attribute as part of the output XML. The default value for TIMESTAMP
> data type is default="0000-00-00 00:00:00". This value is accepted by
> MySQL 5.1 but rejected by PostgreSQL 8.1 with following error statement,
>
> CREATE TABLE "cireport"
> (
>     "id" INTEGER DEFAULT 0 NOT NULL,
>     "startdate" TIMESTAMP DEFAULT '0000-00-00 00:00:00',
>     "enddate" TIMESTAMP DEFAULT '0000-00-00 00:00:00',
>     "employee" INTEGER DEFAULT 0,
>     "lead" INTEGER DEFAULT 0,
>     "rating" INTEGER DEFAULT 0,
>     "type" VARCHAR(50),
>     PRIMARY KEY ("id")
> ) failed with ERROR: date/time field value out of range: "0000-00-00
> 00:00:00"
> Feb 13, 2006 9:49:59 PM org.apache.ddlutils.platform.PlatformImplBase
> evaluateBatch
>
> The SQL fires well when the generated default values are removed from
> the input model file.

>From what I could gather from the documentation of PostgreSQL and
MySQL this is not a valid value for either of the two databases (in
fact, it is not valid in the ISO date specification). The problem is
that the values for month and day start at 1, not a 0.
E.g. see here:

http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://www.postgresql.org/docs/8.1/interactive/datatype-datetime.html

The question now is: how is the column defined in the MySql database ?
Could you provide the SQL for the table definition ?

> Second,
>
> When alter table is used with alter column switched on, a series of
> syntax error messages pop from PostgreSQL 8.1. However the alter is
> executed fine as the continue on error flag was on. The details are,

Please post the stacktraces.

Tom

Mime
View raw message