Re: [HACKERS] Re: [SQL] RULE questions.

Thus spake Tom Lane
> "D'Arcy" "J.M." Cain <darcy(at)druid(dot)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(at){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.