Thread: Case insensitive selects?

Case insensitive selects?

From
"David Reid"
Date:
Does pgsql support this and how would I do it?

david


Re: Case insensitive selects?

From
"Adam Lang"
Date:
It is in the list archives several times.

All you need is to use some basic SQL.

select * from mytable where upper('my criteria') = upper(mytable.info);

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "David Reid" <dreid@jetnet.co.uk>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, February 14, 2001 9:58 AM
Subject: [GENERAL] Case insensitive selects?


> Does pgsql support this and how would I do it?
>
> david


Re: Case insensitive selects?

From
Anand Raman
Date:
Hi
use it if u absolutly need it.. Using a function on a column name
doesnt use the index associated with that column,.. So exercise this
option with some amount of thinking..

Anand
On Wed, Feb 14, 2001 at 11:39:47AM -0500, Adam Lang wrote:
>It is in the list archives several times.
>
>All you need is to use some basic SQL.
>
>select * from mytable where upper('my criteria') = upper(mytable.info);
>
>Adam Lang
>Systems Engineer
>Rutgers Casualty Insurance Company
>http://www.rutgersinsurance.com
>----- Original Message -----
>From: "David Reid" <dreid@jetnet.co.uk>
>To: <pgsql-general@postgresql.org>
>Sent: Wednesday, February 14, 2001 9:58 AM
>Subject: [GENERAL] Case insensitive selects?
>
>
>> Does pgsql support this and how would I do it?
>>
>> david

Re: Case insensitive selects?

From
Michael Fork
Date:
Indexes *can* and *will* be used if you create the appropiate
functional indexes, i.e:

CREATE INDEX idx_table_field_upper ON table(upper(field));

SELECT field FROM table WHERE upper(field) LIKE upper('some string');

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Thu, 15 Feb 2001, Anand Raman wrote:

> Hi
> use it if u absolutly need it.. Using a function on a column name
> doesnt use the index associated with that column,.. So exercise this
> option with some amount of thinking..
>
> Anand
> On Wed, Feb 14, 2001 at 11:39:47AM -0500, Adam Lang wrote:
> >It is in the list archives several times.
> >
> >All you need is to use some basic SQL.
> >
> >select * from mytable where upper('my criteria') = upper(mytable.info);
> >
> >Adam Lang
> >Systems Engineer
> >Rutgers Casualty Insurance Company
> >http://www.rutgersinsurance.com
> >----- Original Message -----
> >From: "David Reid" <dreid@jetnet.co.uk>
> >To: <pgsql-general@postgresql.org>
> >Sent: Wednesday, February 14, 2001 9:58 AM
> >Subject: [GENERAL] Case insensitive selects?
> >
> >
> >> Does pgsql support this and how would I do it?
> >>
> >> david
>


Re: Case insensitive selects?

From
David Wheeler
Date:
On Thu, 15 Feb 2001, Michael Fork wrote:

> Indexes *can* and *will* be used if you create the appropiate
> functional indexes, i.e:
>
> CREATE INDEX idx_table_field_upper ON table(upper(field));
>
> SELECT field FROM table WHERE upper(field) LIKE upper('some string');

Hmmm...I'd hate to have two indexes on every field I query like this, one
case-senstive, one case-insensitve (like the one you create here). Is
there a configuration option or something that will tell pgsql to do
case-insensitive comparisons (kinda like MS SQL Server has)? That could
save us on indexing overhead, since we want all of our WHERE comparisons
to be case-insensitive, anyway.

I should also not that we're also using --with-multibyte and having all of
our databases use Unicode exclusively.

Thanks!

David


Re: Case insensitive selects?

From
Tom Lane
Date:
David Wheeler <david@wheeler.net> writes:
>> Indexes *can* and *will* be used if you create the appropiate
>> functional indexes, i.e:
>>
>> CREATE INDEX idx_table_field_upper ON table(upper(field));
>>
>> SELECT field FROM table WHERE upper(field) LIKE upper('some string');

> Hmmm...I'd hate to have two indexes on every field I query like this, one
> case-senstive, one case-insensitve (like the one you create here). Is
> there a configuration option or something that will tell pgsql to do
> case-insensitive comparisons (kinda like MS SQL Server has)? That could
> save us on indexing overhead, since we want all of our WHERE comparisons
> to be case-insensitive, anyway.

Then why are you bothering to maintain a case-sensitive index?

There's no free lunch available here; if you think there is, then you
are misunderstanding what an index is.  Either the index is in
case-sensitive order, or it's not.

            regards, tom lane

Re: Case insensitive selects?

From
David Wheeler
Date:
On Thu, 15 Feb 2001, Tom Lane wrote:

> Then why are you bothering to maintain a case-sensitive index?

Because while some queries do a case-insensitive query, others do not, in
the sense that I do not everywhere convert the string to compare to lower
case.

> There's no free lunch available here; if you think there is, then you
> are misunderstanding what an index is.  Either the index is in
> case-sensitive order, or it's not.

Well, I think I understand pretty well what an index is. But I don't get
that the earlier example was of a case-insensitive index, but of an index
where all the entries were forced into lower case (or upper case, as the
case may be [pun not intended]). Thus, if I have this index:

CREATE INDEX idx_mime_type__name ON mime_type(LOWER(name));

and I execute this query:

SELECT *
FROM   mime_type
WHERE  name = 'text/HTML';

Will it use the index I created above or not? I'm assuming not unless I
rewrite the query like this:

SELECT *
FROM   mime_type
WHERE  name = LOWER('text/HTML');

But then I wouldn't call the index I created "case-insensitive."

But I would be happy to know if I'm missing something here.

Thanks,

David


Re: Case insensitive selects?

From
Michael Fork
Date:
If you are going to be only doing case-insensitive compares, why would you
have two indexes on the field?

Although I am no guru on PostgreSQL internals or database theory, a
case insensitive select on a mixed case index would not work for the
following reason (correct me if i am wrong):

1) becuase of ASCII values and the way btree indexes are ordered, 'A' and
'a' are not store next to each other, meaning that you cannot map all the
caracters of the index to the same case on the fly w/o missing a chunk of
index (unless you wanted to make multiple passes through the index, which
would negate any speed gains of *not* having multiple indexes becuase of
the exponential growth, i.e. searching for 'that' would require 16 passes
thru -- what, What, wHat, whAt, whaT, etc.)

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Thu, 15 Feb 2001, David Wheeler wrote:

>
> On Thu, 15 Feb 2001, Michael Fork wrote:
>
> > Indexes *can* and *will* be used if you create the appropiate
> > functional indexes, i.e:
> >
> > CREATE INDEX idx_table_field_upper ON table(upper(field));
> >
> > SELECT field FROM table WHERE upper(field) LIKE upper('some string');
>
> Hmmm...I'd hate to have two indexes on every field I query like this, one
> case-senstive, one case-insensitve (like the one you create here). Is
> there a configuration option or something that will tell pgsql to do
> case-insensitive comparisons (kinda like MS SQL Server has)? That could
> save us on indexing overhead, since we want all of our WHERE comparisons
> to be case-insensitive, anyway.
>
> I should also not that we're also using --with-multibyte and having all of
> our databases use Unicode exclusively.
>
> Thanks!
>
> David
>


Re: Case insensitive selects?

From
Tom Lane
Date:
David Wheeler <david@wheeler.net> writes:
> Thus, if I have this index:
>
> CREATE INDEX idx_mime_type__name ON mime_type(LOWER(name));
>
> and I execute this query:
>
> SELECT *
> FROM   mime_type
> WHERE  name = 'text/HTML';
>
> Will it use the index I created above or not? I'm assuming not unless I
> rewrite the query like this:
>
> SELECT *
> FROM   mime_type
> WHERE  name = LOWER('text/HTML');

Not then either; you'd need to write

