Thread
-
create index with substr function
Ray <ray_siu@ge-ts.com.hk> — 2004-10-21T02:25:17Z
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 -
Re: create index with substr function
Stephan Szabo <sszabo@megazone.bigpanda.com> — 2004-10-21T02:57:54Z
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
Tom Lane <tgl@sss.pgh.pa.us> — 2004-10-21T02:59:14Z
"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
Ray <ray_siu@ge-ts.com.hk> — 2004-10-21T03:11:05Z
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
Rosser Schwarz <rosser.schwarz@gmail.com> — 2004-10-21T03:34:55Z
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
Ray <ray_siu@ge-ts.com.hk> — 2004-10-21T03:37:26Z
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
Joshua D. Drake <jd@commandprompt.com> — 2004-10-21T06:13:32Z
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
george young <gry@ll.mit.edu> — 2004-10-21T14:22:37Z
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)