From: "D'Arcy" "J.M." Cain <darcy@druid.net>
To: tgl@sss.pgh.pa.us
Cc: pgsql-hackers@PostgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] RULE questions.
Date: Thu, 11 Feb 1999 14:03:48 -0500 (EST)
Message-ID: <m10B1Ou-0000c1C@druid.net> (raw)
In-Reply-To: <14220.918747300@sss.pgh.pa.us>
Thus spake Tom Lane
> "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> > Second, an option to CREATE INDEX to make the index case insensitive.
>
> That, at least, we can already do: build the index on lower(field) not
> just field. Or upper(field) if that seems more natural to you.
Almost. I guess I wasn't completely clear. Here's an example.
darcy=> create table x (a int, t text);
CREATE
darcy=> create unique index ti on x (lower(t) text_ops);
CREATE
darcy=> insert into x values (1, 'abc');
INSERT 19021 1
darcy=> insert into x values (2, 'ABC');
ERROR: Cannot insert a duplicate key into a unique index
darcy=> insert into x values (2, 'Def');
INSERT 19023 1
darcy=> select * from x;
a|t
-+---
1|abc
2|Def
(2 rows)
darcy=> select * from x where t = 'ABC';
a|t
-+-
(0 rows)
Note that it prevented me from adding the upper case dup just fine. The
last select is the issue. It's necessary for the user to know how it is
stored before doing the select. I realize that you can do this.
darcy=> select * from x where lower(t) = 'abc';
But other systems make this more convenient by just making 'ABC' and 'abc'
equivalent.
Mind you, it may not be possible in our system without creating a new,
case-insensitive type.
> > Also, in a primary key field (or
> > unique index) it would be nice if "A" was rejected if "a" already was
> > in the database.
>
> Making either of the above a UNIQUE index should accomplish that.
True. I'm thinking of the situation where you want the primary key to
be case-insensitive. You can't control that on the auto-generated
unique index so you have to add a second unique index on the same
field. Again, perhaps a new type is the proper way to handle this.
Speaking of primary keys, there's one more thing needed to make primary
support complete, I think. Didn't we originally say that a primary
key field was immutable? We should be able to delete the record but
not change the value of the field in an update. Would this be hard
to do?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, tgl@sss.pgh.pa.us, pgsql-hackers@PostgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] RULE questions.
In-Reply-To: <m10B1Ou-0000c1C@druid.net>
References: <14220.918747300@sss.pgh.pa.us> <m10B1Ou-0000c1C@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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