Re: Inconsistent results from HEX values in predicates

Actually I find this returns my row:

select  col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' or col1=E'\x20';

Why does x'202020' match an equality condition on E'\x20' ?

Is there some auto trimming going on?

This is PostgreSQL 9.3.5 on Linux

On 14 November 2014 11:41, Gary Cowell <gary.cowell+pgsql@gmail.com> wrote:
> 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!