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');
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.
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
In response to
pgsql-novice by date
|Next:||From: Bruce Momjian||Date: 2011-01-14 22:33:31|
|Subject: Re: Upgrade path from 8.2.9 to 9.0|
|Previous:||From: Kenneth Marshall||Date: 2011-01-13 15:11:27|
|Subject: Re: Collation and Case Insensitivity|