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