PostgreSQL if temporary table is dropped query is empty

Question

I'm working on a rather simple homework assignment where I have to retrieve some data that complies with some criteria and then run another query on the data that was selected in the previous query.

I create a temporary table and populate it with the results of the first query and then run a second query on the table. Then I drop the table.

Here's the problem: If I drop the table, the second query is empty as result is discarded. I don't understand this. I don't use temporary table for select, only for data validation. And yet if the table is dropped at the end of the query, the query returns empty. If I don't drop the table it works fine. But if I run the query again it says table exists. What am I doing wrong here. Here's the code:

SELECT DISTINCT gno
INTO TEMPORARY TABLE TMP
FROM edge
WHERE weight>100;

SELECT  gname, gsum(graph.gno)
FROM TMP, graph
WHERE graph.gno = TMP.gno AND gsum(graph.gno)>=ALL
(SELECT gsum(graph.gno)
FROM graph);

Please show the application code that's invoking this SQL. – Craig Ringer Aug 01 '14 at 12:30 — Craig Ringer, Aug 01 '14 at 12:30

score 0 · Accepted Answer · edited May 23 '17 at 10:33

Temporary tables are created in a special temporary schema, unique to each session. They are only visible in the same session and only until the end of the session (unless dropped earlier).

That temporary schema implicitly is the first schema in the search_path (after the system schema pg_catalog), so it effectively hides other tables of the same name. Per documentation:

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.

Once you drop the temp table, the next table with the same name in the search_path becomes visible again. And that table does not have any matching values in its existing gno column. So the result is empty.

That must be the case because, if no such table existed, you wouldn't get an empty result but an error message for the next attempt.

Also, your second query should rather be:

SELECT  gname, gsum(graph.gno)
FROM    tmp
JOIN    graph USING (gno)
WHERE   gsum(graph.gno) = (
   SELECT max(gsum(graph.gno))
   FROM   graph
   );

With explicit JOIN syntax and simplified WHERE condition.
Assuming gsum() is a plain function, not an aggregate function.

PostgreSQL if temporary table is dropped query is empty

1 Answers1