SELECT *
FROM   mime_type
WHERE  LOWER(name) = LOWER('text/HTML');

or equivalently

SELECT *
FROM   mime_type
WHERE  LOWER(name) = 'text/html';

which is what will result from constant-folding anyway.

The details of invocation seem beside the point, however.  The point is
that a btree index is all about sort order, and the sort order of data
viewed case-sensitively is quite different from the sort order of
monocased data.  Perhaps in an ASCII universe you could play some tricks
to make the same index serve both purposes, but it'll never work in
non-ASCII locales ...

            regards, tom lane

misc psql questions

From
James Thompson
Date:
About a lifetime ago I used to do quite a bit of work w/ Oracle.

It's command line sql tool had some pretty nice features that I haven't
been able to find in psql.  I was wondering if any of the following
existed....

I think the first was called break on which altered in output from
something like

name    date        qty
-------------------------
Fred    01-JAN-2000 10
Fred    10-JAN-2000 13
Fred    01-JUL-2000 1
Fred    01-DEC-2000 100
Bob     01-JAN-2000 5
Bob     10-MAY-2000 10

to

name    date        qty
-------------------------
Fred    01-JAN-2000 10
        10-JAN-2000 13
        01-JUL-2000 1
        01-DEC-2000 100
Bob     01-JAN-2000 5
        10-MAY-2000 10

it also allowed for things like compute sum which would activate on breaks
but I don't recall how they worked.

The other thing I'd love to be able to do is get user input while running
a sql file.  I don't recall how this worked exactly but the script would
either accept variables calling the script or prompt for them.

So if I had a sql script in a file named contact_report.  And I did

prod=> \i contact_report 01-JAN-2000 31-DEC-2001

then it would load the script and replace IIRC &1 and &2 with the
respective dates listed on command line.

It also had an ACCEPT command that would cause it to prompt for input from
user and assign to a varable name.  like

accept amount prompt 'Enter the amount to search for: '
select * from foo where quantity = &amount

These made it very easy to build simple reports and scripts for less
technical end users.

Is any of this possible with psql?

Thanks,
James

->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
James Thompson    138 Cardwell Hall  Manhattan, Ks   66506    785-532-0561
Kansas State University                          Department of Mathematics
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<



Re: misc psql questions

From
Peter Eisentraut
Date:
James Thompson writes:

> About a lifetime ago I used to do quite a bit of work w/ Oracle.
>
> It's command line sql tool had some pretty nice features that I haven't
> been able to find in psql.  I was wondering if any of the following
> existed....
>
> I think the first was called break on which altered in output from
> something like
>
> name    date        qty
> -------------------------
> Fred    01-JAN-2000 10
> Fred    10-JAN-2000 13
> Fred    01-JUL-2000 1
> Fred    01-DEC-2000 100
> Bob     01-JAN-2000 5
> Bob     10-MAY-2000 10
>
> to
>
> name    date        qty
> -------------------------
> Fred    01-JAN-2000 10
>         10-JAN-2000 13
>         01-JUL-2000 1
>         01-DEC-2000 100
> Bob     01-JAN-2000 5
>         10-MAY-2000 10
>
> it also allowed for things like compute sum which would activate on breaks
> but I don't recall how they worked.

This seems to be a thing for a report generator.  Try pgaccess.

> The other thing I'd love to be able to do is get user input while running
> a sql file.  I don't recall how this worked exactly but the script would
> either accept variables calling the script or prompt for them.
>
> So if I had a sql script in a file named contact_report.  And I did
>
> prod=> \i contact_report 01-JAN-2000 31-DEC-2001
>
> then it would load the script and replace IIRC &1 and &2 with the
> respective dates listed on command line.

You can use \set to set variables.

> It also had an ACCEPT command that would cause it to prompt for input from
> user and assign to a varable name.

Try
\echo -n 'Prompt: '
\set varname `read input; echo $input`

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Re: Case insensitive selects?

From
David Wheeler
Date:
On Thu, 15 Feb 2001, Tom Lane wrote:

