Edit Question - KnowledgeHub

Title Body

I'm trying to learn SQL, using PostgreSQL 9.1.3. I would like to understand some behavior that strikes me as inconsistent. To wit: This works: WITH innermost AS (SELECT 2) SELECT * FROM innermost UNION SELECT 3; I get this: ?column? ---------- 2 3 This works: WITH outmost AS ( (WITH innermost AS (SELECT 2) SELECT * FROM innermost) ) SELECT * FROM outmost; Result: ?column? ---------- 2 This also works: WITH outmost AS ( SELECT 1 UNION (WITH innermost AS (SELECT 2) SELECT * FROM innermost) ) SELECT * FROM outmost; I get this: ?column? ---------- 1 2 But this does not work: WITH outmost AS ( SELECT 1 UNION (WITH innermost as (SELECT 2) SELECT * FROM innermost UNION SELECT 3) ) SELECT * FROM outmost; Result: ERROR: relation "innermost" does not exist LINE 4: SELECT * FROM innermost To my way of thinking, either the last one should succeed or one of the other ones should fail. I don't see the pattern. Is there some general rule that would enable me to predict what combinations of nested CTEs and UNIONs will or will not work?

Tags (comma-separated) Cancel