Two errors with create/alter database SQL in PostgreSQL 8.1

[Vignesh Swaminathan <vs...@cordys.com>]

Hi,

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.

========================================================================
======================
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,

Generated alter database SQL contains statement of type,

ALTER TABLE "cirdetail"
	MODIFY "id" INTEGER DEFAULT 0 NOT NULL;

This fires well in MySQL 5.1 but fails for PostgreSQL 8.1 for syntax.
PostgreSQL understands

ALTER TABLE "cirdetail"
	ALTER "id" TYPE INTEGER 

ALTER TABLE "cirdetail"
	ALTER "id" SET NOT NULL

I am not sure if they work all in one statement (could also not find the
syntax to set default value)

Regards
Vignesh Swaminathan

***************************************************************************************************
The information in this message is confidential and may be legally  privileged. 
It is intended solely for the addressee. Access to this message by anyone else is 
unauthorized. If you are not the intended recipient, any disclosure, copying, or 
distribution of the message, or any action or omission taken by you in reliance 
on it is prohibited and may be unlawful. Please immediately contact the sender if 
you have received this message in error. This email does not constitute any 
commitment  from Cordys Holding BV or any of its subsidiaries except when 
expressly agreed in a written agreement between the intended recipient and 
Cordys Holding BV or its subsidiaries.
 ***************************************************************************************************

Re: Two errors with create/alter database SQL in PostgreSQL 8.1

[Thomas Dudziak <to...@gmail.com>]

On 2/14/06, Vignesh Swaminathan <vs...@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