Re: Case sensitivity and migration

On Thu, Mar 20, 2003 at 10:18:26 +0000,
Martin Foster <martin(at)ethereal-realms(dot)org> wrote:
>
> 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')

It would be more like:
SELECT * FROM User WHERE lower(UserName) = lower('Mike')
You can create a function index on lower(UserName) that would be usable
by such queries.

Another option would be to use case insensitive reqgular expression matches.
However, those normally won't be indexable.

> Any good functions which make use of Unix time?

You are probably best reading the documentation on time and date functions.
Your question isn't specific enough to tell you if the functions you want
are built in.

> 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).

You probably don't want to do things that way. The serial type has a default
that does a nextval. So if you don't specify a value for that column or
use the special value DEFAULT on inserts the right thing will happen.