common-table-expression

Are SELECT type queries the only type that can be nested?

Basic answer There are CTEs (Common Table Expressions) in Postgres – like in any major modern RDBMS, That includes MySQL since version 8.0. Since version 9.1 Postgres also features data-modifying CTEs, which can be “nested”. Unlike subqueries CTEs pose as optimization barriers. The query planner cannot inline trivial commands into the main command or reorder … Read more

Window Functions or Common Table Expressions: count previous rows within range

I don’t think you can do this cheaply with a plain query, CTEs and window functions – their frame definition is static, but you need a dynamic frame (depending on column values). Generally, you’ll have to define lower and upper bound of your window carefully: The following queries exclude the current row and include the … Read more

CTE error: “Types don’t match between the anchor and the recursive part”

Exactly what it says: ‘name1’ has a different data type to ‘name’ + CAST((rn+1) as varchar(255)) Try this (untested) ;with cte as ( select 1 as rn, CAST(‘name1’ as varchar(259)) as nm union all select rn+1,nm = ‘name’ + CAST((rn+1) as varchar(255)) from cte a where rn<10) select * from cte Basically, you have to … Read more

Delete parent if it’s not referenced by any other child

In PostgreSQL 9.1 or later you can do this with a single statement using a data-modifying CTE. This is generally less error prone. It minimizes the time frame between the two DELETEs in which a race conditions could lead to surprising results with concurrent operations: WITH del_child AS ( DELETE FROM child WHERE child_id = … Read more