Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.2/8.9.1) with ESMTP id IAA08926 for ; Mon, 15 Feb 1999 08:30:37 -0500 (EST) (envelope-from sferac@bo.nettuno.it) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id OAA13788; Mon, 15 Feb 1999 14:29:15 +0100 (MET) Received: from rosso.sferacarta.com (bo.nettuno.it) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10CNmf-0000tW-00; Mon, 15 Feb 1999 13:09:57 +0000 Message-ID: <36C80E1F.D9202F72@bo.nettuno.it> Date: Mon, 15 Feb 1999 13:07:59 +0100 From: "jose' soares" Organization: Sferacarta Software X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Remigiusz Sokolowski CC: Sascha Schumann , pgsql-sql@hub.org Subject: Re: [SQL] index on aggregate function References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Remigiusz Sokolowski ha scritto: > > > > 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. > > I have holidays and now have to read so much mails, that I can't find if > there were any other suggestions about that. AFAIK there is no lower() > function for varchar type and this is a problem - solution is to use text > type or create function lower() for varchar type. > Rem > > -------------------------------------------------------------------*------------ > Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * * > -----------------------------------------------------------------*****---------- --lower() works with varchar... prova=> CREATE TABLE test ( name VARCHAR(32) ); CREATE prova=> insert into test values ('AAAA'); INSERT 188475 1 prova=> select lower(name) from test; lower ----- aaaa (1 row) but it doesn't work on create index... prova=> drop table test; DROP prova=> CREATE TABLE test ( name VARCHAR(32) ); CREATE prova=> CREATE INDEX test_idx ON test lower(nome); ERROR: parser: parse error at or near "lower" -- - Jose' - And behold, I tell you these things that ye may learn wisdom; that ye may learn that when ye are in the service of your fellow beings ye are only in the service of your God. - Mosiah 2:17 -