[SQL] Array: comparing first N elements?
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
Re: [SQL] Array: comparing first N elements?
On Tue, May 12, 2009 at 3:28 PM, Pavel Stehule 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
Re: [SQL] Array: comparing first N elements?
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 : > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Array: comparing first N elements?
2009/5/12 Achilleas Mantzios > 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
Re: [SQL] Array: comparing first N elements?
On Tue, May 12, 2009 at 4:05 AM, David Garamond 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Array: comparing first N elements?
Στις 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
Maximilian Tyrtania wrote: > am 11.05.2009 16:38 Uhr schrieb Alvaro Herrera unter > alvhe...@commandprompt.com: > > >>>Note that the de_DE locale uses Latin9 encoding, which is incompatible > >>>with UTF8. > >>> I'd try checking if the problem is reproducible in > >>> de_DE.utf8 (you need to create a new database for testing, obviously). > >> > >> Wait a minute. I need to re- initdb with de_DE.UTF-8, don't I? > > > > Well, either that, or create a new database with Latin9 encoding. > > FAKDB=# CREATE DATABASE "TestLatin9" > FAKDB-# WITH ENCODING='LATIN9' > FAKDB-#OWNER=postgres; > ERROR: encoding LATIN9 does not match server's locale de_DE > DETAIL: The server's LC_CTYPE setting requires encoding UTF8. > FAKDB=# > > Now i'm deeply confused...So do i have to re-initdb? Hmm, I didn't expect this. I guess I assumed de_DE was an alias for the Latin1- or Latin9- encoded locale, but it seems your system uses it as an alias for the UTF-8 encoded one. So my initial comment seems to be wrong as well. Please paste the output of the "locale" command. What platform are you using anyway? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
am 11.05.2009 16:38 Uhr schrieb Alvaro Herrera unter alvhe...@commandprompt.com: >>>Note that the de_DE locale uses Latin9 encoding, which is incompatible >>>with UTF8. >>> I'd try checking if the problem is reproducible in >>> de_DE.utf8 (you need to create a new database for testing, obviously). >> >> Wait a minute. I need to re- initdb with de_DE.UTF-8, don't I? > > Well, either that, or create a new database with Latin9 encoding. FAKDB=# CREATE DATABASE "TestLatin9" FAKDB-# WITH ENCODING='LATIN9' FAKDB-#OWNER=postgres; ERROR: encoding LATIN9 does not match server's locale de_DE DETAIL: The server's LC_CTYPE setting requires encoding UTF8. FAKDB=# Now i'm deeply confused...So do i have to re-initdb? Best, Maximilian Tyrtania -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
am 12.05.2009 19:23 Uhr schrieb Alvaro Herrera unter alvhe...@commandprompt.com: >> FAKDB=# CREATE DATABASE "TestLatin9" >> FAKDB-# WITH ENCODING='LATIN9' >> FAKDB-#OWNER=postgres; >> ERROR: encoding LATIN9 does not match server's locale de_DE >> DETAIL: The server's LC_CTYPE setting requires encoding UTF8. >> FAKDB=# >> >> Now i'm deeply confused...So do i have to re-initdb? > > Hmm, I didn't expect this. I guess I assumed de_DE was an alias for > the Latin1- or Latin9- encoded locale, but it seems your system uses it > as an alias for the UTF-8 encoded one. So my initial comment seems to > be wrong as well. Please paste the output of the "locale" command. Sputnik-Server:~ administrator$ locale LANG= LC_COLLATE="C" LC_CTYPE="C" LC_MESSAGES="C" LC_MONETARY="C" LC_NUMERIC="C" LC_TIME="C" LC_ALL="C" > What platform are you using anyway? Mac OS 10.4.11 Best, Maximilian Tyrtania -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
