Hello Phillip,
On 13.01.2011 15:33, Phillip Smith wrote:
> 1. Put keys on natural key fields, for instance a product part number. I product number 'ABC123' inserted i need to
disallow'abc123' to be inserted as a second row. Please don't tell me i have to add another column holding a lowered
versionof the product number. My database is littered with this need and i would end up bloating my table schema.
All roads lead to Rome.
You either can do:
INSERT INTO tab(col,...) VALUES(UPPER(value),....);
INSERT INTO tab(col,...) VALUES(LOWER(value),...);
To make sure that only upper or lower values will get inserted.
The other way is that you use an UPPER or LOWER UNIQUE index:
DROP your UNIQUE index for the column and create a new one:
CREATE UNIQUE INDEX ON tab(LOWER(col));
or even by using UPPER:
CREATE UNIQUE INDEX ON tab(UPPER(col));
>
> 2. I need to query case insensitively. SELECT * FROM product WHERE product_number = 'ABC123' should return the same
rowas SELECT * FROM product WHERE product_number = 'abc123'
SELECT * FROM product WHERE UPPER(product_number) = UPPER('ABC123');
or
SELECT * FROM product WHERE LOWER(product_number) = LOWER('ABC123');
> Is there a database wide collation setting i can make.
PostgreSQL is using libc for localisation/globalisation.
For collation it is LC_COLLATE.
It will be set up during initdb.
Best Regards,
Susanne
--
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com