In Postgres is there a difference between a CTE and a temporary table other than the fact that the CTE exists just for the context of one statement?

Documentation says that

Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Does "can be thought" means that they are identical?

  • Not identical because a CTE is not necessarily written to disk – a_horse_with_no_name Mar 20 '15 at 15:05
  • @a_horse_with_no_name -- one can imagine circumstances where a temporary table is never written to disk as well. – mustaccio Mar 20 '15 at 15:10
  • 1
    I was asking specifically in the context of Postgres. This blog post makes me think that CTE's in Postgres are always materialised: blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences – sumek Mar 20 '15 at 15:34

To add to what others have said. Temporary tables can also have primary keys, constraints, indexes, etc. whereas CTEs cannot.

On the flip side, you can do some pretty neat tricks with CTEs that would be harder, I think, if done with temporary tables-- such as chaining them to perform deletes, inserts, and selects all in one statement. There are some nice examples of "cool-CTE-tricks" here.

| improve this answer | |

A CTE is just that -- Common Table Expression, that is, only a syntax construct. The result set described by a CTE may never be materialized in the specified form.

A temporary table, on the other hand, is a real database object that is initialized with the structure described by its DDL statement and possibly populated by actual rows.

| improve this answer | |

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.