db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vignesh Swaminathan" <vswam...@cordys.com>
Subject Two errors with create/alter database SQL in PostgreSQL 8.1
Date Tue, 14 Feb 2006 06:23:35 GMT
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.
 ***************************************************************************************************


Mime
  • Unnamed multipart/mixed (inline, None, 0 bytes)
View raw message