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

Collation and Case Insensitivity

From: "Phillip Smith" <phillip(at)softworks(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Collation and Case Insensitivity
Date: 2011-01-13 14:33:46
Message-ID: D869A69BDB32184981F31AB9349C8C9B188D1D@exch-be34.exchange.local (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Kenneth MarshallDate: 2011-01-13 15:11:27
Subject: Re: Collation and Case Insensitivity
Previous:From: Tom LaneDate: 2011-01-13 00:36:15
Subject: Re: batching commands with libpq

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