Hello
I have a table, and I want to always supply predicates in a BETWEEN format.
If all data is to be returned, then the FROM and TO values of the
BETWEEN are set to low hex value, and high hex value.
Sometimes this is not returning rows, here's an example:
e5iso=# create table t1 (col1 character(3));
CREATE TABLE
e5iso=# insert into t1 values('AAA');
INSERT 0 1
e5iso=# insert into t1 values('000');
INSERT 0 1
e5iso=# insert into t1 values(' ');
INSERT 0 1
e5iso=# insert into t1 values(' x ');
INSERT 0 1
e5iso=# select col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' ;
col1
------
AAA
000
x
(3 rows)
So where did my ' ' row go?
Am I missing something, are SPACES special in some way?
e5iso=# select * from t1 where col1 is null;
col1
------
(0 rows)
All my rows hex values:
e5iso=# select encode(col1::bytea,'hex') from t1;
encode
--------
414141
303030
202020
207820
The database is a LATIN9 database.
Help please!