pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedArray: comparing first N elements?
6+ messages / 4 participants
[nested] [flat]
* Array: comparing first N elements?
@ 2009-05-12 08:05 David Garamond <davidgaramond@gmail.com>
2009-05-12 08:28 ` Re: Array: comparing first N elements? Pavel Stehule <pavel.stehule@gmail.com>
2009-05-12 08:37 ` Re: Array: comparing first N elements? Glenn Maynard <glennfmaynard@gmail.com>
2009-05-12 08:38 ` Re: Array: comparing first N elements? Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 3 replies; 6+ messages in thread
From: David Garamond @ 2009-05-12 08:05 UTC (permalink / raw)
To: pgsql-sql
I have a "materialized path" tree table like this (simplified):
CREATE TABLE product (
id SERIAL PRIMARY KEY,
parents INT[] NOT NULL,
name TEXT NOT NULL,
UNIQUE (parents, name)
);
CREATE INDEX name ON product(name);
Previously I use TEXT column for parents, but arrays look interesting and
convenient so I'm considering migrating to arrays. However, how do I rewrite
this using arrays?
SELECT * FROM product
WHERE parents LIKE '0001/0010/%';
In other words, testing against the first N elements in an array.
Regards,
Dave
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array: comparing first N elements?
2009-05-12 08:05 Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
@ 2009-05-12 08:28 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-05-12 08:40 ` Re: Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2009-05-12 08:28 UTC (permalink / raw)
To: David Garamond <davidgaramond@gmail.com>; +Cc: pgsql-sql
Hello
create or replace function eqn(anyarray, anyarray, int)
returns boolean as $$
select not exists(select $1[i] from generate_series(1,$3) g(i)
except
select $2[i] from generate_series(1,$3) g(i))
$$ language sql immutable strict;
postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 3);
eqn
-----
t
(1 row)
Time: 1,590 ms
postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 4);
eqn
-----
f
(1 row)
regards
Pavel Stehule
2009/5/12 David Garamond <davidgaramond@gmail.com>:
> I have a "materialized path" tree table like this (simplified):
> CREATE TABLE product (
> id SERIAL PRIMARY KEY,
> parents INT[] NOT NULL,
> name TEXT NOT NULL,
> UNIQUE (parents, name)
> );
> CREATE INDEX name ON product(name);
>
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewrite
> this using arrays?
> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';
> In other words, testing against the first N elements in an array.
> Regards,
> Dave
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array: comparing first N elements?
2009-05-12 08:05 Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
2009-05-12 08:28 ` Re: Array: comparing first N elements? Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-05-12 08:40 ` David Garamond <davidgaramond@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Garamond @ 2009-05-12 08:40 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-sql
On Tue, May 12, 2009 at 3:28 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
> Hello
>
> create or replace function eqn(anyarray, anyarray, int)
> returns boolean as $$
> select not exists(select $1[i] from generate_series(1,$3) g(i)
> except
> select $2[i] from generate_series(1,$3) g(i))
> $$ language sql immutable strict;
>
> postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 3);
> eqn
> -----
> t
> (1 row)
>
> Time: 1,590 ms
> postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 4);
> eqn
> -----
> f
> (1 row)
>
Hi Pavel,
Thanks for the solution, but that's too slow. I'd rather just do this
instead:
select * from product
where parents[1:(select array_length(parents,1) from product where
name='wanted')+1]=
(select parents from product where name='wanted')||
(select id from product where name='wanted');
but the above query is also unable to use any indices (unlike LIKE 'foo%').
Regards,
Dave
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array: comparing first N elements?
2009-05-12 08:05 Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
@ 2009-05-12 08:37 ` Glenn Maynard <glennfmaynard@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Glenn Maynard @ 2009-05-12 08:37 UTC (permalink / raw)
To: pgsql-sql
On Tue, May 12, 2009 at 4:05 AM, David Garamond <davidgaramond@gmail.com> wrote:
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewrite
> this using arrays?
> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';
> In other words, testing against the first N elements in an array.
SELECT * FROM product
WHERE parents[1] = 1 AND parents[2] = 2;
I'd expect there to be a way to index this, on individual components
or a slice, eg.
CREATE INDEX parents_1 ON product(parents[1]);
CREATE INDEX parents_2to4 ON product(parents[2], parents[3], parents[4]);
... but this throws a parse error. I don't have an immediate need for
this, but I'm curious if this is possible--it seems a natural part of
having a native array type.
--
Glenn Maynard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array: comparing first N elements?
2009-05-12 08:05 Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
@ 2009-05-12 08:38 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2009-05-12 08:42 ` Re: Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2009-05-12 08:38 UTC (permalink / raw)
To: pgsql-sql; +Cc: David Garamond <davidgaramond@gmail.com>
Óôéò Tuesday 12 May 2009 11:05:28 ï/ç David Garamond Ýãñáøå:
> I have a "materialized path" tree table like this (simplified):
>
> CREATE TABLE product (
> id SERIAL PRIMARY KEY,
> parents INT[] NOT NULL,
> name TEXT NOT NULL,
> UNIQUE (parents, name)
> );
> CREATE INDEX name ON product(name);
>
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewrite
> this using arrays?
>
Hi, I have used *exactly* the same scheme to model all PMS data in out fleet comprising
of 1.5 million rows, for some 6 years now.
You may find it in literature as genealogical tree representation.
> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';
Node 0001/0010 will have an id lets call it "parid".
If you model your path in parents[] (we also use the same column name!)
starting from the immediate father at parents[1] and going up to the root at parents[#parents]
then what you actually want is to find all nodes for which parents[1]=parid
you would want to look at the intarray contrib package for index suppor and many other goodies,
also you might want to write fucntions first(parents), last(parents) and then have an index
on those as well.
This way searching for the direct children of a node is very fast.
If on the other hand you want to find all children of parid, regardless of level,
then you would do that with: intset(parid) ~ parents
For the above to be efficient you should create an index on parents. Prefer method "gin" with opclass "gin__int_ops"
Well thats how i implemented trees in postgresql anyway.
>
> In other words, testing against the first N elements in an array.
>
> Regards,
> Dave
>
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array: comparing first N elements?
2009-05-12 08:05 Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
2009-05-12 08:38 ` Re: Array: comparing first N elements? Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2009-05-12 08:42 ` David Garamond <davidgaramond@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Garamond @ 2009-05-12 08:42 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
2009/5/12 Achilleas Mantzios <achill@matrix.gatewaynet.com>
> you would want to look at the intarray contrib package for index suppor and
> many other goodies,
> also you might want to write fucntions first(parents), last(parents) and
> then have an index
> on those as well.
> This way searching for the direct children of a node is very fast.
Thanks for the suggestions! Index support is exactly what I'm looking for.
Will look into intarray.
Regards,
dave
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-05-12 08:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-12 08:05 Array: comparing first N elements? David Garamond <davidgaramond@gmail.com>
2009-05-12 08:28 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-05-12 08:40 ` David Garamond <davidgaramond@gmail.com>
2009-05-12 08:37 ` Glenn Maynard <glennfmaynard@gmail.com>
2009-05-12 08:38 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2009-05-12 08:42 ` David Garamond <davidgaramond@gmail.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