Where is temporary table created?

Where can I find created temporary ta开发者_如何学Cble in posgresql folders? If I do select * from temp_table; then I got result, but cannot see it structure of my database in the PgAdmin?

Temporary tables get put into a schema called "pg_temp_NNN", where "NNN" indicates which server backend you're connected to. This is implicitly added to your search path in the session that creates them.

Note that you can't access one connection's temp tables via another connection... so depending on how exactly pgAdmin organises its connections, even being able to find the tables in the object explorer might not be useful.

Here is one way to get the name of the pg_temp_nnn schema for your session:

select distinct 'pg_temp_'||sess_id from pg_stat_activity where procpid = pg_backend_pid()

This will identify the session that is running that SQL statement itself, and returns the session id that it is running under.

You can then use this to list all your temporary tables:

select * 
from information_schema.tables 
where table_schema = 
  ( select distinct 'pg_temp_'||sess_id 
    from pg_stat_activity 
    where procpid = pg_backend_pid()
  )

Or to get the table structure:

select * 
from information_schema.columns 
where table_schema = 
  ( select distinct 'pg_temp_'||sess_id 
    from pg_stat_activity 
    where procpid = pg_backend_pid()
  )
and table_name = 'my_temp_table'
order by ordinal_position