pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
ARRAYs and INDEXes ... 3+ messages / 3 participants [nested] [flat]
* ARRAYs and INDEXes ... @ 2005-08-16 04:54 Marc G. Fournier <scrappy@postgresql.org> 0 siblings, 2 replies; 3+ messages in thread From: Marc G. Fournier @ 2005-08-16 04:54 UTC (permalink / raw) To: pgsql-sql Can't seem to find anything concerning this in the docs, and I don't think it is something that can be done, but figure I'll double check before I write it off completely ... If I create a table: CREATE TABLE customers ( customer_id SERIAL, monthly_balance DECIMAL(7,2)[12] ); Is it possible to create an INDEX on customers.monthly_balance such that I could do something like: SELECT * FROM customers WHERE monthly_balance[6] = 0.00; As an example ... or SELECT * FROM customers WHERE 0.00 = any (monthly_balance); ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ARRAYs and INDEXes ... @ 2005-08-16 05:30 Michael Fuhr <mike@fuhr.org> parent: Marc G. Fournier <scrappy@postgresql.org> 1 sibling, 0 replies; 3+ messages in thread From: Michael Fuhr @ 2005-08-16 05:30 UTC (permalink / raw) To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql On Tue, Aug 16, 2005 at 01:54:13AM -0300, Marc G. Fournier wrote: > > CREATE TABLE customers ( > customer_id SERIAL, > monthly_balance DECIMAL(7,2)[12] > ); > > Is it possible to create an INDEX on customers.monthly_balance such that I > could do something like: > > SELECT * FROM customers WHERE monthly_balance[6] = 0.00; You could use expression indexes, one per month: CREATE INDEX customers_mb_1_idx ON customers ((monthly_balance[1])); CREATE INDEX customers_mb_2_idx ON customers ((monthly_balance[2])); etc. > SELECT * FROM customers WHERE 0.00 = any (monthly_balance); Not sure about that one. -- Michael Fuhr ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ARRAYs and INDEXes ... @ 2005-08-16 14:59 Greg Stark <gsstark@mit.edu> parent: Marc G. Fournier <scrappy@postgresql.org> 1 sibling, 0 replies; 3+ messages in thread From: Greg Stark @ 2005-08-16 14:59 UTC (permalink / raw) To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql "Marc G. Fournier" <scrappy@postgresql.org> writes: > SELECT * FROM customers WHERE monthly_balance[6] = 0.00; This, like the other poster said, can be accomplished with a set of simple expression indexes. > As an example ... or > > SELECT * FROM customers WHERE 0.00 = any (monthly_balance); This would require a GiST index. Look at the intarray contrib module. I don't think there's any equivalent for other data types. You might have to store these values as fixed precision numbers and divide or multiple by 100 to convert. -- greg ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-16 14:59 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-08-16 04:54 ARRAYs and INDEXes ... Marc G. Fournier <scrappy@postgresql.org> 2005-08-16 05:30 ` Michael Fuhr <mike@fuhr.org> 2005-08-16 14:59 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox