Thread: Matching against a field case in-sensitive.

Hi,

Since PostgreSQL is by default case sensitive, I would like to know if it is possible to do a search or somehow set the column in a relation to be case insensitive (on search at least).

I would however like to store it case sensitive so I can display it as it was stored.

Ps. I am not interested in creating another column.

Since PostgreSQL is by default case sensitive, I would like to know if it is possible to do a search or somehow set the column in a relation to be case insensitive (on search at least). 

I would however like to store it case sensitive so I can display it as it was stored.

PostgreSQL has string functions , you can use these in your where clause 

In response to James Dooley :
> Hi,
>
> Since PostgreSQL is by default case sensitive, I would like to know if it is
> possible to do a search or somehow set the column in a relation to be case
> insensitive (on search at least).
>
> I would however like to store it case sensitive so I can display it as it was
> stored.

test=# select * from foo where t = 'foo';
 i |  t
---+-----
 1 | foo
(1 row)

test=*# select * from foo where lower(t) = lower('foo');
 i |  t
---+-----
 1 | foo
 2 | FOO
(2 rows)


You can also create an index based on lower() for such queries.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

KEYWORDS:
Identifier and key word names are case insensitive. Therefore
UPDATE MY_TABLE SET A = 5;
can equivalently be written as
uPDaTE my_TabLE SeT a = 5;

IDENTIFIERS:
Quoting an identifier also makes it case-sensitive,
whereas unquoted names are always folded to lower case.
For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL,
but "Foo" and "FOO" are different from these three and each other.
(The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

in summary taking the quotes off identifiers will make your SQL statement case insensitive..

http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html

HTH
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

---

Date: Mon, 2 Mar 2009 15:40:27 +0100
Subject: [GENERAL] Matching against a field case in-sensitive.
From: jamdooley@gmail.com
To: pgsql-general@postgresql.org

Hi,

Since PostgreSQL is by default case sensitive, I would like to know if it is possible to do a search or somehow set the column in a relation to be case insensitive (on search at least).

I would however like to store it case sensitive so I can display it as it was stored.

Ps. I am not interested in creating another column.

---

Windows Live™: Life without walls. Check it out.

James Dooley wrote:
> Hi,
>
> Since PostgreSQL is by default case sensitive, I would like to know if
> it is possible to do a search or somehow set the column in a relation to
> be case insensitive (on search at least).
>
> I would however like to store it case sensitive so I can display it as
> it was stored.
>
> Ps. I am not interested in creating another column.
>
>
>
>

select value from table where relation ilike 'search-criteria'