pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTR: Like and =
6+ messages / 4 participants
[nested] [flat]
* TR: Like and =
@ 2003-06-23 16:29 Nicolas JOUANIN <n.jouanin@regie-france.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nicolas JOUANIN @ 2003-06-23 16:29 UTC (permalink / raw)
To: pgsql-sql
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
---------------------------------------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Like and =
@ 2003-06-23 16:53 Randall Lucas <rlucas@tercent.net>
parent: Nicolas JOUANIN <n.jouanin@regie-france.com>
0 siblings, 2 replies; 6+ messages in thread
From: Randall Lucas @ 2003-06-23 16:53 UTC (permalink / raw)
To: Nicolas JOUANIN <n.jouanin@regie-france.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Like and =
@ 2003-06-23 17:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Randall Lucas <rlucas@tercent.net>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-06-23 17:35 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: Nicolas JOUANIN <n.jouanin@regie-france.com>; pgsql-sql; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Like and =
@ 2003-06-24 06:49 Nicolas JOUANIN <n.jouanin@regie-france.com>
parent: Randall Lucas <rlucas@tercent.net>
1 sibling, 1 reply; 6+ messages in thread
From: Nicolas JOUANIN @ 2003-06-24 06:49 UTC (permalink / raw)
To: Randall Lucas <rlucas@tercent.net>; +Cc: pgsql-sql
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
> >
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Like and =
@ 2003-06-25 12:18 Dani Oderbolz <oderbolz@ecologic.de>
parent: Nicolas JOUANIN <n.jouanin@regie-france.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dani Oderbolz @ 2003-06-25 12:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TR: Like and =
@ 2003-06-27 06:45 Nicolas JOUANIN <n.jouanin@regie-france.com>
parent: Dani Oderbolz <oderbolz@ecologic.de>
0 siblings, 0 replies; 6+ messages in thread
From: Nicolas JOUANIN @ 2003-06-27 06:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-06-27 06:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-23 16:29 TR: Like and = Nicolas JOUANIN <n.jouanin@regie-france.com>
2003-06-23 16:53 ` Randall Lucas <rlucas@tercent.net>
2003-06-23 17:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-24 06:49 ` Nicolas JOUANIN <n.jouanin@regie-france.com>
2003-06-25 12:18 ` Dani Oderbolz <oderbolz@ecologic.de>
2003-06-27 06:45 ` Nicolas JOUANIN <n.jouanin@regie-france.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox