Menu
I am trying to use nested with:
CREATE TABLE audit_trail (
old_email TEXT NOT NULL,
new_email TEXT NOT NULL
);
INSERT INTO audit_trail(old_email, new_email)
VALUES ('[email protected]', '[email protected]'),
('[email protected]', '[email protected]'),
('[email protected]', '[email protected]'),
('[email protected]', '[email protected]'),
('[email protected]', '[email protected]');
with iter2 as (
with iter1 as (
select old_email, new_email from audit_trail where old_email = '[email protected]'
) select a.old_email, a.new_email from audit_trail a join iter1 b on (a.old_email = b.new_email)
) select * from iter1 union iter2;
I got this error:
ERROR: syntax error at or near "iter2" at character 264
STATEMENT: with iter2 as (
with iter1 as (
select old_email, new_email from audit_trail where old_email = '[email protected]'
) select a.old_email, a.new_email from audit_trail a join iter1 b on (a.old_email = b.new_email)
) select * from iter1 union iter2;
ERROR: syntax error at or near "iter2"
LINE 5: ) select * from iter1 union iter2;
Syntax error, obviously. Is nested with supported?
PostgreSQL version 9.4.4
294
Google BigQuery & PostgreSQL : Big Query for Data Analysis In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable.
A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
In PostgreSQL, the CTE(Common Table Expression) is used as a temporary result set that the user can reference within another SQL statement like SELECT, INSERT, UPDATE or DELETE. CTEs are temporary in the sense that they only exist during the execution of the query.
The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one.
The error message concerns inproper union syntax, it should be
...
select * from iter1
union
select * from iter2;
In this case however you will get the error
ERROR: relation "iter1" does not exist
LINE 6: select * from iter1
because nested with statement can be used but the table defined in an inner query is not visible outside the outer query. Use list of queries:
with iter1 as (
select old_email, new_email
from audit_trail
where old_email = '[email protected]'
),
iter2 as (
select a.old_email, a.new_email
from audit_trail a
join iter1 b on (a.old_email = b.new_email)
)
select * from iter1
union
select * from iter2;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
