X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AA725329EC2 for ; Mon, 11 Oct 2004 23:16:08 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87948-03 for ; Mon, 11 Oct 2004 22:15:57 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.194]) by svr1.postgresql.org (Postfix) with ESMTP id C974C329C89 for ; Mon, 11 Oct 2004 23:15:59 +0100 (BST) Received: by mproxy.gmail.com with SMTP id 74so351248rnk for ; Mon, 11 Oct 2004 15:15:42 -0700 (PDT) Received: by 10.38.165.11 with SMTP id n11mr1522496rne; Mon, 11 Oct 2004 15:15:42 -0700 (PDT) Received: by 10.38.76.43 with HTTP; Mon, 11 Oct 2004 15:15:41 -0700 (PDT) Message-ID: <37d451f704101115152fa2acc8@mail.gmail.com> Date: Mon, 11 Oct 2004 17:15:41 -0500 From: Rosser Schwarz Reply-To: Rosser Schwarz To: pgsql-admin@postgresql.org Subject: indices and casts Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/160 X-Sequence-Number: 15153 I have a number of timestamp columns on various tables I'd like also to be able to query against as dates. I can easily enough say "SELECT * FROM foo WHERE bar::date = '2004-10-11'" and the like, but such queries are inevitably sequential scans, and the tables are rather large for that to be as performant as I'd like. Is it possible to do something functionally equivalent to "CREATE INDEX name ON table USING btree(column::date)"? That statement yields a syntax error on the "::". Trying to create the index using the date_ops opclass yields an error to the effect that date_ops don't work on timestamp values. The only alternatives I can see are: to issue queries of the form "SELECT foo FROM bar WHERE baz BETWEEN '2004-10-11' AND '2004-10-12'", which is do-able, since most of our SQL is generated, but awkward and prone to being forgotten in the odd case where it's not generated; or adding another column of type date, which seems awkward and prone to causing confusion between the date and timestamp columns. What am I missing (alternatively, what am I smoking)? 7.4.2 on RH9, if it helps. /rls -- :wq