Thread: temp table question

Hi list;

If I create a temp table (i.e. create temp table xyz as select from ...) is
the scope of this table limited to a session. Meaning,  can several sessions
all run the above create temp table statement all referencing the same temp
table name at the same time?


Thanks in advance

Kevin Kempter wrote:
> Hi list;
>
> If I create a temp table (i.e. create temp table xyz as select from
> ...) is the scope of this table limited to a session. Meaning,  can
> several sessions all run the above create temp table statement all
> referencing the same temp table name at the same time?

Yes, temp tables exist outside of the normal schema and are limited to
the session they are created in. I'm not sure quite how it works
internally but my understanding is that it would be something akin to:

your_session.your_temp_table

rather than:

your_schema.your_temp_table

so collisions won't occur.

See:

http://www.postgresql.org/docs/8.2/static/sql-createtable.html

> Although the syntax of CREATE TEMPORARY TABLE resembles that of the
> SQL standard, the effect is not the same. In the standard, temporary
> tables are defined just once and automatically exist (starting with
> empty contents) in every session that needs them. PostgreSQL instead
> requires each session to issue its own CREATE TEMPORARY TABLE command
> for each temporary table to be used. This allows different sessions
> to use the same temporary table name for different purposes, whereas
> the standard's approach constrains all instances of a given temporary
> table name to have the same table structure.

b

On Feb 1, 2008 9:06 PM, Kevin Kempter <kevin@kevinkempterllc.com> wrote:
> Hi list;
>
> If I create a temp table (i.e. create temp table xyz as select from ...) is
> the scope of this table limited to a session. Meaning,  can several sessions
> all run the above create temp table statement all referencing the same temp
> table name at the same time?

Temp tables are only visible to the session that created them.  See

http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html