Re: select statement fails

On Thu, Apr 10, 2008 at 11:13:33AM +0300, Andrus wrote:
> 1. SQL assumes that CHARACTER(n) column is always padded with spaces in
> right.
> So casting to text should preserve spaces.
> Why PostgreSQL cast to text violates SQL ?

It says it is padded with spaces, but it also says that these spaces
are insignificant and should be ignored in certain contexts. This area
of the spec is poorly worked out, see

http://archives.postgresql.org/pgsql-sql/2004-02/msg00229.php

for some examples of where the behaviour you want doesn't work.

> 2.
>
> create table test ( tc char(1) );
> create index tc on test(tc);
> select * from test where tc='x'::text;
>
> I'm afraid that if test table has large number of rows, PostgreSQL is not
> capable to use index for this query doe to the cast to text.
> Is it so ?

PostgreSQL does have the concept of cross-type index operators, so the
above may work in recent versions. On the other hand, you could just
drop the cast and it will always work.

Seems odd you add a cast explicitly to a type different from the column
you are comparing to. It's just asking for trouble.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.