Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 396CD9FB2D0 for ; Thu, 7 Jun 2007 01:34:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 87003-03 for ; Thu, 7 Jun 2007 01:34:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id D72C29FB2E4 for ; Thu, 7 Jun 2007 01:34:45 -0300 (ADT) Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l574Yl9I001277; Wed, 6 Jun 2007 21:34:47 -0700 Message-ID: <46678B1B.4050303@commandprompt.com> Date: Wed, 06 Jun 2007 21:35:39 -0700 From: "Joshua D. Drake" User-Agent: Thunderbird 1.5.0.10 (X11/20070403) MIME-Version: 1.0 To: Chris Hoover CC: Tom Lane , Alvaro Herrera , "pgsql-admin@postgresql.org Admin" Subject: Re: 8.2.4 Won't Build 8.1 Functional Indexes References: <1d219a6f0706061112p355fc6cexb30a6b4b4bf187c3@mail.gmail.com> <20070606195459.GH11800@alvh.no-ip.org> <17138.1181173286@sss.pgh.pa.us> <1d219a6f0706061916k73deaa6bv2dd9e800e77a6f24@mail.gmail.com> In-Reply-To: <1d219a6f0706061916k73deaa6bv2dd9e800e77a6f24@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Wed, 06 Jun 2007 21:34:47 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/60 X-Sequence-Number: 25728 Chris Hoover wrote: > Well, the one index: > CREATE INDEX acceptedbilling_to_date > The second index is rather stupid, it was an early index before I > figured out how to split a timestamp. > > Anyway, is there a way to make the first index work? Otherwise we end > up with a seq scan on our billing table which is very painful. First, please do not top post: http://www.catb.org/jargon/html/T/top-post.html Second: create function mydate(varchar) returns varchar AS $$ select to_char($1::date,'YYYYMMDD'); $$ LANGUAGE SQL IMMUTABLE; create index foobar on date_test(mydate(date)); You will need to test this, but it does allow you to create the index. Sincerely, Joshua D. Drake > > Thanks, > > Chris > > > On 6/6/07, *Tom Lane* > wrote: > > Alvaro Herrera > writes: > > You can't do this because to_date and other functions are not > immutable. > > 8.2 seems to be more picky about this -- the date conversions of > > timestamptz columns are dependent on the current timezone. > > The reason 8.2 is more picky is that the function is less immutable > thanks to the addition of locale-dependent functionality: > http://archives.postgresql.org/pgsql-committers/2006-11/msg00264.php > > I gather that the underlying column is timestamp without tz, or it would > never have worked in 8.1 either. That being the case, these index > definitions seem pretty darn stupid anyway --- why aren't you just > indexing on date_trunc or a plain cast to date? > > regards, tom lane > > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/