non-case sensitive searches

On Wed, 13 Jan 1999, Kevin Heflin wrote:

Currently I have a select statement like so:

select * from photos where keywords LIKE '%$cat_name%'

The above select statement works, except that it is case sensitive.
Is there a way to do this that is not case sensitive?

Sorry for the lame question, I found a suggestion on some SQL mailing list
archive which suggested using:

SELECT * from photos were lower(keywords) LIKE lower('%$cat_name%'

--------------------------------------------------------------------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619 | FAX:318.221.6612
kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

Hi Kevin,

You can also use regexp expression like this :

SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';

it works fine.

Gilles

Kevin Heflin wrote:

On Wed, 13 Jan 1999, Gilles Darold wrote:

Hi Kevin,

You can also use regexp expression like this :

SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';

it works fine.

Thanks for the tip. Any recommendations as to which would be faster when
searching about 2,000 records or so..

WHERE keywords ~* '.*$cat_name.*';

or

WHERE lower(keywords) LIKE lower('%$cat_name%')

Thanks again.

--------------------------------------------------------------------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619 | FAX:318.221.6612
kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

Actually, There is a far better way to do that, the exact operator
escapes me right now... but its in the postgres documentation.

On Wed, 13 Jan 1999, Kevin Heflin wrote:

On Wed, 13 Jan 1999, Gilles Darold wrote:

Hi Kevin,

You can also use regexp expression like this :

SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';

it works fine.

Thanks for the tip. Any recommendations as to which would be faster when
searching about 2,000 records or so..

WHERE keywords ~* '.*$cat_name.*';

or

WHERE lower(keywords) LIKE lower('%$cat_name%')

Well, you could (1) omit the second "lower()" function
(like '%cat_name%' is already in lower case!) (2) insure that your
table already contains nothing but lower-case (or upper-case) entries,
if appropriate.

- Tim

You can also use regexp expression like this :

SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';

it works fine.

Thanks for the tip. Any recommendations as to which would be faster when
searching about 2,000 records or so..

LIKE is faster, but with 2,000 records, you won't be able to tell the
difference. It takes a blink.

--Gene

You can also use regexp expression like this :

SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';

it works fine.

Thanks for the tip. Any recommendations as to which would

be faster when

searching about 2,000 records or so..

LIKE is faster, but with 2,000 records, you won't be able to tell the
difference. It takes a blink.

--Gene

And remember unless you use full text indexing (which is expensive) you
won't have indexing on that search (for either the LIKE or regex).
-DEJ

On Wed, 13 Jan 1999, Jackson, DeJuan wrote:

And remember unless you use full text indexing (which is

expensive) you

won't have indexing on that search (for either the LIKE or regex).
-DEJ

Actually, if you use case sensitive regex, and anchor the
begining (with
^) then it will use btree indexes.

So will a front anchored LIKE, but the query that was being performed
was an unanchored LIKE (i.e. '%stuff%').

On Wed, 13 Jan 1999, Jackson, DeJuan wrote:

And remember unless you use full text indexing (which is expensive) you
won't have indexing on that search (for either the LIKE or regex).
-DEJ

Actually, if you use case sensitive regex, and anchor the begining (with
^) then it will use btree indexes.

I got an error when accessing a view. The view looks like this:

CREATE TABLE ar_contacts(
contact_seq int,
update_date date,
period date,
paid float,
sales float,
last_date date,
due float,
PRIMARY KEY (contact_seq, period)
);
CREATE VIEW ar_sales90_v AS
SELECT contact_seq, SUM(sales) AS sales90 FROM ar_contacts WHERE
period >= date(date_trunc('month','now'::date) - '2 month'::timespan)
GROUP BY contact_seq;

If I
SELECT * FROM ar_sales90_v WHERE contact_seq = 1;
then everything is fine. I get what I expect:

contact_seq|sales90
-----------+-------
1| 6
(1 row)

If I do this:
SELECT sales90 FROM ar_sales90_v WHERE contact_seq = 1;
I get this result:

pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while
processing the request. We have lost the connection to the backend, so
further processing is impossible. Terminating.

The backend didn't die; I can restart psql and do my queries.

We have problems with aggregates in some views. It is a know problem on
the TODO list.

I got an error when accessing a view. The view looks like this:

CREATE TABLE ar_contacts(
contact_seq int,
update_date date,
period date,
paid float,
sales float,
last_date date,
due float,
PRIMARY KEY (contact_seq, period)
);
CREATE VIEW ar_sales90_v AS
SELECT contact_seq, SUM(sales) AS sales90 FROM ar_contacts WHERE
period >= date(date_trunc('month','now'::date) - '2 month'::timespan)
GROUP BY contact_seq;

If I
SELECT * FROM ar_sales90_v WHERE contact_seq = 1;
then everything is fine. I get what I expect:

contact_seq|sales90
-----------+-------
1| 6
(1 row)

If I do this:
SELECT sales90 FROM ar_sales90_v WHERE contact_seq = 1;
I get this result:

pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while
processing the request. We have lost the connection to the backend, so
further processing is impossible. Terminating.

The backend didn't die; I can restart psql and do my queries.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026