How to create a multi-column index with 2 dates using 'gist'?
От | Fred Janon |
---|---|
Тема | How to create a multi-column index with 2 dates using 'gist'? |
Дата | |
Msg-id | 2fd0c7810908240224h58478784ld475371ab5db414b@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: How to create a multi-column index with 2 dates using 'gist'?
Re: How to create a multi-column index with 2 dates using 'gist'? |
Список | pgsql-general |
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;
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;
В списке pgsql-general по дате отправления: