I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or recursive SQL statements.

As far as I can tell Snowflake does not support these operations. Are there any other queries that can be made to mimic this or work around this?

"Currently, Snowflake does not fully support common table expressions (CTE) in DDL operations."

Snowflake documentation: https://docs.snowflake.net/manuals/sql-reference/constructs/with.html

see: https://www.postgresql.org/docs/9.1/static/queries-with.html

  • I would be quite surprised if you are able to handle this in Snowflake without application-side logic, as parent-child relationships are exactly what recursive CTEs are for. You may (possibly) be able to do a finite recursion by explicitly spelling out each level as sequential separate CTEs, but it's likely to be convoluted. – Wildcard Sep 11 '18 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.