Thread: TR: Like and =

TR: Like and =

From

"Nicolas JOUANIN"

Date:

23 June 2003, 16:28:36

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

Re: TR: Like and =

From

Randall Lucas

Date:

23 June 2003, 16:53:50

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
>

Re: TR: Like and =

From

Tom Lane

Date:

23 June 2003, 17:35:50

Randall Lucas <rlucas@tercent.net> writes:
> 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; 

Yeah.  As of CVS tip, the system is actually going out of its way to
cause this to happen: if we deleted the separate ~~ operator for bpchar,
then the automatic rtrim() that now happens when converting bpchar to
text would cause the extra spaces to go away, and the LIKE would work
as Nicolas is expecting.  On the other hand, this would probably create
some backwards-compatibility issues, since existing uses of LIKE with
bpchar operands are no doubt using patterns that expect the spaces to be
there.  Any opinions whether we should change it or not?
        regards, tom lane

Re: TR: Like and =

From

"Nicolas JOUANIN"

Date:

24 June 2003, 06:48:14

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

Re: TR: Like and =

From

Dani Oderbolz

Date:

25 June 2003, 12:19:00

Nicolas JOUANIN wrote:

>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.
>  
>

Do you have a specific reason why to use CHAR?
I use CHAR only for certain one-byte flags, and even there its use is 
debatable.
I would use VARCHAR; if I was you.

Cheers,
Dani

Re: TR: Like and =

From

"Nicolas JOUANIN"

Date:

27 June 2003, 06:44:52

In fact I'm trying to migrate a database from Informix IDS to Postgres. This
IDS database uses CHAR so I just let the same.
Now I'm conviced that I sould convert CHAR to VARCHAR.

Thanks.

> -----Message d'origine-----
> De : pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]De la part de Dani Oderbolz
> Envoye : mercredi 25 juin 2003 14:19
> A : pgsql-sql@postgresql.org
> Objet : Re: [SQL] TR: Like and =
>
>
> Nicolas JOUANIN wrote:
>
> >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.
> >
> >
>
> Do you have a specific reason why to use CHAR?
> I use CHAR only for certain one-byte flags, and even there its use is
> debatable.
> I would use VARCHAR; if I was you.
>
> Cheers,
> Dani
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend