From: | Fred Janon <fjanon(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Fwd: How to create a multi-column index with 2 dates using 'gist'? |
Date: | 2009-08-25 09:29:47 |
Message-ID: | 2fd0c7810908250229h51538b5dv43cf4c1dc04c4115@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-performance |
Asking the Performance people as well, since I didn't get any answer from
General...
I have been unable to create a multi column index with 2 integers as well,
same error as the one I get with 2 dates.
Thanks
Fred
---------- Forwarded message ----------
From: Fred Janon <fjanon(at)gmail(dot)com>
Date: Mon, Aug 24, 2009 at 17:24
Subject: How to create a multi-column index with 2 dates using 'gist'?
To: pgsql-general(at)postgresql(dot)org
Hi,
I am using 8.3 and pgAdmin III. I have a couple of tables using 2 DATE
columns like 'startdate' and 'enddate' (just date, not interested in time in
these columns). I have some queries (some using OVERLAPS) involving both
'startdate' and 'enddate' columns. I tried to create a multi column index
using pgAdmin and it comes back with this error:
ERROR: data type date has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.
I search the pdf docs and online without finding what an "operator class"
for DATE would be. Would a multi-column index help in that case (OVERLAPS
and dates comparison) anyway? Or should I just define an index for each of
the dates?
Below are the table and index defintions.
Thanks
Fred
---------------------------------------------
CREATE INDEX startenddate
ON times USING gist (startdate, enddate);
---------------------------------------------
-- Table: times
-- DROP TABLE times;
CREATE TABLE times
(
id serial NOT NULL,
startdate date NOT NULL,
enddate date NOT NULL,
starttime time without time zone,
endtime time without time zone,
CONSTRAINT pk_id PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE times OWNER TO postgres;
GRANT ALL ON TABLE times TO postgres;
GRANT ALL ON TABLE times TO public;
From | Date | Subject | |
---|---|---|---|
Next Message | Rstat | 2009-08-25 09:50:52 | ETL software and training |
Previous Message | Craig Ringer | 2009-08-25 02:42:50 | Re: How to simulate crashes of PostgreSQL? |
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2009-08-25 10:52:11 | Re: How to create a multi-column index with 2 dates using 'gist'? |
Previous Message | Gavin Love | 2009-08-24 17:46:59 | Re: Indexing on a circle datatype |