Temporary table in pgAdmin

I am using pgAdmin for my Postgres 8.4 database and I was wondering where (any table/schema/etc. ?) may I find a list of currently used temporary tables? I assume there has to be a place where I can find it.

They are not present in a catalog object tables nor in views, any othe开发者_开发百科r suggestions?

Postgres creates a temporary schema for temporary tables named "pg_temp_#", you can see it with psql...

create temp table mytemptable(name varchar);

select c.relname
from pg_namespace n
  join pg_class   c on n.oid=c.relnamespace
where n.nspname='pg_temp_1';

You can list your schemas executing "\dn" in psql.

Hope that helps.

Temporary tables live in temporary schemas pg_temp_{№} that hidden by default in pgAdmin UI.

In pgAdmin(pgAdmin4 at least) you can open preferences pane and switch on this setting:

Display->Show system objects? to True

This will show hidden schemas with your created temp tables.

PS After changing preference refresh schema tree

In pgAdmin 4 you just need to enable in

Properties -> Browser -> Display -> Show system objects

(this one first from the bottom). After this you would be able to find your table inside

pg_temp_(#) schema

https://www.dbrnd.com/2017/06/postgresql-find-a-list-of-active-temp-tables-with-size-and-user-information-idle-connection/

SELECT
    n.nspname as SchemaName
    ,c.relname as RelationName
    ,CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
    END as RelationType
    ,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner               
    ,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n               
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s') 
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC

Will show you output of all temporary tables.

 schemaname | relationname | relationtype | relationowner | relationsize
------------+--------------+--------------+---------------+--------------
 pg_temp_63 | temp_sl_4    | table        | power_bi_cr   | 2355 MB
 pg_temp_63 | temp_sl_3    | table        | power_bi_cr   | 1342 MB
 pg_temp_63 | temp_sl_2    | table        | power_bi_cr   | 1239 MB
 pg_temp_63 | temp_sl      | table        | power_bi_cr   | 1216 MB
 pg_temp_63 | temp_sl_gr   | table        | power_bi_cr   | 521 MB
 pg_temp_63 | temp_ftlo    | table        | power_bi_cr   | 457 MB
 pg_temp_63 | temp_th3     | table        | power_bi_cr   | 123 MB
 pg_temp_63 | temp_th      | table        | power_bi_cr   | 79 MB
 pg_temp_63 | temp_th2     | table        | power_bi_cr   | 17 MB
(9 rows)