You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Vignesh Swaminathan <vs...@cordys.com> on 2006/02/16 11:57:01 UTC
Insert SQL error in PostgreSQL 8.1
Hi
I am trying to insert a row into a simple table like
CREATE TABLE orders
(
id int4 NOT NULL,
customerid int4,
orderdate timestamp,
shipdate timestamp,
CONSTRAINT orders_pkey PRIMARY KEY (id)
)
When I fire an insert using Platform.insert(Database, SQL) I get an
exception message as follows. I tried printing the SQL using
Platform.getInsertSQL which returned
INSERT INTO "orders" ("id", "customerid", "orderdate", "shipdate")
VALUES ('1000', '1000', '2006-03-16 00:00:00', '2006-04-21 00:00:00')
However, when I fire this statement in the PostgreSQL query tool the
insert goes ahead without problem. Please help.
The exception stack is
Exception in thread "main" org.apache.ddlutils.DynaSqlException: Error
while inserting into the database
at
org.apache.ddlutils.platform.PlatformImplBase.insert(PlatformImplBase.ja
va:1064)
at
org.apache.ddlutils.platform.PlatformImplBase.insert(PlatformImplBase.ja
va:1140)
at TestData.insertData(TestData.java:58)
at TestData.main(TestData.java:93)
Caused by: org.postgresql.util.PSQLException: ERROR: column "orderdate"
is of type timestamp without time zone but expression is of type
character varying
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu
torImpl.java:1512)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp
l.java:1297)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:430)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
c2Statement.java:346)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2S
tatement.java:300)
at
org.apache.ddlutils.platform.PlatformImplBase.insert(PlatformImplBase.ja
va:1053)
... 3 more
<vignesh/>
"Winning solutions find the right balance between market needs and
technical needs"
***************************************************************************************************
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: Insert SQL error in PostgreSQL 8.1
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/16/06, Vignesh Swaminathan <vs...@cordys.com> wrote:
> I am trying to insert a row into a simple table like
>
> CREATE TABLE orders
> (
> id int4 NOT NULL,
> customerid int4,
> orderdate timestamp,
> shipdate timestamp,
> CONSTRAINT orders_pkey PRIMARY KEY (id)
> )
>
> When I fire an insert using Platform.insert(Database, SQL) I get an
> exception message as follows. I tried printing the SQL using
> Platform.getInsertSQL which returned
>
> INSERT INTO "orders" ("id", "customerid", "orderdate", "shipdate")
> VALUES ('1000', '1000', '2006-03-16 00:00:00', '2006-04-21 00:00:00')
>
> However, when I fire this statement in the PostgreSQL query tool the
> insert goes ahead without problem. Please help.
>
<snip>
> Caused by: org.postgresql.util.PSQLException: ERROR: column "orderdate"
> is of type timestamp without time zone but expression is of type
> character varying
Looks like your database model has VARCHAR for the orderdate (and
possibly shipdate) column, and PostgreSql has no implicit default
conversion from string to timestamp. Is the model read from the
database or from an XML file ?
Tom