Skip site navigation (1) Skip section navigation (2)

Re: Collation and Case Insensitivity

From: Susanne Ebrecht <susanne(at)2ndQuadrant(dot)com>
To: Phillip Smith <phillip(at)softworks(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Collation and Case Insensitivity
Date: 2011-01-14 14:20:48
Message-ID: 4D305BC0.7050300@2ndQuadrant.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Bruce MomjianDate: 2011-01-14 22:33:31
Subject: Re: Upgrade path from 8.2.9 to 9.0
Previous:From: Kenneth MarshallDate: 2011-01-13 15:11:27
Subject: Re: Collation and Case Insensitivity

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group