pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCase Insensitive comparison
9+ messages / 6 participants
[nested] [flat]
* Case Insensitive comparison
@ 2003-09-25 03:35 George A.J <jinujosein@yahoo.com>
0 siblings, 3 replies; 9+ messages in thread
From: George A.J @ 2003-09-25 03:35 UTC (permalink / raw)
To: pgsql-sql
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 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
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().
jinujose
---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-25 04:45 Josh Berkus <josh@agliodbs.com>
parent: George A.J <jinujosein@yahoo.com>
2 siblings, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2003-09-25 04:45 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-25 05:19 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: George A.J <jinujosein@yahoo.com>
2 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2003-09-25 05:19 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-25 05:30 Roberto Mello <rmello@cc.usu.edu>
parent: George A.J <jinujosein@yahoo.com>
2 siblings, 2 replies; 9+ messages in thread
From: Roberto Mello @ 2003-09-25 05:30 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; +Cc: pgsql-sql
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 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
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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-25 05:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-09-25 05:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: George A.J <jinujosein@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-25 13:20 Bruno Wolff III <bruno@wolff.to>
parent: Roberto Mello <rmello@cc.usu.edu>
1 sibling, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2003-09-25 13:20 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: George A.J <jinujosein@yahoo.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-25 14:04 George A.J <jinujosein@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: George A.J @ 2003-09-25 14:04 UTC (permalink / raw)
To: pgsql-sql
Thanks to all of you for your valuable suggesstions....
does postgresql internally uses the = operator(text,text) for any other purposes.
i think that overloading it solves the index problem too...
Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-26 03:46 Josh Berkus <josh@agliodbs.com>
parent: Roberto Mello <rmello@cc.usu.edu>
1 sibling, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2003-09-26 03:46 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; George A.J <jinujosein@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Insensitive comparison
@ 2003-09-26 17:39 Roberto Mello <rmello@cc.usu.edu>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Roberto Mello @ 2003-09-26 17:39 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: George A.J <jinujosein@yahoo.com>; pgsql-sql
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/ +
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-09-26 17:39 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-25 03:35 Case Insensitive comparison George A.J <jinujosein@yahoo.com>
2003-09-25 04:45 ` Josh Berkus <josh@agliodbs.com>
2003-09-25 05:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-25 14:04 ` George A.J <jinujosein@yahoo.com>
2003-09-25 05:19 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-25 05:30 ` Roberto Mello <rmello@cc.usu.edu>
2003-09-25 13:20 ` Bruno Wolff III <bruno@wolff.to>
2003-09-26 03:46 ` Josh Berkus <josh@agliodbs.com>
2003-09-26 17:39 ` Roberto Mello <rmello@cc.usu.edu>
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