Обсуждение: how to select temp table

ver 8.4
i create two table with same name  named 'testtable'

one is temp table

 

 i select * from testtable
then this table is a public  or a temp ???
drop table testtable 

then is this table a public or a temp? i test ,it drop temp table fisrst!

so am i  must show  public or pg_temp_1  when i has two table with same name !!!
 

On 2009-05-06 14.34, liuzg4 liuzg4 wrote:
> ver 8.4
> i create two table with same name  named 'testtable'
>
> one is temp table
>
>
>
>   i select * from testtable
> then this table is a public  or a temp ???

Temp. To access public use "select * from public.testtable".

"Temporary tables exist in a special schema, so a schema name cannot be
given when creating a temporary table."

See http://www.postgresql.org/docs/8.4/static/sql-createtable.html for
details.

/r

> drop table testtable
>
> then is this table a public or a temp? i test ,it drop temp table fisrst!
>
> so am i  must show  public or pg_temp_1  when i has two table with same name
> !!!
>

Robert Gravsjö escribió:
> On 2009-05-06 14.34, liuzg4 liuzg4 wrote:
>> ver 8.4
>> i create two table with same name  named 'testtable'
>>
>> one is temp table
>>
>>   i select * from testtable
>> then this table is a public  or a temp ???
>
> Temp. To access public use "select * from public.testtable".
>
> "Temporary tables exist in a special schema, so a schema name cannot be
> given when creating a temporary table."

Note that you can refer to the temp table like this:

select * from pg_temp.testtable;

Also, you can make the non-temp table first in the search path by
putting pg_temp later than the public schema (or wherever you have
created the function):


select * from testtable;    -- refers to temp
set search_path to 'public', 'pg_temp';
select * from testtable;    -- refers to non-temp

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.