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: [EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
