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(dot)cowell+pgsql(at)gmail(dot)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!