Is it possible to make a recursive SQL query?

by

Here is an example script using common table expression:

with recursive sumthis(id, val) as (
    select id, value
    from example
    where id = :selectedid
    union all
    select C.id, C.value
    from sumthis P
    inner join example C on P.id = C.parentid
)
select sum(val) from sumthis

The script above creates a ‘virtual’ table called sumthis that has columns id and val. It is defined as the result of two selects merged with union all.

First select gets the root (where id = :selectedid).

Second select follows the children of the previous results iteratively until there is nothing to return.

The end result can then be processed like a normal table. In this case the val column is summed.

More Related Contents:

  1. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  2. Select first row in each GROUP BY group?
  3. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  4. How to use RETURNING with ON CONFLICT in PostgreSQL?
  5. Does PostgreSQL support “accent insensitive” collations?
  6. PostgreSQL DISTINCT ON with different ORDER BY
  7. IN vs ANY operator in PostgreSQL
  8. Escaping keyword-like column names in Postgres
  9. Delete duplicate rows (don’t delete all duplicate)
  10. How do I force Postgres to use a particular index?
  11. How to create a temporary function in PostgreSQL?
  12. Postgres Case Sensitivity
  13. Record returned from function has columns concatenated
  14. Query for element of array in JSON column
  15. INSERT rows into multiple tables in a single query, selecting from an involved table
  16. Casting NULL type when updating multiple rows
  17. How to round an average to 2 decimal places in PostgreSQL?
  18. Best way to delete millions of rows by ID
  19. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  20. How to find a table having a specific column in postgresql
  21. How to read the contents of an .sql file into an R script to run a query?
  22. PostgreSQL JOIN with array type with array elements order, how to implement?
  23. Trim trailing spaces with PostgreSQL
  24. INSERT COMMAND :: ERROR: column “value” does not exist
  25. Check whether string is a date Postgresql
  26. SQL LIKE condition to check for integer?
  27. Counting the number of occurrences of a substring within a string in PostgreSQL
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. Temporal database design, with a twist (live vs draft rows)
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment