Thread: case-(in)sensitive Server

Hi all,

we are here using PostgreSQl 7.1.3 on SUSe 6.4 with Windows 2000 Clients via
ODBC. When I now try to search with OpenOffice 1.0 (from the Client) for
"Test" and its written "test" I cant't found it.
I know that I can search case insensitive in the SQL-Prompt (found serveral
examples).

But my Problem is: The people from the sales dept. use OpenOffice and they
use the integrated Search.

My Question is: Can I make the Postgres Server case-insensitive? - And if
yes, how?


Thanks for help

Daniel

--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net

Daniel,

> we are here using PostgreSQl 7.1.3 on SUSe 6.4 with Windows 2000
> Clients via
> ODBC. When I now try to search with OpenOffice 1.0 (from the Client)
> for
> "Test" and its written "test" I cant't found it.
> I know that I can search case insensitive in the SQL-Prompt (found
> serveral
> examples).

Well, you've come to the right place.  I'm on both projects ...
PostgreSQL and OpenOffice.org.

First off, you're in desperate need of an upgrade, both for the SuSE OS
and (somewhat less so) for PostgreSQL.   This is not, however, related
to your problem.

> But my Problem is: The people from the sales dept. use OpenOffice and
> they
> use the integrated Search.
>
> My Question is: Can I make the Postgres Server case-insensitive? -
> And if
> yes, how?

No, you cannot change the server.  PostgreSQL is UNIX-based, and UNIX
is a case-sensitive system. And, as you are using the OpenOffice.org
built-in search tool, you do not have the opportunity to replace "="
with "ILIKE".

Jan, Tom, is there any way that he could do this with RULEs?  It seems
like there should be some way, but I can't figure it out.

As I am on the dba.OpenOffice.org project, I would also be interested
in having your input for the DBA project.   We are currently
overhauling database access in OpenOffice.org 1.0, and would dearly
appreciate the input of someone, like you, who is using it in a
production environment.

-Josh Berkus
 agliodbs@openoffice.org

"Josh Berkus" <josh@agliodbs.com> writes:
> Jan, Tom, is there any way that he could do this with RULEs?  It seems
> like there should be some way, but I can't figure it out.

Do *what*, exactly?  It wasn't at all clear to me what SQL operation is
being performed here.

            regards, tom lane

Tom,

> > Jan, Tom, is there any way that he could do this with RULEs?  It seems
> > like there should be some way, but I can't figure it out.
>
> Do *what*, exactly?  It wasn't at all clear to me what SQL operation is
> being performed here.

He wants
SELECT * FROM table_a WHERE code = 'volume';
to be automagically replaced with:
SELECT * FROM table_a WHRE code ILIKE 'volume';

to build-in case insensitivity.

--
-Josh Berkus

Josh Berkus <josh@agliodbs.com> writes:
> He wants
> SELECT * FROM table_a WHERE code = 'volume';
> to be automagically replaced with:
> SELECT * FROM table_a WHRE code ILIKE 'volume';

I don't see any way we could do that at the server level without causing
*all* text = operations to work that way; which almost inevitably would
break something.

Given that the client-side code is open source, I would think that a
client-side solution would be no harder to make happen, and it'd surely
be a lot cleaner.

            regards, tom lane

Tom,

> I don't see any way we could do that at the server level without causing
> *all* text = operations to work that way; which almost inevitably would
> break something.
>
> Given that the client-side code is open source, I would think that a
> client-side solution would be no harder to make happen, and it'd surely
> be a lot cleaner.

We're working on it (I'm on the dba.openoffice.org project).  I was just
hoping for an immediate solution for this guy.

--
-Josh Berkus