Re: Inconsistent results from HEX values in predicates

Gary Cowell <gary.cowell+pgsql@gmail.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