Re: Problem with fixed length fields.

On Sat, 23 Oct 2004, Dave Smith wrote:

> Platform:
> Postgres 7.4.5, lastest JDBC driver from CVS.
>
> Table:
> create table t(a char(2),b char(2));
> insert into t values ('  ','  ');
>
> Jdbc:
>
> This query finds nothing
> PreparedStatement st = db.prepareStatement("select * from t where a=?
> and b=?");
> st.setString(1,"  ");
> st.setString(2,"  ");
> ResultSet rs = st.executeQuery();
>
> This query works
> rs = db.prepareStatement("select * from t where a='  ' and b='
> '").executeQuery();
>

This is a problem with the driver because it is typing the '  ' values as
text, not char, so the comparison doesn't work right because these two
types handle trailing spaces differently.  Notice:

jurka=# select length(' '::char);
 length
--------
      0
(1 row)

jurka=# select length(' '::text);
 length
--------
      1
(1 row)

At the moment setObject(i, str, Types.CHAR) doesn't work, but we could
make that happen, but it just doesn't seem like a good workaround.
Alternatively you could use ?::char in your query or str.trim(), but
that's certainly not intuitive or user friendly.  We've got to come up
with something better than this.


Oliver, any ideas?

Kris Jurka