pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Mysql to postgres
4+ messages / 3 participants
[nested] [flat]

* Mysql to postgres
@ 2005-04-11 06:26  Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
  0 siblings, 1 reply; 4+ messages in thread

From: Theo Galanakis @ 2005-04-11 06:26 UTC (permalink / raw)
  To: pgsql-admin


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


______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Mysql to postgres
@ 2005-04-11 07:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2005-04-11 07:02 UTC (permalink / raw)
  To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Mysql to postgres
@ 2005-04-11 18:56  Jim C. Nasby <decibel@decibel.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Jim C. Nasby @ 2005-04-11 18:56 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-admin

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?"



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Mysql to postgres
@ 2005-04-11 23:53  Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
  0 siblings, 0 replies; 4+ messages in thread

From: Theo Galanakis @ 2005-04-11 23:53 UTC (permalink / raw)
  To: 'Jim C. Nasby' <decibel@decibel.org>; +Cc: pgsql-admin


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?"


______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-04-11 23:53 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-11 06:26 Mysql to postgres Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-04-11 07:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-11 18:56   ` Jim C. Nasby <decibel@decibel.org>
2005-04-11 23:53 Re: Mysql to postgres Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>

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