TR: Like and =

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

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

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