pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate 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>
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:57 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: 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:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ray <ray_siu@ge-ts.com.hk>
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 03:11 Ray <ray_siu@ge-ts.com.hk>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
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 03:34 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: 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 03:37 Ray <ray_siu@ge-ts.com.hk>
parent: Rosser Schwarz <rosser.schwarz@gmail.com>
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 06:13 Joshua D. Drake <jd@commandprompt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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 14:22 george young <gry@ll.mit.edu>
parent: Ray <ray_siu@ge-ts.com.hk>
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