db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew ``Bass'' Shcheglov <andrewb...@gmail.com>
Subject Trailing space in VARCHARs
Date Sun, 05 Aug 2007 15:47:34 GMT

The issue has been partially discussed over the Internet,
but I haven't found full clarification yet.

I'm using Derby, JDK 1.6.0_01 on a Linux 2.6 box.

If I have a table with a VARCHAR field
with a UNIQUE constraint placed on that field,
I can't insert two records that differ only in trailing space,
say 'foo' and 'foo '.

Moreover, if I drop the constraint and insert those
two different records, the Derby engine treats them as equal,
meaning both

SELECT * from <...> WHERE <...> = 'foo'


SELECT * from <...> WHERE <...> = 'foo '

return 2 records instead of 1.

In all cases, the trailing space is preserved, however,
so I can distinguish the records once I fetch them via JDBC
(of course, string literals "foo" and "foo " are not equivalent in Java).

I have 2 questions:

1. Is the described above a legitimate behaviour of Derby?

2. Can I somehow turn this behaviour off
(for Derby to behave more like Oracle)?


Andrew ``Bass'' Shcheglov

Andrew ``Bass'' Shcheglov

View raw message