Thread: Case Insensitive comparison

jinujose,

> i am using postgresql 7.3.x. I am converting a database in MS SQL server to
> PostgreSQL.

Good luck to you!

> The main problems i am facing is that in sql server the text comparisons
> are case insensitive. how can i compare text case insensitive in postgresql
> without using an upper() or lower() function in both sides (=). Is there
> any option to set in postgresql? Is there any problem in overriding the =
> operator that compare text. ie droping the current operator = and creating
> a new = operator(text,text). Does the existing = operator is using
> internally by postgres for some porpose. please help

I would *not* suggest modifying such a fundamental operator; you are very 
likely to break something.

You can use "ILIKE" to compare text:
'Joe' ILIKE 'joE' == true

You can also use the "regex" operator:
'Joe' ~* '^jOE$' == TRUE
... but that takes more adaptation.

However, neither of the above approaches can be indexed.

> Another problem is in creating function...
> How can i create a function that accept and return any type. the type "any"
> is not allowing as parameter or return type. Is it possible? i want to
> create a function similar to NULLIF().

You can't, nor will you be able to -- in te future, some 7.4 functions will be 
able to *accept* any type, but they will still return a specific type.  
Instead, you need to create a seperate NULLIF() for each data type you're 
likely to use.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

On Wed, 24 Sep 2003, George A.J wrote:

> The main problems i am facing is that in sql server the text comparisons
> are case insensitive. how can i compare text case insensitive in
> postgresql without using an upper() or lower() function in both sides
> (=). Is there any option to set in postgresql?
>
> Is there any problem in overriding the = operator that compare text. ie
> droping the current operator = and creating a new = operator(text,text).
> Does the existing = operator is using internally by postgres for some
> porpose. please help

Well, you'd really want to change all the comparison operators because
otherwise I think indexes would be screwy and I don't know off hand if
this will cause problems, but I can imagine that it might, if only on
queries from clients and such on the system catalogs.

One other option is to find or make a case insensitive collating locale
for your operating system and use that when initializing the database.
I've never tried so I can't say whether that'll have its own problems.

On Wed, Sep 24, 2003 at 08:35:50PM -0700, George A.J wrote:
> hai
>  
> i am using postgresql 7.3.x. I am converting a database in MS SQL server to PostgreSQL. 
>  
> The main problems i am facing is that in sql server the text comparisons are case insensitive. how can i compare text
caseinsensitive in postgresql without using an upper() or lower() function in both sides (=). Is there any option to
setin postgresql?
 
> Is there any problem in overriding the = operator that compare text. ie droping the current operator = and creating a
new= operator(text,text). Does the existing = operator is using internally by postgres for some porpose. please help
 

Souinds like MS-SQL has broken behavior.

Use ILIKE, but I can't remember if you lose indice seaches with that.
Overriding the operator should be ok, but again I don't know what the
impact on index usage would be.

> Another problem is in creating function...
> How can i create a function that accept and return any type. the type "any" is not allowing as parameter or return
type.Is it possible? i want to create a function similar to NULLIF().
 

Use the standard coalesce().

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
MONEY TALKS ...   but all mine ever says is GOODBYE!

Josh Berkus <josh@agliodbs.com> writes:
>> How can i create a function that accept and return any type. the type "any"
>> is not allowing as parameter or return type. Is it possible? i want to
>> create a function similar to NULLIF().

> You can't, nor will you be able to -- in te future, some 7.4 functions will be 
> able to *accept* any type, but they will still return a specific type.

Au contraire.  The 7.4 polymorphic-function features may well solve
jinujose's problem, if he can indeed write a type-independent function
body.  For example

create function nullif(anyelement, anyelement) returns anyelement as
'select case when $1 = $2 then null else $1 end' language sql;

I'm not sure we're all the way there yet, cf
http://archives.postgresql.org/pgsql-general/2003-09/msg00500.php
        regards, tom lane

On Wed, Sep 24, 2003 at 23:30:08 -0600, Roberto Mello <rmello@cc.usu.edu> wrote:
> On Wed, Sep 24, 2003 at 08:35:50PM -0700, George A.J wrote:
> 
> > Another problem is in creating function...
> > How can i create a function that accept and return any type. the type "any" is not allowing as parameter or return
type.Is it possible? i want to create a function similar to NULLIF().
 
> 
> Use the standard coalesce().

coalesce is used to do the inverse of nullif. Postgres also has a nullif
function.

I suspect that he relly doesn't want to use either of those, but instead
wants to have some function that returns the same type as its argument
without having to write one function for each different type. There is
something along those lines coming in 7.4.

Josh Berkus writes:
>> How can i create a function that accept and return any type. the type "any"
>> is not allowing as parameter or return type. Is it possible? i want to
>> create a function similar to NULLIF().

> You can't, nor will you be able to -- in te future, some 7.4 functions will be
> able to *accept* any type, but they will still return a specific type.

Au contraire. The 7.4 polymorphic-function features may well solve
jinujose's problem, if he can indeed write a type-independent function
body. For example

create function nullif(anyelement, anyelement) returns anyelement as
'select case when $1 = $2 then null else $1 end' language sql;

I'm not sure we're all the way there yet, cf
http://archives.postgresql.org/pgsql-general/2003-09/msg00500.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Roberto,

> > Another problem is in creating function...
> > How can i create a function that accept and return any type. the type
> > "any" is not allowing as parameter or return type. Is it possible? i want
> > to create a function similar to NULLIF().
>
> Use the standard coalesce().

NULLIF is the converse of COALESCE().

Any idea when you're going to overhaul the CookBook?   

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, Sep 25, 2003 at 08:46:39PM -0700, Josh Berkus wrote:
> 
> NULLIF is the converse of COALESCE().

Oh, ooops! My apologies.
> Any idea when you're going to overhaul the CookBook?   

*sighs*

The software is pretty much ready. I'll have time to install and configure
it next week, after my exams.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +