Home > mailing lists

Thread: create index with substr function

create index with substr function

From

"Ray"

Date:

21 October 2004, 06:25:25

Hi All,

I have a table in my postgres:

Table: doc

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

Re: create index with substr function

From

Tom Lane

Date:

21 October 2004, 09:11:40

"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

Re: create index with substr function

From

Stephan Szabo

Date:

21 October 2004, 09:12:22

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.

Re: create index with substr function

From

"Ray"

Date:

21 October 2004, 09:12:22

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

Re: create index with substr function

From

Rosser Schwarz

Date:

21 October 2004, 09:44:53

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

Re: create index with substr function

From

"Ray"

Date:

21 October 2004, 09:45:24

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
>

Re: create index with substr function

From

"Joshua D. Drake"

Date:

21 October 2004, 10:19:05

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

Re: create index with substr function

From

george young

Date:

21 October 2004, 18:23:31

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)