Skip site navigation (1) Skip section navigation (2)

char columns, space padding, and the "like" operator

From: "Haszlakiewicz, Eric" <EHASZLA(at)transunion(dot)com>
To: <pgsql-interfaces(at)postgresql(dot)org>
Subject: char columns, space padding, and the "like" operator
Date: 2009-02-02 21:00:33
Message-ID: 9D29FD18CBD74A478CBA86E6EF6DBAD403394D9A@CHI4EVS04.corp.transunion.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-interfaces by date

Next:From: Tom LaneDate: 2009-02-02 21:38:55
Subject: Re: char columns, space padding, and the "like" operator
Previous:From: Oliveiros CristinaDate: 2009-02-02 18:56:19
Subject: How to pass NULL on a NpgsqlParameter

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group