Re: Collation and Case Insensitivity

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 version of 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 row as 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