> Not then either; you'd need to write
>
> SELECT *
> FROM   mime_type
> WHERE  LOWER(name) = LOWER('text/HTML');
>
> or equivalently
>
> SELECT *
> FROM   mime_type
> WHERE  LOWER(name) = 'text/html';
>
> which is what will result from constant-folding anyway.

Yes, of course; my oversight.

> The details of invocation seem beside the point, however.  The point is
> that a btree index is all about sort order, and the sort order of data
> viewed case-sensitively is quite different from the sort order of
> monocased data.  Perhaps in an ASCII universe you could play some tricks
> to make the same index serve both purposes, but it'll never work in
> non-ASCII locales ...

Hmmm...somehow, MS gets it to work in SQL Server. Lord knows how (or if
it's effective or fast), but I won't worry about it (since the last
thing I want to do is switch to NT!). I'll just code more carefully per
the examples above to ensure proper index use.

Thanks,

David


Re: Case insensitive selects?

From
David Wheeler
Date:
Hmmm... I'm trying to create an index,

CREATE INDEX idx_server__host_name ON server(LOWER(host_name));

But it won't create. Here's the error:

ERROR:  DefineIndex: function 'upper(varchar)' does not exist

Anyone know what's up with that? The table does have the host_name column
of type VARCHAR.

Thanks,

David


Re: Case insensitive selects?

From
David Wheeler
Date:
Forgot to mention, I'm using 7.03.

Thanks,

David

On Thu, 15 Feb 2001, David Wheeler wrote:

> Hmmm... I'm trying to create an index,
>
> CREATE INDEX idx_server__host_name ON server(LOWER(host_name));
>
> But it won't create. Here's the error:
>
> ERROR:  DefineIndex: function 'upper(varchar)' does not exist
>
> Anyone know what's up with that? The table does have the host_name column
> of type VARCHAR.
>
> Thanks,
>
> David


Re: Case insensitive selects?

From
David Wheeler
Date:
On Thu, 15 Feb 2001, Michael Fork wrote:

> Indexes *can* and *will* be used if you create the appropiate
> functional indexes, i.e:
>
> CREATE INDEX idx_table_field_upper ON table(upper(field));
>
> SELECT field FROM table WHERE upper(field) LIKE upper('some string');

Hmmm...I'd hate to have two indexes on every field I query like this, one
case-senstive, one case-insensitve (like the one you create here). Is
there a configuration option or something that will tell pgsql to do
case-insensitive comparisons (kinda like MS SQL Server has)? That could
save us on indexing overhead, since we want all of our WHERE comparisons
to be case-insensitive, anyway.

I should also not that we're also using --with-multibyte and having all of
our databases use Unicode exclusively.

Thanks!

David


Re: Case insensitive selects?

From
"Mitch Vincent"
Date:
> Hmmm...I'd hate to have two indexes on every field I query like this, one
> case-senstive, one case-insensitve (like the one you create here). Is
> there a configuration option or something that will tell pgsql to do
> case-insensitive comparisons (kinda like MS SQL Server has)? That could
> save us on indexing overhead, since we want all of our WHERE comparisons
> to be case-insensitive, anyway.

If you want all of them to be case insensitive then make the upper ( or
lower() ) index and don't make any case sensitive queries! :-)

Make sure all your queries use upper() or lower() around the field and value
you're comparing and you're golden.. Unless I've misunderstood you, I don't
see the problem..

SELECT * FROM whatever WHERE lower(myfield) = lower('myvalue'); -- and make
your index on lower(myfield)... Viola!

-Mitch


Re: Case insensitive selects?

From
Bruce Momjian
Date:
> On Thu, 15 Feb 2001, Michael Fork wrote:
>
> > Indexes *can* and *will* be used if you create the appropiate
> > functional indexes, i.e:
> >
> > CREATE INDEX idx_table_field_upper ON table(upper(field));
> >
> > SELECT field FROM table WHERE upper(field) LIKE upper('some string');
>
> Hmmm...I'd hate to have two indexes on every field I query like this, one
> case-senstive, one case-insensitve (like the one you create here). Is
> there a configuration option or something that will tell pgsql to do
> case-insensitive comparisons (kinda like MS SQL Server has)? That could
> save us on indexing overhead, since we want all of our WHERE comparisons
> to be case-insensitive, anyway.

I was wondering if we could do case-insensitive index waking by doing
looking for CAR as:

    CAR
    CAr
    CaR
    Car
    cAR
    cAr
    caR
    car

Basically you look for CAR, then back up in the btree, to CA and look
for r instead of R.  I relized the number of tests would exponentially
explode, but isn't it just like btree walking where we back up to test
the lowercase of the letter.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Case insensitive selects?

From
Bruce Momjian
Date:
> On Fri, 16 Feb 2001, Bruce Momjian wrote:
>
> > Yes, our CREATE INDEX lower(col) already does that, but you do have to
> > use lower(col) when doing the query.
>
> Right, that's what I'm suggesting a configuration that automates the
> lower(col) bit in CREATE INDEX and that automates the lower(col) in
> queries.
>
> BTW, I've run into some snags with CREATE INDEX lower(col). First it
> wouldn't work because my col was varchar (fixec by creating a new
> function) and then because I tried to combine columns:
>
> CREATE UNIQUE INDEX idx_name ON server(lower(col1), col2);

