3 ms·
CTEs are just a nicer, more readable way of writing a subquery. Also, CTEs can be recursive. In Postgres, CTEs can be useful when used with the RETURNING clau
by JacKTrocinskI 4y ago
CTEs are just a nicer, more readable way of writing a subquery. Also, CTEs can be recursive. In Postgres, CTEs can be useful when used with the RETURNING clause. If you're using the same CTE in multiple different queries, then consider creating a view instead. I wouldn’t use a CTE for lookup values, create a lookup table instead.
- alexisread 4y agoNot only that, but in Postgres you can do updates in the CTEs, and chain them together eg. WITH CTE1 AS ( SELECT.... ) CTE2 AS ( INSERT....RETURNING insertid ) INSERT INTO TABLE2 insertid as FK, otherdata... The CTEs are executed in define-order as a single complete transaction, no need to use multiple calls to insert relational data