Case sensitivity and migration

From: Martin Foster <martin(at)ethereal-realms(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Case sensitivity and migration
Date: 2003-03-20 10:18:26
Message-ID: 3E799572.6060609@ethereal-realms.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I currently have an series of scripts designed to work with MySQL as the
primary database back end. Needless to say, that some parts of the
code are hacks due to the inability to use sub-queries, views and/or
even triggers.

There are a few points I need to be sure of before I attempt a migration
from MySQL to PostgreSQL or any other DBMS. The first being case
sensitivity, in information stored within the database tables
themselves. For example:

SELECT * FROM User WHERE UserName='MiKe': Matches Mike, mike, MIKE...

The above is an example on how MySQL would react to such a query, and in
fact the system is very much based on that factor. At one point in
time, I recall trying PostGRE and finding that such queries are in fact
case sensitive meaning that Mike, mike and MIKE are all separate.

Is there a way to change the behavior during the creation of the table
itself or when specific databases are created? Or must one use a
syntax like the following:

SELECT lower(UserName) FROM User WHERE UserName=lower('Mike')

The above just seems to make the SQL more complicated then it could be
in a few cases. Even if one could create a trigger to drop the case on
all user names upon inserts.

Will TEXT accept Unicode?

Any good functions which make use of Unix time?

My last question is related to sequences. For the sake of emulating
the functionality that AUTO_INCREMENT gives MySQl users, I want to
create a trigger that will automatically add in the sequence if the user
opted not to make use of nextval(text) beforehand (sends a NULL).

With that in mind. Will currval(text) will work properly if used later
on outside of the trigger? Or will the trigger basically hide it's use?

Martin Foster
Creator/Designer Ethereal Realms
martin(at)ethereal-realms(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stéphane Pinel 2003-03-20 11:11:20 Handling Blobs with libpq
Previous Message Ferruccio Zamuner 2003-03-20 09:33:05 search_path for schemas