Ewe, that is a tough one.  We don't support multi-column functional
indexes, do we?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Case insensitive selects?

From
David Wheeler
Date:
On Fri, 16 Feb 2001, Michael Fork wrote:

> This is the function Tom Lane told me to use in < 7.1 (IIRC, this will
> cause problems in >= 7.1, so you have to remember to remove from your
> dump)
>
> CREATE FUNCTION "upper" (varchar ) RETURNS text AS 'upper' LANGUAGE
> 'INTERNAL';

Yeah, I found that function posted to the list last May. Thanks, Michael.

David


Re: Case insensitive selects?

From
Michael Fork
Date:
This is the function Tom Lane told me to use in < 7.1 (IIRC, this will
cause problems in >= 7.1, so you have to remember to remove from your
dump)

CREATE FUNCTION "upper" (varchar ) RETURNS text AS 'upper' LANGUAGE
'INTERNAL';



Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Thu, 15 Feb 2001, David Wheeler wrote:

> Hmmm... I'm trying to create an index,
>
> CREATE INDEX idx_server__host_name ON server(LOWER(host_name));
>
> But it won't create. Here's the error:
>
> ERROR:  DefineIndex: function 'upper(varchar)' does not exist
>
> Anyone know what's up with that? The table does have the host_name column
> of type VARCHAR.
>
> Thanks,
>
> David
>


Re: Case insensitive selects?

From
David Wheeler
Date:
On Fri, 16 Feb 2001, Bruce Momjian wrote:

> Yes, our CREATE INDEX lower(col) already does that, but you do have to
> use lower(col) when doing the query.

Right, that's what I'm suggesting a configuration that automates the
lower(col) bit in CREATE INDEX and that automates the lower(col) in
queries.

BTW, I've run into some snags with CREATE INDEX lower(col). First it
wouldn't work because my col was varchar (fixec by creating a new
function) and then because I tried to combine columns:

CREATE UNIQUE INDEX idx_name ON server(lower(col1), col2);

But I see that either they all have to be inside the function or for there
be be no function. Will 7.1  support mixing like this?

Thanks for your prompt responses, Bruce.

David


Re: Case insensitive selects?

From
David Wheeler
Date:
On Fri, 16 Feb 2001, Bruce Momjian wrote:

> I was wondering if we could do case-insensitive index waking by doing
> looking for CAR as:
>
>     CAR
>     CAr
>     CaR
>     Car
>     cAR
>     cAr
>     caR
>     car
>
> Basically you look for CAR, then back up in the btree, to CA and look
> for r instead of R.  I relized the number of tests would exponentially
> explode, but isn't it just like btree walking where we back up to test
> the lowercase of the letter.

