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@xxxxxxxxxx
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
