pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
create index with substr function
8+ messages / 6 participants
[nested] [flat]

* create index with substr function
@ 2004-10-21 02:25 Ray <ray_siu@ge-ts.com.hk>
  2004-10-21 02:57 ` Re: create index with substr function Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-10-21 02:59 ` Re: create index with substr function Tom Lane <tgl@sss.pgh.pa.us>
  2004-10-21 03:34 ` Re: create index with substr function Rosser Schwarz <rosser.schwarz@gmail.com>
  0 siblings, 3 replies; 8+ messages in thread

From: Ray @ 2004-10-21 02:25 UTC (permalink / raw)
  To: pgsql-performance

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.

Thanks
Ray=

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: create index with substr function
  2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
@ 2004-10-21 02:57 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-10-21 03:11   ` Re: create index with substr function Ray <ray_siu@ge-ts.com.hk>
  2 siblings, 1 reply; 8+ messages in thread

From: Stephan Szabo @ 2004-10-21 02:57 UTC (permalink / raw)
  To: Ray <ray_siu@ge-ts.com.hk>; +Cc: pgsql-performance


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.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: create index with substr function
  2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
  2004-10-21 02:57 ` Re: create index with substr function Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-10-21 03:11   ` Ray <ray_siu@ge-ts.com.hk>
  0 siblings, 0 replies; 8+ messages in thread

From: Ray @ 2004-10-21 03:11 UTC (permalink / raw)
  To: pgsql-performance

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" <sszabo@megazone.bigpanda.com>
To: "Ray" <ray_siu@ge-ts.com.hk>
Cc: <pgsql-performance@postgresql.org>
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.
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: create index with substr function
  2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
@ 2004-10-21 02:59 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-10-21 06:13   ` Re: create index with substr function Joshua D. Drake <jd@commandprompt.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2004-10-21 02:59 UTC (permalink / raw)
  To: Ray <ray_siu@ge-ts.com.hk>; +Cc: pgsql-performance

"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.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: create index with substr function
  2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
  2004-10-21 02:59 ` Re: create index with substr function Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-21 06:13   ` Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Joshua D. Drake @ 2004-10-21 06:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ray <ray_siu@ge-ts.com.hk>; pgsql-performance

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: create index with substr function
  2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
@ 2004-10-21 03:34 ` Rosser Schwarz <rosser.schwarz@gmail.com>
  2004-10-21 03:37   ` Re: create index with substr function Ray <ray_siu@ge-ts.com.hk>
  2 siblings, 1 reply; 8+ messages in thread

From: Rosser Schwarz @ 2004-10-21 03:34 UTC (permalink / raw)
  To: Ray <ray_siu@ge-ts.com.hk>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: create index with substr function
  2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
  2004-10-21 03:34 ` Re: create index with substr function Rosser Schwarz <rosser.schwarz@gmail.com>
@ 2004-10-21 03:37   ` Ray <ray_siu@ge-ts.com.hk>
  2004-10-21 14:22     ` Re: create index with substr function george young <gry@ll.mit.edu>
  0 siblings, 1 reply; 8+ messages in thread

From: Ray @ 2004-10-21 03:37 UTC (permalink / raw)
  To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: pgsql-performance

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" <rosser.schwarz@gmail.com>
To: "Ray" <ray_siu@ge-ts.com.hk>
Cc: <pgsql-performance@postgresql.org>
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
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: create index with substr function
  2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
  2004-10-21 03:34 ` Re: create index with substr function Rosser Schwarz <rosser.schwarz@gmail.com>
  2004-10-21 03:37   ` Re: create index with substr function Ray <ray_siu@ge-ts.com.hk>
@ 2004-10-21 14:22     ` george young <gry@ll.mit.edu>
  0 siblings, 0 replies; 8+ messages in thread

From: george young @ 2004-10-21 14:22 UTC (permalink / raw)
  To: Ray <ray_siu@ge-ts.com.hk>; +Cc: pgsql-performance

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" <ray_siu@ge-ts.com.hk> 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" <rosser.schwarz@gmail.com>
> To: "Ray" <ray_siu@ge-ts.com.hk>
> Cc: <pgsql-performance@postgresql.org>
> 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)



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-10-21 14:22 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-21 02:25 create index with substr function Ray <ray_siu@ge-ts.com.hk>
2004-10-21 02:57 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-10-21 03:11   ` Ray <ray_siu@ge-ts.com.hk>
2004-10-21 02:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-21 06:13   ` Joshua D. Drake <jd@commandprompt.com>
2004-10-21 03:34 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2004-10-21 03:37   ` Ray <ray_siu@ge-ts.com.hk>
2004-10-21 14:22     ` george young <gry@ll.mit.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox