Re: select statement fails

On Apr 10, 2008, at 3:13 AM, Andrus wrote:
> Albe,
>
>>> select * from test where tc=' '::text;
>> Because the arguments to the operator "=" are of different type,
>> implicit type conversion takes place.
>> "character(1)" will by converted to "text", during this conversion
>> trailing blanks will be ignored, as befits the "character(n)" type.
>
> Thank you.
>
> 1. SQL assumes that CHARACTER(n) column is always padded with spaces
> in
> right.

That is only for storage and display.

> So casting to text should preserve spaces.
> Why PostgreSQL cast to text violates SQL ?

It doesn't and it is right there in the manual:

"Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
padding spaces are treated as semantically insignificant. Trailing
spaces are disregarded when comparing two values of type character,
and they will be removed when converting a character value to one of
the other string types. Note that trailing spaces are semantically
significant in character varying and text values."

http://www.postgresql.org/docs/current/interactive/datatype-character.html

> 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 ?

You have two options:

1. Just us text for the column's data type.
2. Create an index on the column cast as text:

CREATE INDEX test_tc_txt_idx ON test (tc::text);

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com