Re: Inconsistent results from HEX values in predicates

Gary Cowell <gary(dot)cowell+pgsql(at)gmail(dot)com> writes:
> 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?

In a char(N) column, certainly. Read the manual.

You could dodge that by using varchar or text instead. But you may have
another problem besides trailing blanks not being significant: you're
assuming that the sort order of text is pure bytewise, which would only
be true in C locale. So this coding technique is fragile as can be
and I don't recommend it.

regards, tom lane