pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedchar columns, space padding, and the "like" operator
4+ messages / 2 participants
[nested] [flat]
* char columns, space padding, and the "like" operator
@ 2009-02-02 21:00 Haszlakiewicz, Eric <EHASZLA@transunion.com>
0 siblings, 1 reply; 4+ messages in thread
From: Haszlakiewicz, Eric @ 2009-02-02 21:00 UTC (permalink / raw)
To: pgsql-interfaces
I recently got very confused by the operation of the like operator and
it's interaction with "char" type columns. i.e.:
create table foo ( col1 char(10) );
insert into foo values ('SOMEVALUE');
select * from foo where col1 like 'SOME% %';
-- The above returns the column
select 'SOMEVALUE' like 'SOME% %';
-- but this returns false
Once I realized that the value in the table actually got extended to
'SOMEVALUE ', things started making sense, since the equivalent quick
select is actually:
select 'SOMEVALUE ' like 'SOME% %';
Unfortunately, my app has a whole bunch of places where it uses
constructs like this against char columns. Other databases (such as
Informix), automatically strip spaces off char column so queries like
the above behave in a more intuitive fashion. That causes it's own
problems, so I'm not suggesting adding feature to postgres, but I was
wondering if it already exists, and if so how do I turn it on?
eric
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: char columns, space padding, and the "like" operator
@ 2009-02-02 21:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Haszlakiewicz, Eric <EHASZLA@transunion.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2009-02-02 21:38 UTC (permalink / raw)
To: Haszlakiewicz, Eric <EHASZLA@transunion.com>; +Cc: pgsql-interfaces
"Haszlakiewicz, Eric" <EHASZLA@transunion.com> writes:
> Once I realized that the value in the table actually got extended to
> 'SOMEVALUE ', things started making sense, since the equivalent quick
> select is actually:
> select 'SOMEVALUE ' like 'SOME% %';
> Unfortunately, my app has a whole bunch of places where it uses
> constructs like this against char columns. Other databases (such as
> Informix), automatically strip spaces off char column so queries like
> the above behave in a more intuitive fashion.
Cast the char(n) column to text or varchar, and it should work more
like you're expecting.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: char columns, space padding, and the "like" operator
@ 2009-02-02 22:07 Haszlakiewicz, Eric <EHASZLA@transunion.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Haszlakiewicz, Eric @ 2009-02-02 22:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
>-----Original Message-----
>From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
>"Haszlakiewicz, Eric" <EHASZLA@transunion.com> writes:
>> Once I realized that the value in the table actually got extended to
>> 'SOMEVALUE ', things started making sense, since the equivalent quick
>> select is actually:
>> select 'SOMEVALUE ' like 'SOME% %';
>
>> Unfortunately, my app has a whole bunch of places where it uses
>> constructs like this against char columns. Other databases (such as
>> Informix), automatically strip spaces off char column so queries like
>> the above behave in a more intuitive fashion.
>
>Cast the char(n) column to text or varchar, and it should work more
>like you're expecting.
Yeah, I figured that much. I was hoping for a connection-wide or
database-wide setting, so I wouldn't have to go change all my SQL
statements.
eric
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: char columns, space padding, and the "like" operator
@ 2009-02-03 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Haszlakiewicz, Eric <EHASZLA@transunion.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2009-02-03 00:43 UTC (permalink / raw)
To: Haszlakiewicz, Eric <EHASZLA@transunion.com>; +Cc: pgsql-interfaces
"Haszlakiewicz, Eric" <EHASZLA@transunion.com> writes:
>> Cast the char(n) column to text or varchar, and it should work more
>> like you're expecting.
> Yeah, I figured that much. I was hoping for a connection-wide or
> database-wide setting, so I wouldn't have to go change all my SQL
> statements.
Well, you could experiment with removing the char(n) variant of the ~~
operator, but if it breaks you get to keep both pieces.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-02-03 00:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-02-02 21:00 char columns, space padding, and the "like" operator Haszlakiewicz, Eric <EHASZLA@transunion.com>
2009-02-02 21:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-02-02 22:07 ` Haszlakiewicz, Eric <EHASZLA@transunion.com>
2009-02-03 00:43 ` Tom Lane <tgl@sss.pgh.pa.us>
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