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 <fja...@gmail.com> Date: Mon, Aug 24, 2009 at 17:24 Subject: How to create a multi-column index with 2 dates using 'gist'? To: pgsql-gene...@postgresql.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;
