Received: from guerilla.foo.bar (qmailr@hennen32s.iserlohn.netsurf.de [194.195.194.226]) by hub.org (8.9.2/8.9.1) with SMTP id HAA81290 for ; Sat, 6 Feb 1999 07:48:30 -0500 (EST) (envelope-from sas@schell.de) Received: (qmail 784 invoked by uid 1000); 6 Feb 1999 12:49:34 -0000 Date: Sat, 6 Feb 1999 13:49:34 +0100 From: Sascha Schumann To: Remigiusz Sokolowski Cc: pgsql-sql@hub.org Subject: Re: [SQL] index on aggregate function Message-ID: <19990206134934.A703@schell.de> References: <19990130162017.A3151@schell.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.95.1i In-Reply-To: ; from Remigiusz Sokolowski on Mon, Feb 01, 1999 at 09:50:57AM +0100 X-Operating-System: Linux guerilla 2.2.1 On Mon, Feb 01, 1999 at 09:50:57AM +0100, Remigiusz Sokolowski wrote: > > I somewhere read that it is possible to create an index on aggregate functions > > in PostgreSQL. Which syntax do I have to use for it? > > > > test=> CREATE TABLE test ( name VARCHAR(32) ); > > CREATE > > test=> CREATE INDEX test_idx ON test lower(name); > > ERROR: parser: parse error at or near "lower" > > > > gives me a parser error. > > Right syntax is > CREATE INDEX test_idx ON test (lower(name) text_ops); > I try it and index was generated without error > Rem Well, that exact statement gives me: ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist I also tried varchar_ops and char_ops instead of text_ops, but that does not resolve the problem. I'm using PostgreSQL 6.4.2 on Linux without problems so far. -- Regards, Sascha Schumann | Consultant | finger sas@schell.de | for PGP public key