From ray_siu@ge-ts.com.hk Thu Oct 21 02:25:24 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B35CC32ADC5 for ; Thu, 21 Oct 2004 03:25:24 +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 07802-02 for ; Thu, 21 Oct 2004 02:25:16 +0000 (GMT) Received: from mail.ge-ts.com.hk (unknown [202.66.115.131]) by svr1.postgresql.org (Postfix) with ESMTP id 5B05932A4B0 for ; Thu, 21 Oct 2004 03:25:17 +0100 (BST) Received: from raysiu ([172.16.96.15]) by mail.ge-ts.com.hk (8.12.8/8.12.8) with SMTP id i9L2PGCh013233 for ; Thu, 21 Oct 2004 10:25:17 +0800 Message-ID: <069201c4b715$34851670$0f6010ac@raysiu> From: "Ray" To: Subject: create index with substr function Date: Thu, 21 Oct 2004 10:25:17 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_068F_01C4B758.4260C620" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200410/333 X-Sequence-Number: 8809 This is a multi-part message in MIME format. ------=_NextPart_000_068F_01C4B758.4260C620 Content-Type: text/plain; charset="big5" Content-Transfer-Encoding: quoted-printable Hi All, I have a table in my postgres: Table: doc Column | Type | Modifiers=20 ---------------+-----------------------------+----------- doc_id | bigint | not null comp_grp_id | bigint | not null doc_type | character varying(10)| not null doc_urn | character varying(20)| not null I want to create an index on doc_urn column with using substr function like= this: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); but there is an error: ERROR: parser: parse error at or near "10" at character 68 what's wrong for this SQL? As I have found some reference on the internet, = I can't find anything wrong in this SQL. Thanks Ray= ------=_NextPart_000_068F_01C4B758.4260C620 Content-Type: text/html; charset="big5" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I have a table in my postgres:
Table: doc
    =20 Column    =20 |           =20 Type            = ; |=20 Modifiers
    =20 ---------------+-----------------------------+-----------
 doc_id&n= bsp;         |=20 bigint           &nb= sp;         =20 | not null
 comp_grp_id |=20 bigint           &nb= sp;         =20 | not null
 doc_type      | character= =20 varying(10)| not null
 doc_urn      &= nbsp;=20 | character varying(20)| not null
I want to create an index on doc_urn colum= n with=20 using substr function like this:
CREATE INDEX idx_doc_substr_doc_urn ON doc= USING=20 btree (SUBSTR(doc_urn,10));
 
but there is an error:

ERROR:  parser: parse error at or near "10" at character 68
 
what's wrong for this SQL? As I have found some reference on the inter= net,=20 I can't find anything wrong in this SQL.
 
