Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.2/8.9.1) with ESMTP id WAA35053 for ; Sun, 7 Feb 1999 22:19:42 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id WAA13892; Sun, 7 Feb 1999 22:18:36 -0500 (EST) To: Marc Howard Zuckman cc: pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org Subject: Re: [SQL] Functional Indexes In-reply-to: Your message of Sun, 7 Feb 1999 21:42:23 -0500 (EST) Date: Sun, 07 Feb 1999 22:18:35 -0500 Message-ID: <13890.918443915@sss.pgh.pa.us> From: Tom Lane Marc Howard Zuckman writes: > On Mon, 8 Feb 1999, Sascha Schumann wrote: >> funweb=> create unique index userdat_idx2 on userdat (lower(username) >> varchar_ops); >> ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist >> >> This error message looks very bogus to me. > I don't think lower is defined for varchar arguments. consider redefining > username as type text and using text_ops. I think Marc is right. Someone was working on adding lower() to the available ops for varchar for 6.5, but it's not there in 6.4. You can get lower() to work on varchar source data in a simple SELECT, but that's some sort of hack that involves the system knowing that text and varchar have the same physical representation so it's OK to use a function that takes text on a varchar column. The type matching requirements for functional indexes are tighter. Note to hackers: is there a good reason why indexes are more restrictive? Offhand it seems like the same physical-equivalence trick could be applied. regards, tom lane