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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-01-14 22:33:31 | Re: Upgrade path from 8.2.9 to 9.0 |
Previous Message | Kenneth Marshall | 2011-01-13 15:11:27 | Re: Collation and Case Insensitivity |