Thread: Mysql to postgres

I have a legacy system that has been developed with a mysql backend. I'm in the process of converting it over to postgres. As postgres is case sensitive it appears as I will have to make numerous code changes from "like" to "ilike" and from e.g. "cola = cola" to  "lower(cola) = lower(cola)" and also changing all the indexes to lowercase as well. This is not as simple as it appears as the sql is dynamically generated in the code base, so I can see this as a very lengthy transition.

I have considered perhaps it might be easier to take a step back from the code and look at changing postgres.

There may be a way to change the database in postgres to be case insensitive. i.e. change some of the operations such as "text=text" to be case insensitive. Does this sound Insane? I have looking further into this and have found the  function that does the text matching:

CREATE OR REPLACE FUNCTION texteq(text, text)
  RETURNS bool AS
'texteq'
  LANGUAGE 'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION texteq(text, text) TO public;
COMMENT ON FUNCTION texteq(text, text) IS 'equal';

a. I was wondering if it was at all possible to change the case of the two text inputs to lower case. This will correct all text=text matches.

b. Then, substitute the like with ilike function, so they are the same.
C. By doing this I should not have to change any code at all, I guess I will still have to update the indexes to lower case, however that is a quick task.

Is this at all possible? And I dicing with danger here? Please enlighten me?

Theo

Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> There may be a way to change the database in postgres to be case
> insensitive. i.e. change some of the operations such as "text=text" to be
> case insensitive. Does this sound Insane?

I think it would be exceedingly unwise to muck with the standard
behavior of a commonly used datatype.  What some other people have
done is to invent a variant datatype "citext" that shares most of the
same operations as "text", but has case-insensitive comparisons.
Then you only need to hack your table definitions, not your application
code.

http://gborg.postgresql.org/project/citext/projdisplay.php

I think you could probably also do it by building a locale whose
comparisons are case-insensitive, but I don't know enough about
locales to be sure.

            regards, tom lane

On Mon, Apr 11, 2005 at 03:02:01AM -0400, Tom Lane wrote:
> I think you could probably also do it by building a locale whose
> comparisons are case-insensitive, but I don't know enough about
> locales to be sure.

FWIW, this is how old versions of Sybase handled case insensitivity.
Pretty much every other database I've worked on though, you want to go
the lower()/lcase() route.

citext is interesting; I'll have to look into that for
stats.distributed.net. Another example of how incredibly extensible
PostgreSQL is.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Thankyou all for your feedback. Unfortunately, I think I may have to go down the lower() road of changing the code.

It will be far more time consuming, however there are other columns that are char,varchar,text etc.

Will definitely keep the citext contribution in mind for another time.

Theo

-----Original Message-----
From: Jim C. Nasby [mailto:decibel@decibel.org]
Sent: Tuesday, 12 April 2005 4:57 AM
To: Tom Lane
Cc: Theo Galanakis; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Mysql to postgres

On Mon, Apr 11, 2005 at 03:02:01AM -0400, Tom Lane wrote:
> I think you could probably also do it by building a locale whose
> comparisons are case-insensitive, but I don't know enough about
> locales to be sure.

FWIW, this is how old versions of Sybase handled case insensitivity. Pretty much every other database I've worked on though, you want to go the lower()/lcase() route.

citext is interesting; I'll have to look into that for stats.distributed.net. Another example of how incredibly extensible PostgreSQL is.

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"