Temp table as in using WITH?
More info in the PostgreSQL docs [1]:
> If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.
[1]: https://www.postgresql.org/docs/13/sql-createtable.html
On Mysql, using a union all creates a temp table which can perform catastrophically under database load. I’ve seen a union all query with zero rows in the second half render a database server unresponsive when the database was under high load, causing a service disruption. We ended rewriting the union all query as two database fetches and have not seen a single problem in that area since.
I was shocked by this union all behavior, but it is apparently a well known thing on MySQL.
I can’t speak to Postgres behavior for this kind of query.