Tag Info

New answers tagged


Unreferenced CTEs are not executed at all. Related thread on pgsql-bugs with Tom Lane explaining the behavior. In your first example you have: SELECT * FROM firstquery; No reference to the CTE nonemptycheck. So the CTE is never executed. In the second example you have: SELECT firstquery.* FROM nonemptycheck, firstquery; nonemptycheck is referenced, ...


I suggest a "data-modifying CTE": WITH cte AS ( SELECT *, row_number() OVER () AS rn FROM ( SELECT id FROM tbl ORDER BY random() LIMIT 600 -- 2 x 300 ) sub ) INSERT INTO table2(source, target) SELECT c1.id, c2.id FROM cte c1 JOIN cte c2 ON c2.rn = c1.rn + 300; In the CTE: select 600 random rows (to create ...

Top 50 recent answers are included