Обсуждение: 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 ---------------------------------------------------------------
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 >
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
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 > > >
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
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