Обсуждение: Collation and Case Insensitivity

On Thu, Jan 13, 2011 at 02:33:46PM -0000, Phillip Smith wrote:
> Hi,
>
> Im moving over from MS SQL. I've been googling this for ages now and suprisingly cannot find a clear answer.
>
> I want my data in tables to be case insensitive.
>
> This is so i can:
>
> 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. 
>
> 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' 
>
> Is there a database wide collation setting i can make. There are lots of online posts regarding using LOWER function
forquerying. This is a workaround for point (2) but does not remedy point (1) above. 
>
> Many thanks
>
> Phillip
>
> Phillip Smith

There is a citext module that may help:

http://www.postgresql.org/docs/9.0/static/citext.html

Alternatively, you could use a trigger on INSERT/UPDATE to
automatically lower() the incoming data which would allow
(2) to work.

Regards,
Ken

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

On 2011-01-14, Susanne Ebrecht <susanne@2ndQuadrant.com> wrote:

> CREATE UNIQUE INDEX ON tab(LOWER(col));
> or even by using UPPER:
> CREATE UNIQUE INDEX ON tab(UPPER(col));

or possibly:

CREATE UNIQUE INDEX ON tab((col::citext));


--
⚂⚃ 100% natural