Re: TR: Like and =

Hi Nicholas,

CHAR fields, as opposed to VARCHAR, are blank-padded to the set length.

Therefore, when you inserted a < 25 character string, it got padded
with spaces until the end.

Likewise, when you cast '1006666058' to a CHAR(25) in the = below, it
gets padded, so it matches.

The LIKE operator takes a pattern, and since your pattern did not
specify a wildcard at the end, it didn't exactly match the padded
string.

This behavior does seem kind of confusing; in any case, it probably
argues for using varchar.

Best,

Randall

On Monday, June 23, 2003, at 12:29 PM, Nicolas JOUANIN wrote:

>
> Hi,
>
> I've got a table , pdi, with a field pro_id defined as char(25). One
> fied
> og this table contains the string '1006666058' plus spaces to fill the
> 25
> length (ie pro_id = '1006666058 ').
> When I run:
> select * from pdi where pdi = '1006666058' the row is returned.
> When I run:
> select * from pdi where pdi like '1006666058' the row is NOT
> returned.
>
> select length(pro_id) where pdi = '1006666058' returns:
> length
> -----------
> 25
>
> 2 Row(s) affected
>
> 1) In PostgreSQL documentation, it's said that without % wildcards like
> operates the same as = , it seems not.
> 2) Why does the = operator return the row ? it shouldn't because of the
> trailing spaces.
> 3) The row was inserted from the COPY command:
> COPY pdi FROM STDIN NULL as '' DELIMITER as '|';
> VOL|1006666058|0|PART||PART
> \.
> Why does my field contain trailing spaces ?
>
> Regards and thanks again for your useful help.
>
>
> PS:
> create table pdi
> (
> pmf_id char(4) not null ,
> pro_id char(25) not null ,
> lng_id char(3) not null ,
> pdi_desc char(50) not null ,
> pdi_instr text,
> pdi_matchdesc char(50),
> CONSTRAINT pk_pdi PRIMARY KEY (pro_id,pmf_id,lng_id)
> );
>
> Nicolas.
>
> ---------------------------------------------------------------
> Nicolas JOUANIN - SA REGIE FRANCE
> Village Informatique BP 3002
> 17030 La Rochelle CEDEX
> Tel: 05 46 44 75 76
> Fax: 05 46 45 34 17
> email: n(dot)jouanin(at)regie-france(dot)com
> Web : www.regie-france.com
> ---------------------------------------------------------------
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>