Wouldn't it be more efficient to just have a single, case-insensitive
index, and then have the query engine automagically compare to the index
in a case-insensitive way? I'm assuming that this is the sort of approach
MS takes, which is why one has to choose the sort order at installation
time. If I choose case-insensitive Unicode, then I would expect the server
to do these things for me behind the scenes:

* When I create an index, automatically convert all char/varchar/text
  fields with lower().
* When I do a query, automatically use lower() on all fields and values
  queried against.

The result would be the same as Mitch describes, only I don't have to do
the work in my queries. The database would assume I want case-insensitive
matching based on some configuration I set, and do all the lower()s for
me. Perhaps the configuration could be set on a per-database basis (like
character set now is with multibyte).

Does that make sense?

Best

David



Re: Case insensitive selects?

From
Bruce Momjian
Date:
> Wouldn't it be more efficient to just have a single, case-insensitive
> index, and then have the query engine automagically compare to the index
> in a case-insensitive way? I'm assuming that this is the sort of approach
> MS takes, which is why one has to choose the sort order at installation
> time. If I choose case-insensitive Unicode, then I would expect the server
> to do these things for me behind the scenes:
>

Yes, our CREATE INDEX lower(col) already does that, but you do have to
use lower(col) when doing the query.


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Case insensitive selects?

From
David Wheeler
Date:
On Fri, 16 Feb 2001, Bruce Momjian wrote:

> Ewe, that is a tough one.  We don't support multi-column functional
> indexes, do we?

Too bad, because I could use that. I'm getting around it for now by making
sure the records in col1 are always lower case, anyway.

Thanks,

David


Re: Case insensitive selects?

From
Martijn van Oosterhout
Date:
Tom Lane wrote:

[snip]

> > Hmmm...I'd hate to have two indexes on every field I query like this, one
> > case-senstive, one case-insensitve (like the one you create here). Is
> > there a configuration option or something that will tell pgsql to do
> > case-insensitive comparisons (kinda like MS SQL Server has)? That could
> > save us on indexing overhead, since we want all of our WHERE comparisons
> > to be case-insensitive, anyway.
>
> Then why are you bothering to maintain a case-sensitive index?
>
> There's no free lunch available here; if you think there is, then you
> are misunderstanding what an index is.  Either the index is in
> case-sensitive order, or it's not.

I've actually been thinking about this and maybe this is possible with
some smarts in the query parser. If you have an index on
lower(fieldname) then consider the following query:

select *
from table1, table2
where table1.a = table2.b;

(the index is on lower(table1.a).

Now, it should be true that a = b implies lower(a) = lower(b), so the
above query is equivalent to:

select *
from table1, table2
where table1.a = table2.b
and lower(table1.a) = lower(table2.b);

This query can use the index and produce the correct result. Am I
missing anything?
--
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

Re: Case insensitive selects?

From
David Wheeler
Date:
On Sun, 18 Feb 2001, Martijn van Oosterhout wrote:

> Tom Lane wrote:
>
> [snip]
> >
> > Then why are you bothering to maintain a case-sensitive index?
> >
> > There's no free lunch available here; if you think there is, then you
> > are misunderstanding what an index is.  Either the index is in
> > case-sensitive order, or it's not.
>
> I've actually been thinking about this and maybe this is possible with
> some smarts in the query parser. If you have an index on
> lower(fieldname) then consider the following query:
>
> select *
> from table1, table2
> where table1.a = table2.b;
>
> (the index is on lower(table1.a).
>
> Now, it should be true that a = b implies lower(a) = lower(b), so the
> above query is equivalent to:
>
> select *
> from table1, table2
> where table1.a = table2.b
> and lower(table1.a) = lower(table2.b);
>
> This query can use the index and produce the correct result. Am I
> missing anything?

This is almost exactly what I was thinking of.

David