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