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(at)tercent(dot)net]
> Envoye : lundi 23 juin 2003 18:54
> A : Nicolas JOUANIN
> Cc : pgsql-sql(at)postgresql(dot)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(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
> >
>