Re: TR: Like and =

Hi,
Thanks for your help. In fact that means 2 solutions for this:

1) select * from pdi where rtrim(pdi) = '1006666058' 

or

2) Use VARCHAR instead of CHAR

I don't which is the best , but both are working.

Nicolas.


> -----Message d'origine-----
> De : Randall Lucas [mailto:rlucas@tercent.net]
> Envoye : lundi 23 juin 2003 18:54
> A : Nicolas JOUANIN
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] 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
> >
>