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.jouanin@regie-france.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
>