CODEWITHSUNDEEP
sqlpostgresql
lupti_du
lupti_du

Reputation: 129

recursive query in postgresql

I have the following table:

table 'users'

and I have the following query:

    WITH RECURSIVE users_r AS (
          SELECT user_id, parent_id, 0 as level
          FROM users
          WHERE parent_id is null
        UNION ALL
          SELECT u.user_id, u.parent_id, u.level + 1
          FROM users u
              INNER JOIN users_r
                  ON (u.parent_id = users_r.user_id)   
    )

    SELECT * FROM users_r LIMIT 1000

I want to fill "level" column with depending on the count of ancestors. But my code isn't working. It fills rows only where parent_id is null.

Result of my code

Upvotes: 0

Views: 37

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

It is a simple typo.

The recursive SELECT, immediately after the UNION ALL should not read

SELECT u.user_id, u.parent_id, u.level + 1

but

SELECT u.user_id, u.parent_id, users_r.level + 1

You would have noticed right away if you didn't have a level column in users.

Upvotes: 1

Related Questions