Ingmar Brouns wrote:
>> My solution is fast and efficient, it will call upper() only once
>> per query. I don't see your problem. Different database systems
>> do things in different ways, but as long as you can do what you need
>> to do, that should be good enough.
> I was toying around a littl
On Mon, Jul 1, 2013 at 10:01 AM, Albe Laurenz wrote:
> bhanu udaya wrote:
What is the best way of doing case insensitive searches in postgres using
Like.
>>>
>>> Table "laurenz.t"
>>> Column | Type | Modifiers
>>> +-+---
>>> id | integer | not null
>>> val | text
bhanu udaya wrote:
>>> What is the best way of doing case insensitive searches in postgres using
>>> Like.
>>
>> Table "laurenz.t"
>> Column | Type | Modifiers
>> +-+---
>> id | integer | not null
>> val | text | not null
>> Indexes:
>> "t_pkey" PRIMARY KEY, btree (id)
>>
>
pattern? Can you cache the result in a materialized view?
In general, getting one row from an index from a table that fits in your
RAM is possible in a few ms. Case insensitive or not.
Can you show us a explain analyze.
>
> Thanks for all replies and help.
> ________
&g
like to do more research and come to
conclusion.
From: udayabhanu1...@hotmail.com
To: ne...@neiltiffin.com
CC: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Postgres case insensitive searches
Date: Sun, 30 Jun 2013 22:35:32 +0530
I almost used every option ; upper, posix, gist, gin, c
? I could have gone for partitions, etc., but it is
plan B and more over partitions in postgres has to undergo more manual process.
Thanks for all replies and help.
Subject: Re: [GENERAL] Postgres case insensitive searches
From: ne...@neiltiffin.com
Date: Sat, 29 Jun 2013 14:08:47 -0500
CC: pgsql
for partitions, etc., but it is plan B and more
over partitions in postgres has to undergo more manual process.
Thanks for all replies and help.
Subject: Re: [GENERAL] Postgres case insensitive searches
From: ne...@neiltiffin.com
Date: Sat, 29 Jun 2013 14:08:47 -0500
CC: pgsql-general
I have a table called jobs with ~17 millions records. Without an index on
the queue column, the following query
select count(*) from jobs where lower(queue) = 'normal'
found ~2.6 millions records in 10160ms
With the following index:
create index lower_queue on jobs (lower(queue))
th
On Jun 29, 2013, at 11:24 AM, bhanu udaya wrote:
> Upper and Lower functions are not right choice when the table is > 2.5
> million and where we also have heavy insert transactions.
PostgreSQL and SQL Server are completely different. Rules that apply to SQL
Server do not necessarily apply to
On 06/29/2013 09:24 AM, bhanu udaya wrote:
Upper and Lower functions are not right choice when the table is > 2.5
million and where we also have heavy insert transactions.
Prove it. Seriously, just run a test case against it. See how it works
for you. Inserts are generally a very inexpensive
On 6/29/2013 9:24 AM, bhanu udaya wrote:
Upper and Lower functions are not right choice when the table is > 2.5
million and where we also have heavy insert transactions.
I doubt, if we can cache the table if there are frequent
inserts/updates. The good idea would be to get the DB to case
ins
.at;
> chris.trav...@gmail.com; mag...@hagander.net
> Subject: Re: [GENERAL] Postgres case insensitive searches
>
>
> On 06/28/2013 03:21 AM, bhanu udaya wrote:
> > Hello,
> >
> > Grettings,
> >
> > What is the best way of doing case insensitive se
> Subject: Re: [pgadmin-support] [GENERAL] Postgres case
insensitive searches
> From: haram...@gmail.com
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: laurenz.a...@wien.gv.at;
pgsql-general@postgresql.org;
On 06/28/2013 03:21 AM, bhanu udaya wrote:
Hello,
Grettings,
What is the best way of doing case insensitive searches in postgres
using Like.
Ilike - does not use indexes
function based indexes are not as fast as required.
CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... doe
res case
insensitive searches
> From: haram...@gmail.com
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: laurenz.a...@wien.gv.at;
pgsql-general@postgresql.org; pgadmin-supp...@postgresql.org
> To: udayabhanu1
On Jun 29, 2013, at 15:02, bhanu udaya wrote:
> I agree that it is just search condition. But, in a 2.5 million record table
> search, upper function is not that fast.
Suit yourself, the solution is there.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll
we are using is Linux 64 bit.
Thanks and Regards
Radha Krishna
> Subject: Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
> From: haram...@gmail.com
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: laurenz.a...@wien.gv.at; pgsql-general@postgresql.org;
&g
On Jun 29, 2013, at 3:59, bhanu udaya wrote:
> Thanks. But, I do not want to convert into upper and show the result.
Why not? It won't modify your results, just the search condition:
SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val;
Or:
SELECT id, val FROM t WHERE upper(val) L
On 6/28/2013 6:59 PM, bhanu udaya wrote:
select * from table where type like 'ab%'. It should get all above 3
records. Is there a way the database itself can be made
case-insensitive with UTF8 characterset. I tried with character type &
collation POSIX, but it did not really help.
use ILIKE
Thanks. But, I do not want to convert into upper and show the result.
Example, if I have records as below:
id type
1. abcd
2. Abcdef
3. ABcdefg
4. aaadf
The below query should report all the above
select * from table where type like 'ab%'. It should get all above 3 records.
Is there a way
bhanu udaya wrote:
> What is the best way of doing case insensitive searches in postgres using
> Like.
Table "laurenz.t"
Column | Type | Modifiers
+-+---
id | integer | not null
val| text| not null
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
CRE
Hello,
Grettings,
What is the best way of doing case insensitive searches in postgres using Like.
Ilike - does not use indexes
function based indexes are not as fast as required.
CITEXT - it still taking 600 ms - 1 second on a 2.2 million rows... does not
use index
Collation Indexes creatio
22 matches
Mail list logo
