Thread: Collation and Case Insensitivity
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 version of 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 row as 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 for querying. This is a workaround for point (2) but does not remedy point (1) above.
Many thanks
Phillip
Phillip Smith
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