Thread: 2Q: Vacuuming and making queries case insensitive
I am using PostgreSQL through the command line. I have written a small application that retrieves me information from a database. I have figured most things I need to know out. But I have two questions I hope you guys can help me with.
Is there any command line script that can make the database run faster? Like vacuuming or something. I'm sorry to ask what is surly a very simple question. But everything is hard to the very inexperienced.
I also wonder if it is possible to make command line searches case insensitive? Like when I write WHERE name='PHIL' I want it to be the same as WHERE name='phil'.
This things would be great if they are possible.
Thanks for taking your time to read my email through!
Best Redards,
S.E.
Is there any command line script that can make the database run faster? Like vacuuming or something. I'm sorry to ask what is surly a very simple question. But everything is hard to the very inexperienced.
I also wonder if it is possible to make command line searches case insensitive? Like when I write WHERE name='PHIL' I want it to be the same as WHERE name='phil'.
This things would be great if they are possible.
Thanks for taking your time to read my email through!
Best Redards,
S.E.
On Jan 27, 2008 8:07 PM, sdger erger <killen.nextdoor@gmail.com> wrote:
You'll need to tell us what is slow. Use the "explain" command and post the results here. For example:
explain select stuff from table where name ilike 'phil';
where name ilike 'phil'.
Sean
I am using PostgreSQL through the command line. I have written a small application that retrieves me information from a database. I have figured most things I need to know out. But I have two questions I hope you guys can help me with.
Is there any command line script that can make the database run faster? Like vacuuming or something. I'm sorry to ask what is surly a very simple question. But everything is hard to the very inexperienced.
You'll need to tell us what is slow. Use the "explain" command and post the results here. For example:
explain select stuff from table where name ilike 'phil';
I also wonder if it is possible to make command line searches case insensitive? Like when I write WHERE name='PHIL' I want it to be the same as WHERE name='phil'.
where name ilike 'phil'.
Sean
"Sean Davis" <sdavis2@mail.nih.gov> writes:
> On Jan 27, 2008 8:07 PM, sdger erger <killen.nextdoor@gmail.com> wrote:
>> I also wonder if it is possible to make command line searches case
>> insensitive? Like when I write WHERE name='PHIL' I want it to be the same as
>> WHERE name='phil'.
> where name ilike 'phil'.
Probably better: WHERE lower(name) = lower('PHIL')
since this has some chance of using an index (you'd need an index on
lower(name)). ILIKE is basically dead in the water for indexing.
If you're desperate not to change your queries that way, look for citext
on pgfoundry.
regards, tom lane
"Eric Comeau" <Eric.Comeau@signiant.com> writes:
> Did CIText get migrated from gborg to pgfoundry or can I just not find
> it there? I checked under PostgreSQL Enhancements/Data Types and tried a
> search.
Hmm, yeah, I don't see it either. Looking in the pgsql-www archives,
it seems to have been written off as a dead project, but that was
probably a mistake because people are still using it. Marc, could
you resurrect that project from the backup and put it on pgfoundry?
In the meantime, I have a rather old copy laying about here, which
I attach --- I have not tried it in some time though, it may need
minor fixes to get it to work with recent PG releases.
regards, tom lane