Re: jdbc and automagic casting

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

In response to

Responses

Browse pgsql-jdbc by date

  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