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