Skip site navigation (1) Skip section navigation (2)

Re: CREATE TYPE for case insensitive text and varchar

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Daniel Migowski <nur-gueltig-bis-2003-12-01(at)Mig-O(dot)de>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: CREATE TYPE for case insensitive text and varchar
Date: 2003-10-31 03:38:19
Message-ID: 3FA1D92B.5040009@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:

> Daniel Migowski <nur-gueltig-bis-2003-12-01(at)Mig-O(dot)de> writes:
>> I miss the possibility to code something like a userdifined
>> varchar(n).
> 
> You're out of luck on that.  The data types that can have precision
> parameters attached to them are hard-wired into the parser.

Maybe you don't need to invent a whole new data type but the existing 
varchar can serve just fine?

The attached script for version 7.3.4 (does not work with 7.4) 
demonstrates how to add case insensitive operators *=, *> and so on 
including an operator class for btree to the existing varchar.

All one has to do is to use *= instead of = in queries. Indexes, even 
unique, based on case insensitive comparision are possible too and well 
supported. The only thing I think wouldn't work are IN and NOT IN 
constructs.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck(at)Yahoo(dot)com #

Attachment: case_insensitive_varchar.sh
Description: text/plain (3.2 KB)

In response to

pgsql-hackers by date

Next:From: Jan WieckDate: 2003-10-31 04:00:54
Subject: Re: 7.4 and 7.3.5 showstopper
Previous:From: David FetterDate: 2003-10-31 03:23:04
Subject: Re: 7.4RC1 planned for Monday

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group