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)
CREATE INDEX t_val_ci_ind
On Mon, Jul 1, 2013 at 10:01 AM, Albe Laurenz laurenz.a...@wien.gv.at 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 |
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 little bit
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 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
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, citext, etc
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.
Subject: Re: [GENERAL] Postgres case
On Jun 29, 2013, at 3:59, bhanu udaya udayabhanu1...@hotmail.com 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
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;
pgadmin-supp
On Jun 29, 2013, at 15:02, bhanu udaya udayabhanu1...@hotmail.com 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,
indexes used.
I tried with upper, Citext, but the result set was more than a
second.
The OS server we are using is Linux 64 bit.
Thanks and Regards
Radha Krishna
Subject: Re: [pgadmin-support] [GENERAL] Postgres case
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...
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; pgadmin-supp
...@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 searches in postgres
using Like.
Ilike - does not use indexes
function based
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
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 Jun 29, 2013, at 11:24 AM, bhanu udaya udayabhanu1...@hotmail.com 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
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))
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
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)
CREATE
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
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
22 matches
Mail list logo
