pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctional index and string concatenation
5+ messages / 4 participants
[nested] [flat]
* Functional index and string concatenation
@ 2004-03-08 15:19 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 1 reply; 5+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-08 15:19 UTC (permalink / raw)
To: pgsql-sql
Is there a way to create a "functional index" over a string
concatenation of two columns ?
Thanks.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Functional index and string concatenation
@ 2004-03-08 15:28 Edmund Bacon <ebacon@onesystem.com>
parent: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 2 replies; 5+ messages in thread
From: Edmund Bacon @ 2004-03-08 15:28 UTC (permalink / raw)
To: pgsql-sql
On Mon, 2004-03-08 at 08:19, Daniel Henrique Alves Lima wrote:
> Is there a way to create a "functional index" over a string
> concatenation of two columns ?
>
> Thanks.
>
Like this?
test=# create table strtable (x int, str1 text, str2 text);
CREATE TABLE
test=# create index str_idx on strtable( textcat(str1, str2) );
CREATE INDEX
test=#
--
Edmund Bacon <ebacon@onesystem.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Functional index and string concatenation
@ 2004-03-08 15:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Edmund Bacon <ebacon@onesystem.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-03-08 15:48 UTC (permalink / raw)
To: Edmund Bacon <ebacon@onesystem.com>; +Cc: pgsql-sql
Edmund Bacon <ebacon@onesystem.com> writes:
> On Mon, 2004-03-08 at 08:19, Daniel Henrique Alves Lima wrote:
>> Is there a way to create a "functional index" over a string
>> concatenation of two columns ?
> test=# create index str_idx on strtable( textcat(str1, str2) );
As of 7.4 you can do it more directly:
create index str_idx on strtable( (str1 || str2) );
The disadvantage of the textcat() locution is that the planner will only
match it up to queries that also say textcat().
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Functional index and string concatenation
@ 2004-03-08 16:02 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
parent: Edmund Bacon <ebacon@onesystem.com>
1 sibling, 1 reply; 5+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-08 16:02 UTC (permalink / raw)
To: pgsql-sql
Yes, i was searching for the name of equivalent function to "||"
operator...
Now, i've got a new problem: I need to concat three values (two
column values and a sepator constant). But from documentation: " The
function in the index definition can take more than one argument, but
they must be table columns, not constants" and "there cannot be
multicolumn indexes that contain function calls".
Maybe i should to create a new pgsql function.
Thanks !
Edmund Bacon wrote:
>On Mon, 2004-03-08 at 08:19, Daniel Henrique Alves Lima wrote:
>
>
>> Is there a way to create a "functional index" over a string
>>concatenation of two columns ?
>>
>> Thanks.
>>
>>
>>
>Like this?
>
>test=# create table strtable (x int, str1 text, str2 text);
>CREATE TABLE
>test=# create index str_idx on strtable( textcat(str1, str2) );
>CREATE INDEX
>test=#
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Functional index and string concatenation
@ 2004-03-08 17:38 Richard Huxton <dev@archonet.com>
parent: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2004-03-08 17:38 UTC (permalink / raw)
To: email_daniel_h@yahoo.com.br; pgsql-sql
On Monday 08 March 2004 16:02, Daniel Henrique Alves Lima wrote:
> Yes, i was searching for the name of equivalent function to "||"
> operator...
>
> Now, i've got a new problem: I need to concat three values (two
> column values and a sepator constant). But from documentation: " The
> function in the index definition can take more than one argument, but
> they must be table columns, not constants" and "there cannot be
> multicolumn indexes that contain function calls".
>
> Maybe i should to create a new pgsql function.
Yes - a wrapper function is the standard solution. I don't think you need to
do this in 7.4 though.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-03-08 17:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-08 15:19 Functional index and string concatenation Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-08 15:28 ` Edmund Bacon <ebacon@onesystem.com>
2004-03-08 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-08 16:02 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-08 17:38 ` Richard Huxton <dev@archonet.com>
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