From: | Guillaume <lomig42(at)gmail(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: jdbc and automagic casting |
Date: | 2011-09-20 09:18:40 |
Message-ID: | 8e445805-50fd-40ea-b83e-59b1e66b1b97@s20g2000yqh.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Hi,
Thanks to both of you, it helped me find a solution out of this.
I ran a few tests. Basically this INSERT:
INSERT INTO ip_list VALUES ('127.0.0.1')
in 4 different cases, in a small standalone java snippet to understand
what's going on:
- prepared statement (with setString) and default stringtype
- prepared statement (with setString) and stringtype=unspecified
- dynamic sql and default stringtype
- dynamic and stringtype=unspecified
Out of those, both prepared statements failed, but both dynamic sql
worked as expected.
It so happens that setString() in a prepared statement sends a varchar
to postgres, and postgres has no way to convert a varchar to an inet
type (8.4).
This can be confirmed by this in psql:
INSERT INTO dsl.ip_list VALUES (CAST('127.0.0.1' AS CHARACTER
VARYING));
ERROR: column "ip" is of type inet but expression is of type
character varying
LINE 1: ....ip_list VALUES (CAST('127....
I am not sure of the internal conversion done in the usual case INSERT
INTO dsl.ip_list VALUES ('127.0.0.1' );
Anyway, to get out of this, I 'just' had to create a new CAST:
CREATE CAST (CHARACTER VARYING AS inet) WITH INOUT AS ASSIGNMENT;
Now varchars are properly converted to inet on the postgres side, so
it all works for me.
I find it a bit surprising that postgres does not know how to convert
from varchar to inet implicitly (although the inet() operator does
exist), but there is at least a solution.
Thanks for your help,
Guillaume
From | Date | Subject | |
---|---|---|---|
Next Message | Radosław Smogura | 2011-09-20 09:36:01 | Re: behavior at the end of a transaction |
Previous Message | Craig Ringer | 2011-09-20 05:00:53 | Re: binary patch problems |