1

Can I nest writeable CTEs?

WITH "parent_cte" AS (
  WITH "child_cte" AS (
     INSERT INTO ....
  )
  ...
)
...

I know that I can refer to a CTE in the FROM clause of another CTE.

2
  • So, what's the answer? Have you tested? Nov 8, 2017 at 10:51
  • I don't see why you need this feature though. What problem are you trying to solve that can't be done with non-nested CTEs? Nov 8, 2017 at 11:06

1 Answer 1

2

No, modifying CTEs still have to be at the top level.

Testing at Postgres 10.0 produces the error:

WITH clause containing a data-modifying statement must be at the top level.

select version() ;
                                                    version                                                     
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

x=# create table t (id int, x int) ;
CREATE TABLE
x=# insert into t (id, x) select i, i*2 from generate_series(1,10) as g(i) ;
INSERT 0 10
x=# select * from t ;
 id | x  
----+----
  1 |  2
  2 |  4
  3 |  6
  4 |  8
  5 | 10
  6 | 12
  7 | 14
  8 | 16
  9 | 18
 10 | 20
(10 rows)

x=# with a as 
      ( with b as 
          ( update t set x = x + 3 returning * 
          ) 
        select * from b
      ) 
    select * from a ;
ERROR:  WITH clause containing a data-modifying statement must be at the top level
LINE 2:   with b as (update t set x = x + 3 returning *) select * fr...
               ^
x=# 

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.