Thanks
Ray
------=_NextPart_000_068F_01C4B758.4260C620-- From sszabo@megazone.bigpanda.com Thu Oct 21 05:12:20 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7757D32B1E5 for ; Thu, 21 Oct 2004 06:12:20 +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 61307-02 for ; Thu, 21 Oct 2004 05:12:16 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id 4C3FB32B18C for ; Thu, 21 Oct 2004 06:11:39 +0100 (BST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr4.postgresql.org (Postfix) with ESMTP id 857515AF563 for ; Thu, 21 Oct 2004 03:05:30 +0000 (GMT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 2B4F235B1F; Wed, 20 Oct 2004 19:57:54 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 29D1C35AF8; Wed, 20 Oct 2004 19:57:54 -0700 (PDT) Date: Wed, 20 Oct 2004 19:57:54 -0700 (PDT) From: Stephan Szabo To: Ray Cc: pgsql-performance@postgresql.org Subject: Re: create index with substr function In-Reply-To: <069201c4b715$34851670$0f6010ac@raysiu> Message-ID: <20041020195604.T32588@megazone.bigpanda.com> References: <069201c4b715$34851670$0f6010ac@raysiu> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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/335 X-Sequence-Number: 8811 On Thu, 21 Oct 2004, Ray wrote: > Hi All, > > I have a table in my postgres: > Table: doc > Column | Type | Modifiers > ---------------+-----------------------------+----------- > doc_id | bigint | not null > comp_grp_id | bigint | not null > doc_type | character varying(10)| not null > doc_urn | character varying(20)| not null > > I want to create an index on doc_urn column with using substr function like this: > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); > > but there is an error: > > ERROR: parser: parse error at or near "10" at character 68 > > what's wrong for this SQL? As I have found some reference on the > internet, I can't find anything wrong in this SQL. What version are you using? If you're using anything previous to 7.4 then the above definately won't work and the only work around I know of is to make another function which takes only the column argument and calls substr with the 10 constant. From tgl@sss.pgh.pa.us Thu Oct 21 05:11:38 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4393632B10F for ; Thu, 21 Oct 2004 06:11:38 +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 58206-07 for ; Thu, 21 Oct 2004 05:11:35 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id 3C14C32B0DF for ; Thu, 21 Oct 2004 06:11:31 +0100 (BST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr4.postgresql.org (Postfix) with ESMTP id 119D65AF423 for ; Thu, 21 Oct 2004 03:01:49 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9L2xEMF010775; Wed, 20 Oct 2004 22:59:14 -0400 (EDT) To: "Ray" Cc: pgsql-performance@postgresql.org Subject: Re: create index with substr function In-reply-to: <069201c4b715$34851670$0f6010ac@raysiu> References: <069201c4b715$34851670$0f6010ac@raysiu> Comments: In-reply-to "Ray" message dated "Thu, 21 Oct 2004 10:25:17 +0800" Date: Wed, 20 Oct 2004 22:59:14 -0400 Message-ID: <10774.1098327554@sss.pgh.pa.us> From: Tom Lane 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/334 X-Sequence-Number: 8810 "Ray" writes: > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); > ERROR: parser: parse error at or near "10" at character 68 This will work in 7.4, but not older releases. regards, tom lane From ray_siu@ge-ts.com.hk Thu Oct 21 05:12:21 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 44D0432B1EA for ; Thu, 21 Oct 2004 06:12:21 +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 61057-03 for ; Thu, 21 Oct 2004 05:12:15 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id 3769E32B1FB for ; Thu, 21 Oct 2004 06:11:41 +0100 (BST) Received: from mail.ge-ts.com.hk (unknown [202.66.115.131]) by svr4.postgresql.org (Postfix) with ESMTP id 9E8135AF5FC for ; Thu, 21 Oct 2004 03:13:08 +0000 (GMT) Received: from raysiu ([172.16.96.15]) by mail.ge-ts.com.hk (8.12.8/8.12.8) with SMTP id i9L3B5Ch013400 for ; Thu, 21 Oct 2004 11:11:05 +0800 Message-ID: <06e201c4b71b$9ae9cc70$0f6010ac@raysiu> From: "Ray" To: References: <069201c4b715$34851670$0f6010ac@raysiu> <20041020195604.T32588@megazone.bigpanda.com> Subject: Re: create index with substr function Date: Thu, 21 Oct 2004 11:11:05 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 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/336 X-Sequence-Number: 8812 Thank you all kindly response..... : ) I am currently using postgres 7.3, so any example or solution for version after 7.4 if i want to create an index with substr function??? Thanks, Ray ----- Original Message ----- From: "Stephan Szabo" To: "Ray" Cc: Sent: Thursday, October 21, 2004 10:57 AM Subject: Re: [PERFORM] create index with substr function > > On Thu, 21 Oct 2004, Ray wrote: > > > Hi All, > > > > I have a table in my postgres: > > Table: doc > > Column | Type | Modifiers > > ---------------+-----------------------------+----------- > > doc_id | bigint | not null > > comp_grp_id | bigint | not null > > doc_type | character varying(10)| not null > > doc_urn | character varying(20)| not null > > > > I want to create an index on doc_urn column with using substr function like this: > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); > > > > but there is an error: > > > > ERROR: parser: parse error at or near "10" at character 68 > > > > what's wrong for this SQL? As I have found some reference on the > > internet, I can't find anything wrong in this SQL. > > What version are you using? If you're using anything previous to 7.4 then > the above definately won't work and the only work around I know of is to > make another function which takes only the column argument and calls > substr with the 10 constant. > From rosser.schwarz@gmail.com Thu Oct 21 05:44:51 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B6FFF32AE15 for ; Thu, 21 Oct 2004 06:44:51 +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 70432-03 for ; Thu, 21 Oct 2004 05:44:48 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id EFCF932A7AD for ; Thu, 21 Oct 2004 06:44:48 +0100 (BST) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.203]) by svr4.postgresql.org (Postfix) with ESMTP id 081775AF51C for ; Thu, 21 Oct 2004 03:36:55 +0000 (GMT) Received: by mproxy.gmail.com with SMTP id 73so586619rnk for ; Wed, 20 Oct 2004 20:34:55 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=fMzchdGnZQLA9E1hUQqXxc18YROGwM8N3BApSyw2dQh6JOQNwBUPc/WY9wj2HPlq0ZL6ITUORNQ01zbjvnU9eM+jaQbm8KgSstH0vWquZb+XrJQ8zC/39P57MW3d1i57LuDsROQjoMDTfRuKJ+LrqVxT9vkN2r8egqjnWlkrWsg Received: by 10.39.1.26 with SMTP id d26mr2874209rni; Wed, 20 Oct 2004 20:34:55 -0700 (PDT) Received: by 10.38.76.43 with HTTP; Wed, 20 Oct 2004 20:34:55 -0700 (PDT) Message-ID: <37d451f704102020346fdbb855@mail.gmail.com> Date: Wed, 20 Oct 2004 22:34:55 -0500 From: Rosser Schwarz Reply-To: Rosser Schwarz To: Ray Subject: Re: create index with substr function Cc: pgsql-performance@postgresql.org In-Reply-To: <069201c4b715$34851670$0f6010ac@raysiu> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <069201c4b715$34851670$0f6010ac@raysiu> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200410/337 X-Sequence-Number: 8813 while you weren't looking, Ray wrote: > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree ((SUBSTR(doc_urn,10))); You need an additional set of parens around the SUBSTR() call. /rls -- :wq From ray_siu@ge-ts.com.hk Thu Oct 21 05:45:14 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DA1AC32AE59 for ; Thu, 21 Oct 2004 06:45:14 +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 71154-02 for ; Thu, 21 Oct 2004 05:45:09 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id EAA3E32AE41 for ; Thu, 21 Oct 2004 06:44:56 +0100 (BST) Received: from mail.ge-ts.com.hk (unknown [202.66.115.131]) by svr4.postgresql.org (Postfix) with ESMTP id B08555AF5D5 for ; Thu, 21 Oct 2004 03:40:56 +0000 (GMT) Received: from raysiu ([172.16.96.15]) by mail.ge-ts.com.hk (8.12.8/8.12.8) with SMTP id i9L3bPCh013589; Thu, 21 Oct 2004 11:37:26 +0800 Message-ID: <078b01c4b71f$4936e4e0$0f6010ac@raysiu> From: "Ray" To: "Rosser Schwarz" Cc: References: <069201c4b715$34851670$0f6010ac@raysiu> <37d451f704102020346fdbb855@mail.gmail.com> Subject: Re: create index with substr function Date: Thu, 21 Oct 2004 11:37:26 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 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/338 X-Sequence-Number: 8814 sorry it doesn't works, as my postgres is 7.3 not 7.4. any other alternative solution for version after 7.4?? Thank Ray : ) ----- Original Message ----- From: "Rosser Schwarz" To: "Ray" Cc: Sent: Thursday, October 21, 2004 11:34 AM Subject: Re: [PERFORM] create index with substr function > while you weren't looking, Ray wrote: > > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree ((SUBSTR(doc_urn,10))); > > You need an additional set of parens around the SUBSTR() call. > > /rls > > -- > :wq > From jd@commandprompt.com Thu Oct 21 06:19:04 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6E5E832AD91 for ; Thu, 21 Oct 2004 07:19:04 +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 80409-02 for ; Thu, 21 Oct 2004 06:18:59 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id DE07132A849 for ; Thu, 21 Oct 2004 07:18:59 +0100 (BST) Received: from [192.168.1.20] (clbb-248.saw.net [64.146.135.248]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i9L6FRD07936; Wed, 20 Oct 2004 23:15:27 -0700 Message-ID: <4177538C.7010900@commandprompt.com> Date: Wed, 20 Oct 2004 23:13:32 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Ray , pgsql-performance@postgresql.org Subject: Re: create index with substr function References: <069201c4b715$34851670$0f6010ac@raysiu> <10774.1098327554@sss.pgh.pa.us> In-Reply-To: <10774.1098327554@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------080507080403000504090501" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200410/339 X-Sequence-Number: 8815 This is a multi-part message in MIME format. --------------080507080403000504090501 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >"Ray" writes: > > >>CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); >>ERROR: parser: parse error at or near "10" at character 68 >> >> > >This will work in 7.4, but not older releases. > > > Can't you just use a SQL function that calls the substr function? I have done that with date functions before like: CREATE OR REPLACE FUNCTION get_month(text) returns double precision AS ' SELECT date_part('month',$1); ' LANGUAGE 'SQL' IMMUTABLE; CREATE INDEX get_month_idx on foo(get_month(date_field)); Or in this case: CREATE OR REPLACE FUNCTION sub_text(text) returns text AS ' SELECT SUBSTR($1,10) from foo; ' LANGUAGE 'SQL' IMMUTABLE; CREATE INDEX sub_text_idx ON foo(sub_text(doc_urn)); This works on 7.3.6??? Sincerely, Joshua D. Drake > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL --------------080507080403000504090501 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:
"Ray" <ray_siu@ge-ts.com.hk> writes:
  
CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10));
ERROR:  parser: parse error at or near "10" at character 68
    

This will work in 7.4, but not older releases.

  
Can't you just use a SQL function that calls the substr function? I have done that with date functions before
like:
CREATE OR REPLACE FUNCTION get_month(text) returns double precision AS '
   SELECT date_part('month',$1);
' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX get_month_idx on foo(get_month(date_field));
Or in this case:

CREATE OR REPLACE FUNCTION sub_text(text) returns text AS '
      SELECT SUBSTR($1,10) from foo;
' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX sub_text_idx ON foo(sub_text(doc_urn));

This works on 7.3.6???

Sincerely,

Joshua D. Drake




			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
  


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
--------------080507080403000504090501-- From gry@ll.mit.edu Thu Oct 21 14:23:29 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B34A332A219 for ; Thu, 21 Oct 2004 15:23:29 +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 33848-04 for ; Thu, 21 Oct 2004 14:23:23 +0000 (GMT) Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40]) by svr1.postgresql.org (Postfix) with ESMTP id 2E72732A263 for ; Thu, 21 Oct 2004 15:23:20 +0100 (BST) Received: (from smtp@localhost) by ll.mit.edu (8.12.10/8.8.8) id i9LEMxRs000661; Thu, 21 Oct 2004 10:22:59 -0400 (EDT) Received: from sty.llan.ll.mit.edu( ), claiming to be "sty.llan" via SMTP by llpost, id smtpdAAA8mayq6; Thu Oct 21 10:22:37 2004 Date: Thu, 21 Oct 2004 10:22:37 -0400 From: george young To: "Ray" Cc: pgsql-performance@postgresql.org Subject: Re: create index with substr function Message-Id: <20041021102237.5ae1d14e.gry@ll.mit.edu> In-Reply-To: <078b01c4b71f$4936e4e0$0f6010ac@raysiu> References: <069201c4b715$34851670$0f6010ac@raysiu> <37d451f704102020346fdbb855@mail.gmail.com> <078b01c4b71f$4936e4e0$0f6010ac@raysiu> Reply-To: gry@ll.mit.edu Organization: MIT Lincoln Laboratory X-Mailer: Sylpheed version 0.9.10 (GTK+ 1.2.10; i686-pc-linux-gnu) 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/352 X-Sequence-Number: 8828 As previously suggested by Stephan Szabo, you need to create a helper function, e.g.: create or replace function after9(text)returns text language plpgsql immutable as ' begin return substr($1, 10); end; '; You may need the "immutable" specification is to allow the function's use in an index. Then use this function in the index creation: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (after9(doc_urn)); I think that should do it. -- George > On Thu, 21 Oct 2004 11:37:26 +0800 "Ray" threw this fish to the penguins: > sorry it doesn't works, as my postgres is 7.3 not 7.4. any other alternative > solution for version after 7.4?? > > Thank > Ray : ) > > ----- Original Message ----- > From: "Rosser Schwarz" > To: "Ray" > Cc: > Sent: Thursday, October 21, 2004 11:34 AM > Subject: Re: [PERFORM] create index with substr function > > > > while you weren't looking, Ray wrote: > > > > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree > (SUBSTR(doc_urn,10)); > > > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree > ((SUBSTR(doc_urn,10))); > > > > You need an additional set of parens around the SUBSTR() call. > > > > /rls > > > > -- > > :wq > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL)