Postgresql query HAVING do not work

On 1/4/17, Gwork <nnj@riseup.net> wrote:

Version: Postgresql 9.5
OS: Debian 8 jessie run on docker

Following this tutorial The Nested Set Model on
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Section: Depth of a Sub-Tree.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
ORDER BY node.lft;
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

Section: Find the Immediate Subordinates of a Node.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
HAVING depth <= 1
ORDER BY node.lft;
Adding 'HAVING depth <= 1' to the query still return the same results as
above instead of this:
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

I don't know if I'm doing anything wrong?

Note: Edit the post query by adding node.lft, sub_tree.depth to the
GROUP BY.

Hello, Gwork,

HAVING works fine, it is just confusing because of naming. HAVING
works with column names from sources (which is "sub_tree.depth" in
your example), not with names of final columns (because they get
aliases later).

You can check it adding depth to your SELECT part:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
,array_agg(depth)
FROM nested_category AS node,
...

and you can see that values there are not bigger than 1.

You must use the same expression in HAVING clause as in SELECT one to
get what you want:
HAVING (COUNT(parent.name) - (sub_tree.depth + 1)) <= 1

but the result will not have "FLASH" because it has "2" even in your example.
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
(4 rows)

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 1/4/17, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

On 1/4/17, Gwork <nnj@riseup.net> wrote:

Version: Postgresql 9.5
OS: Debian 8 jessie run on docker

Following this tutorial The Nested Set Model on
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Section: Depth of a Sub-Tree.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
ORDER BY node.lft;
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

Section: Find the Immediate Subordinates of a Node.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
HAVING depth <= 1
ORDER BY node.lft;
Adding 'HAVING depth <= 1' to the query still return the same results as
above instead of this:
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

I don't know if I'm doing anything wrong?

Note: Edit the post query by adding node.lft, sub_tree.depth to the
GROUP BY.

Hello, Gwork,

HAVING works fine, it is just confusing because of naming. HAVING
works with column names from sources (which is "sub_tree.depth" in
your example), not with names of final columns (because they get
aliases later).

You can check it adding depth to your SELECT part:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
,array_agg(depth)
FROM nested_category AS node,
...

and you can see that values there are not bigger than 1.

You must use the same expression in HAVING clause as in SELECT one to
get what you want:
HAVING (COUNT(parent.name) - (sub_tree.depth + 1)) <= 1

but the result will not have "FLASH" because it has "2" even in your
example.
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
(4 rows)

I'm sorry, forgot to mention: If you want to deal with hierarchical
data, Postgres has better solution - recursive query[1]https://www.postgresql.org/docs/current/static/queries-with.html. When you
understand principles, it will be much easier for you to write queries
instead of mentioned in the article.

For example, "Retrieving a Single Path" from "Adjacency model" can be
written as:
WITH RECURSIVE
sel(name, parent, depth) AS (
SELECT name, parent, 0 FROM category WHERE name='FLASH'
UNION ALL
SELECT c.name, c.parent, depth + 1 FROM category c, sel WHERE
c.category_id=sel.parent
)
SELECT name FROM sel
ORDER BY depth DESC;

which gives the same result and not depends on "parent.lft" which
don't have to increase.

Moreover, you don't need to lock a table when you change data and you
can even add a constraint to keep consistency:
ALTER TABLE category ADD FOREIGN KEY (parent) REFERENCES
category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT;

[1]: https://www.postgresql.org/docs/current/static/queries-with.html

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 1/4/17, Gwork <nnj@riseup.net> wrote:

On 1/5/17 2:22 AM, Vitaly Burovoy wrote:

On 1/4/17, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

On 1/4/17, Gwork <nnj@riseup.net> wrote:

Version: Postgresql 9.5
OS: Debian 8 jessie run on docker

Following this tutorial The Nested Set Model on
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Section: Depth of a Sub-Tree.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
ORDER BY node.lft;
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

Section: Find the Immediate Subordinates of a Node.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
HAVING depth <= 1
ORDER BY node.lft;
Adding 'HAVING depth <= 1' to the query still return the same results
as
above instead of this:
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

I don't know if I'm doing anything wrong?

Note: Edit the post query by adding node.lft, sub_tree.depth to the
GROUP BY.

Hello, Gwork,

HAVING works fine, it is just confusing because of naming. HAVING
works with column names from sources (which is "sub_tree.depth" in
your example), not with names of final columns (because they get
aliases later).

You can check it adding depth to your SELECT part:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
,array_agg(depth)
FROM nested_category AS node,
...

and you can see that values there are not bigger than 1.

You must use the same expression in HAVING clause as in SELECT one to
get what you want:
HAVING (COUNT(parent.name) - (sub_tree.depth + 1)) <= 1

but the result will not have "FLASH" because it has "2" even in your
example.
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
(4 rows)

I'm sorry, forgot to mention: If you want to deal with hierarchical
data, Postgres has better solution - recursive query[1]. When you
understand principles, it will be much easier for you to write queries
instead of mentioned in the article.

For example, "Retrieving a Single Path" from "Adjacency model" can be
written as:
WITH RECURSIVE
sel(name, parent, depth) AS (
SELECT name, parent, 0 FROM category WHERE name='FLASH'
UNION ALL
SELECT c.name, c.parent, depth + 1 FROM category c, sel WHERE
c.category_id=sel.parent
)
SELECT name FROM sel
ORDER BY depth DESC;

which gives the same result and not depends on "parent.lft" which
don't have to increase.

Moreover, you don't need to lock a table when you change data and you
can even add a constraint to keep consistency:
ALTER TABLE category ADD FOREIGN KEY (parent) REFERENCES
category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT;

[1]https://www.postgresql.org/docs/current/static/queries-with.html

Hi Vitaly,

Your first solution worked great!

I'll like try your second suggestion, I feel is gonna be a better solution
very important to eliminate lock while updating table.

I'll keep you posted if I have any further issue relating to the query.

Thank you for helping out.

Feel free to ask, but do not forget to add the mailing list in CC (via
"Reply to all").
Other people (new users) also can be interested in ways to solve issues.

P.S. Moved from -bugs[2]/messages/by-id/7582ea1e-6146-fd8d-b564-c2fe251210b2@riseup.net -- Best regards, Vitaly Burovoy to -general.

[2]: /messages/by-id/7582ea1e-6146-fd8d-b564-c2fe251210b2@riseup.net -- Best regards, Vitaly Burovoy
--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Looking at tutorial I can not replicate those querys to Postgresql
without serious editing. But, I simply want to create a hierarchical
model tree that look like Amazon.

What's your general solution on that can work better and easy to
maintain than Nested Set Model with update lock?

On 1/5/17 2:51 AM, Vitaly Burovoy wrote:

On 1/4/17, Gwork <nnj@riseup.net> wrote:

On 1/5/17 2:22 AM, Vitaly Burovoy wrote:

On 1/4/17, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

On 1/4/17, Gwork <nnj@riseup.net> wrote:

Version: Postgresql 9.5
OS: Debian 8 jessie run on docker

Following this tutorial The Nested Set Model on
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Section: Depth of a Sub-Tree.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
ORDER BY node.lft;
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

Section: Find the Immediate Subordinates of a Node.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
HAVING depth <= 1
ORDER BY node.lft;
Adding 'HAVING depth <= 1' to the query still return the same results
as
above instead of this:
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

I don't know if I'm doing anything wrong?

Note: Edit the post query by adding node.lft, sub_tree.depth to the
GROUP BY.

Hello, Gwork,

HAVING works fine, it is just confusing because of naming. HAVING
works with column names from sources (which is "sub_tree.depth" in
your example), not with names of final columns (because they get
aliases later).

You can check it adding depth to your SELECT part:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
,array_agg(depth)
FROM nested_category AS node,
...

and you can see that values there are not bigger than 1.

You must use the same expression in HAVING clause as in SELECT one to
get what you want:
HAVING (COUNT(parent.name) - (sub_tree.depth + 1)) <= 1

but the result will not have "FLASH" because it has "2" even in your
example.
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+-------+
(4 rows)

I'm sorry, forgot to mention: If you want to deal with hierarchical
data, Postgres has better solution - recursive query[1]. When you
understand principles, it will be much easier for you to write queries
instead of mentioned in the article.

For example, "Retrieving a Single Path" from "Adjacency model" can be
written as:
WITH RECURSIVE
sel(name, parent, depth) AS (
SELECT name, parent, 0 FROM category WHERE name='FLASH'
UNION ALL
SELECT c.name, c.parent, depth + 1 FROM category c, sel WHERE
c.category_id=sel.parent
)
SELECT name FROM sel
ORDER BY depth DESC;

which gives the same result and not depends on "parent.lft" which
don't have to increase.

Moreover, you don't need to lock a table when you change data and you
can even add a constraint to keep consistency:
ALTER TABLE category ADD FOREIGN KEY (parent) REFERENCES
category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT;

[1]https://www.postgresql.org/docs/current/static/queries-with.html

Hi Vitaly,

Your first solution worked great!

I'll like try your second suggestion, I feel is gonna be a better solution
very important to eliminate lock while updating table.

I'll keep you posted if I have any further issue relating to the query.

Thank you for helping out.

Feel free to ask, but do not forget to add the mailing list in CC (via
"Reply to all").
Other people (new users) also can be interested in ways to solve issues.

P.S. Moved from -bugs[2] to -general.

[2]/messages/by-id/7582ea1e-6146-fd8d-b564-c2fe251210b2@riseup.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 4, 2017 at 7:23 PM, Gwork <nnj@riseup.net> wrote:

Version: Postgresql 9.5
OS: Debian 8 jessie run on docker

Following this tutorial The Nested Set Model on
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Section: Depth of a Sub-Tree.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
ORDER BY node.lft;
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

Section: Find the Immediate Subordinates of a Node.
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, node.lft, sub_tree.depth
HAVING depth <= 1
ORDER BY node.lft;
Adding 'HAVING depth <= 1' to the query still return the same results as
above instead of this:
+----------------------+---------+
| name | depth |
|----------------------+---------|
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---------+

I don't know if I'm doing anything wrong?

Note: Edit the post query by adding node.lft, sub_tree.depth to the
GROUP BY.

FYI,

"The Nested Set Model" has terrible insertion performance. Any row
inserted can cause the entire table to be updated. Not good.
Materialized path approaches tend to be better in every sense.

In postgres, "The Adjacency List Model" can be queried via WITH
RECURSIVE. This mitigates a lot of the downsides that the OP
mentions. I guess mysql does not have that feature?

merlin

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs