Re: [SQL] LTREE extension and "order by"
Hi, thank you for your answer, please can You send me complete select command how to convert ltree column to integer[] and use it to order by. thanks Ivan 2011/7/21 pasman pasmański : > Hi. > > You should convert path to integer[]. > > 2011/7/20, Ivan Polak : >> Hi, >> >> in postgreSQL (with LTREE extension) database I have the following >> table "comments": >> >> id BIGINT /* id */ >> article_id BIGINT /*article-id */ >> parent_id BIGINT >> comment TEXT >> path LTREE >> level INTEGER /* level */ >> >> with the following rows: >> >> id article_id comment parent_id path level >> 1 1 1 1 >> 2 1 1 1.2 2 >> 3 1 2 1.2.3 3 >> 4 1 2 1.4 2 >> 5 1 4 1.4.5 3 >> 6 1 6 1 >> 7 1 6 6.7 2 >> 8 1 6 6.8 2 >> 9 1 9 1 >> 10 1 10 1 >> 11 1 5 1.4.5.11 4 >> >> and I need to select complete tree (with correct order of comments). >> >> SELECT * from comments where article_id = 2 order by >> >> when I used: >> >> SELECT * from comments where article_id = 2 order by path >> >> the result is: >> >> id comment path >> 1 1 >> 2 1.2 >> 3 1.2.3 >> 4 1.4 >> 5 1.4.5 >> 11 1.4.5.11 >> 10 10 >> 6 6 >> 7 6.7 >> 8 6.8 >> 9 9 >> >> BUT, it is wrong, because comment with id = 10 is after comment with id=11 >> >> (i know, this is correct, because ordering by column PATH [as TEXT], >> and 10 is 'after' 1.4.5.11) >> >> , but I need : >> >> id comment path >> 1 1 >> 2 1.2 >> 3 1.2.3 >> 4 1.4 >> 5 1.4.5 >> 11 1.4.5.11 >> 6 6 >> 7 6.7 >> 8 6.8 >> 9 9 >> 10 10 >> >> thanks >> >> Ivan >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > > -- > > pasman > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- 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] LTREE extension and "order by"
Try it: select * from comments where article_id = 2 order by string_to_array(path::text,'.')::integer[]; 2011/7/21 Ivan Polak > Hi, thank you for your answer, please can You send me complete select > command how to convert ltree column to integer[] and use it to order > by. > > thanks > > Ivan > > 2011/7/21 pasman pasmański : > > Hi. > > > > You should convert path to integer[]. > > > > 2011/7/20, Ivan Polak : > >> Hi, > >> > >> in postgreSQL (with LTREE extension) database I have the following > >> table "comments": > >> > >> id BIGINT /* id */ > >> article_id BIGINT /*article-id */ > >> parent_id BIGINT > >> comment TEXT > >> path LTREE > >> level INTEGER /* level */ > >> > >> with the following rows: > >> > >> id article_id comment parent_id path level > >> 1 1 1 1 > >> 2 1 1 1.2 2 > >> 3 1 2 1.2.3 3 > >> 4 1 2 1.4 2 > >> 5 1 4 1.4.5 3 > >> 6 1 6 1 > >> 7 1 6 6.7 2 > >> 8 1 6 6.8 2 > >> 9 1 9 1 > >> 10 1 10 1 > >> 11 1 5 1.4.5.11 4 > >> > >> and I need to select complete tree (with correct order of comments). > >> > >> SELECT * from comments where article_id = 2 order by > >> > >> when I used: > >> > >> SELECT * from comments where article_id = 2 order by path > >> > >> the result is: > >> > >> id comment path > >> 1 1 > >> 2 1.2 > >> 3 1.2.3 > >> 4 1.4 > >> 5 1.4.5 > >> 11 1.4.5.11 > >> 10 10 > >> 6 6 > >> 7 6.7 > >> 8 6.8 > >> 9 9 > >> > >> BUT, it is wrong, because comment with id = 10 is after comment with > id=11 > >> > >> (i know, this is correct, because ordering by column PATH [as TEXT], > >> and 10 is 'after' 1.4.5.11) > >> > >> , but I need : > >> > >> id comment path > >> 1 1 > >> 2 1.2 > >> 3 1.2.3 > >> 4 1.4 > >> 5 1.4.5 > >> 11 1.4.5.11 > >> 6 6 > >> 7 6.7 > >> 8 6.8 > >> 9 9 > >> 10 10 > >> > >> thanks > >> > >> Ivan > >> > >> -- > >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-sql > >> > > > > > > -- > > > > pasman > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > 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] LTREE extension and "order by"
hi, thank you, but there is error: ERROR: cannot cast type ltree to text LINE 1: ... article_id = 2 order by string_to_array(path::text,'.'):... Ivan On 21 July 2011 17:25, Carla wrote: > Try it: > select * from comments where article_id = 2 order by > string_to_array(path::text,'.')::integer[]; > > 2011/7/21 Ivan Polak >> >> Hi, thank you for your answer, please can You send me complete select >> command how to convert ltree column to integer[] and use it to order >> by. >> >> thanks >> >> Ivan >> >> 2011/7/21 pasman pasmański : >> > Hi. >> > >> > You should convert path to integer[]. >> > >> > 2011/7/20, Ivan Polak : >> >> Hi, >> >> >> >> in postgreSQL (with LTREE extension) database I have the following >> >> table "comments": >> >> >> >> id BIGINT /* id */ >> >> article_id BIGINT /*article-id */ >> >> parent_id BIGINT >> >> comment TEXT >> >> path LTREE >> >> level INTEGER /* level */ >> >> >> >> with the following rows: >> >> >> >> id article_id comment parent_id path level >> >> 1 1 1 1 >> >> 2 1 1 1.2 2 >> >> 3 1 2 1.2.3 3 >> >> 4 1 2 1.4 2 >> >> 5 1 4 1.4.5 3 >> >> 6 1 6 1 >> >> 7 1 6 6.7 2 >> >> 8 1 6 6.8 2 >> >> 9 1 9 1 >> >> 10 1 10 1 >> >> 11 1 5 1.4.5.11 4 >> >> >> >> and I need to select complete tree (with correct order of comments). >> >> >> >> SELECT * from comments where article_id = 2 order by >> >> >> >> when I used: >> >> >> >> SELECT * from comments where article_id = 2 order by path >> >> >> >> the result is: >> >> >> >> id comment path >> >> 1 1 >> >> 2 1.2 >> >> 3 1.2.3 >> >> 4 1.4 >> >> 5 1.4.5 >> >> 11 1.4.5.11 >> >> 10 10 >> >> 6 6 >> >> 7 6.7 >> >> 8 6.8 >> >> 9 9 >> >> >> >> BUT, it is wrong, because comment with id = 10 is after comment with >> >> id=11 >> >> >> >> (i know, this is correct, because ordering by column PATH [as TEXT], >> >> and 10 is 'after' 1.4.5.11) >> >> >> >> , but I need : >> >> >> >> id comment path >> >> 1 1 >> >> 2 1.2 >> >> 3 1.2.3 >> >> 4 1.4 >> >> 5 1.4.5 >> >> 11 1.4.5.11 >> >> 6 6 >> >> 7 6.7 >> >> 8 6.8 >> >> 9 9 >> >> 10 10 >> >> >> >> thanks >> >> >> >> Ivan >> >> >> >> -- >> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> > >> > >> > -- >> > >> > pasman >> > >> > -- >> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> > To make changes to your subscription: >> > http://www.postgresql.org/mailpref/pgsql-sql >> > >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > -- 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] LTREE extension and "order by"
Hmm, I'm using PostgreSQL 8.4 and it worked. Try to use the function ltree2text instead of ::text. select * from comments where article_id = 2 order by cast(string_to_array(ltree2text(path),'.') as integer[]); 2011/7/21 Ivan Polak > hi, thank you, but there is error: > > ERROR: cannot cast type ltree to text > LINE 1: ... article_id = 2 order by string_to_array(path::text,'.'):... > > Ivan > > On 21 July 2011 17:25, Carla wrote: > > Try it: > > select * from comments where article_id = 2 order by > > string_to_array(path::text,'.')::integer[]; > > > > 2011/7/21 Ivan Polak > >> > >> Hi, thank you for your answer, please can You send me complete select > >> command how to convert ltree column to integer[] and use it to order > >> by. > >> > >> thanks > >> > >> Ivan > >> > >> 2011/7/21 pasman pasmański : > >> > Hi. > >> > > >> > You should convert path to integer[]. > >> > > >> > 2011/7/20, Ivan Polak : > >> >> Hi, > >> >> > >> >> in postgreSQL (with LTREE extension) database I have the following > >> >> table "comments": > >> >> > >> >> id BIGINT /* id */ > >> >> article_id BIGINT /*article-id */ > >> >> parent_id BIGINT > >> >> comment TEXT > >> >> path LTREE > >> >> level INTEGER /* level */ > >> >> > >> >> with the following rows: > >> >> > >> >> id article_id comment parent_id path level > >> >> 1 1 1 1 > >> >> 2 1 1 1.2 2 > >> >> 3 1 2 1.2.3 3 > >> >> 4 1 2 1.4 2 > >> >> 5 1 4 1.4.5 3 > >> >> 6 1 6 1 > >> >> 7 1 6 6.7 2 > >> >> 8 1 6 6.8 2 > >> >> 9 1 9 1 > >> >> 10 1 10 1 > >> >> 11 1 5 1.4.5.11 4 > >> >> > >> >> and I need to select complete tree (with correct order of comments). > >> >> > >> >> SELECT * from comments where article_id = 2 order by > >> >> > >> >> when I used: > >> >> > >> >> SELECT * from comments where article_id = 2 order by path > >> >> > >> >> the result is: > >> >> > >> >> id comment path > >> >> 1 1 > >> >> 2 1.2 > >> >> 3 1.2.3 > >> >> 4 1.4 > >> >> 5 1.4.5 > >> >> 11 1.4.5.11 > >> >> 10 10 > >> >> 6 6 > >> >> 7 6.7 > >> >> 8 6.8 > >> >> 9 9 > >> >> > >> >> BUT, it is wrong, because comment with id = 10 is after comment with > >> >> id=11 > >> >> > >> >> (i know, this is correct, because ordering by column PATH [as TEXT], > >> >> and 10 is 'after' 1.4.5.11) > >> >> > >> >> , but I need : > >> >> > >> >> id comment path > >> >> 1 1 > >> >> 2 1.2 > >> >> 3 1.2.3 > >> >> 4 1.4 > >> >> 5 1.4.5 > >> >> 11 1.4.5.11 > >> >> 6 6 > >> >> 7 6.7 > >> >> 8 6.8 > >> >> 9 9 > >> >> 10 10 > >> >> > >> >> thanks > >> >> > >> >> Ivan > >> >> > >> >> -- > >> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> >> To make changes to your subscription: > >> >> http://www.postgresql.org/mailpref/pgsql-sql > >> >> > >> > > >> > > >> > -- > >> > > >> > pasman > >> > > >> > -- > >> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> > To make changes to your subscription: > >> > http://www.postgresql.org/mailpref/pgsql-sql > >> > > >> > >> -- > >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-sql > >> > > > > > > -- > 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] LTREE extension and "order by"
2011/7/21 Carla : > Hmm, I'm using PostgreSQL 8.4 and it worked. > Try to use the function ltree2text instead of ::text. > select * from comments where article_id = 2 order by > cast(string_to_array(ltree2text(path),'.') as integer[]); > this does not work in older versions you can use http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Cast_to_varchar Regards Pavel Stehule -- 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] LTREE extension and "order by"
HI, thanks, thanks Carla, your solution is OK :-) (i`m using PostgreSQL 8.2, so Pavel is right). Ivan On 21 July 2011 18:28, Carla wrote: > Hmm, I'm using PostgreSQL 8.4 and it worked. > Try to use the function ltree2text instead of ::text. > select * from comments where article_id = 2 order by > cast(string_to_array(ltree2text(path),'.') as integer[]); > > 2011/7/21 Ivan Polak >> >> hi, thank you, but there is error: >> >> ERROR: cannot cast type ltree to text >> LINE 1: ... article_id = 2 order by string_to_array(path::text,'.'):... >> >> Ivan >> >> On 21 July 2011 17:25, Carla wrote: >> > Try it: >> > select * from comments where article_id = 2 order by >> > string_to_array(path::text,'.')::integer[]; >> > >> > 2011/7/21 Ivan Polak >> >> >> >> Hi, thank you for your answer, please can You send me complete select >> >> command how to convert ltree column to integer[] and use it to order >> >> by. >> >> >> >> thanks >> >> >> >> Ivan >> >> >> >> 2011/7/21 pasman pasmański : >> >> > Hi. >> >> > >> >> > You should convert path to integer[]. >> >> > >> >> > 2011/7/20, Ivan Polak : >> >> >> Hi, >> >> >> >> >> >> in postgreSQL (with LTREE extension) database I have the following >> >> >> table "comments": >> >> >> >> >> >> id BIGINT /* id */ >> >> >> article_id BIGINT /*article-id */ >> >> >> parent_id BIGINT >> >> >> comment TEXT >> >> >> path LTREE >> >> >> level INTEGER /* level */ >> >> >> >> >> >> with the following rows: >> >> >> >> >> >> id article_id comment parent_id path level >> >> >> 1 1 1 1 >> >> >> 2 1 1 1.2 2 >> >> >> 3 1 2 1.2.3 3 >> >> >> 4 1 2 1.4 2 >> >> >> 5 1 4 1.4.5 3 >> >> >> 6 1 6 1 >> >> >> 7 1 6 6.7 2 >> >> >> 8 1 6 6.8 2 >> >> >> 9 1 9 1 >> >> >> 10 1 10 1 >> >> >> 11 1 5 1.4.5.11 4 >> >> >> >> >> >> and I need to select complete tree (with correct order of comments). >> >> >> >> >> >> SELECT * from comments where article_id = 2 order by >> >> >> >> >> >> when I used: >> >> >> >> >> >> SELECT * from comments where article_id = 2 order by path >> >> >> >> >> >> the result is: >> >> >> >> >> >> id comment path >> >> >> 1 1 >> >> >> 2 1.2 >> >> >> 3 1.2.3 >> >> >> 4 1.4 >> >> >> 5 1.4.5 >> >> >> 11 1.4.5.11 >> >> >> 10 10 >> >> >> 6 6 >> >> >> 7 6.7 >> >> >> 8 6.8 >> >> >> 9 9 >> >> >> >> >> >> BUT, it is wrong, because comment with id = 10 is after comment with >> >> >> id=11 >> >> >> >> >> >> (i know, this is correct, because ordering by column PATH [as TEXT], >> >> >> and 10 is 'after' 1.4.5.11) >> >> >> >> >> >> , but I need : >> >> >> >> >> >> id comment path >> >> >> 1 1 >> >> >> 2 1.2 >> >> >> 3 1.2.3 >> >> >> 4 1.4 >> >> >> 5 1.4.5 >> >> >> 11 1.4.5.11 >> >> >> 6 6 >> >> >> 7 6.7 >> >> >> 8 6.8 >> >> >> 9 9 >> >> >> 10 10 >> >> >> >> >> >> thanks >> >> >> >> >> >> Ivan >> >> >> >> >> >> -- >> >> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> >> To make changes to your subscription: >> >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> > >> >> > >> >> > -- >> >> > >> >> > pasman >> >> > >> >> > -- >> >> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> > To make changes to your subscription: >> >> > http://www.postgresql.org/mailpref/pgsql-sql >> >> > >> >> >> >> -- >> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> > >> > >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Need to return one field for multiple Rows
I have the following query Select PubArticleId, PubArticlePageXML from PubarticlePageXml x Inner Join Pubarticlepage p on x.pubarticlepageid=p.pubarticlepageid where pubarticleid=188321 Which Returns PubArticleId(Integer)PubArticlePageXML(Text) 188321 fdfaldkjf 188321 afdaf 188321 adfafd 188321 afddfadfa I want it to return one row 188321 fdfaldkjf afdaf adfafd afddfadfa Any thoughts On how I can do this? Thanks Pam Ozer
Re: [SQL] Need to return one field for multiple Rows
Never Mind Ifigured it out. I just need to use agg_array From: Ozer, Pam Sent: Thursday, July 21, 2011 4:09 PM To: pgsql-sql@postgresql.org Subject: Need to return one field for multiple Rows I have the following query Select PubArticleId, PubArticlePageXML from PubarticlePageXml x Inner Join Pubarticlepage p on x.pubarticlepageid=p.pubarticlepageid where pubarticleid=188321 Which Returns PubArticleId(Integer)PubArticlePageXML(Text) 188321 fdfaldkjf 188321 afdaf 188321 adfafd 188321 afddfadfa I want it to return one row 188321 fdfaldkjf afdaf adfafd afddfadfa Any thoughts On how I can do this? Thanks Pam Ozer
