Thread: RE: [GENERAL] non-case sensitive searches

> > > 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.

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