Hello you should to use a citext datatype http://www.postgresql.org/docs/9.0/interactive/citext.html regards Pavel Stehule 2011/1/13 Phillip Smith <phillip@xxxxxxxxxxxxx>: > Hi, > I'm 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, or a case > insensitiveÂcharacter type.Â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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
