On Thu, Jan 13, 2011 at 02:33:46PM -0000, Phillip Smith wrote:
> 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 Smith
There is a citext module that may help:
Alternatively, you could use a trigger on INSERT/UPDATE to
automatically lower() the incoming data which would allow
(2) to work.
In response to
pgsql-novice by date
|Next:||From: Susanne Ebrecht||Date: 2011-01-14 14:20:48|
|Subject: Re: Collation and Case Insensitivity|
|Previous:||From: Phillip Smith||Date: 2011-01-13 14:33:46|
|Subject: Collation and Case Insensitivity|