Referencing a global temp with same name as local temp table

[Josh]

Hello,

If I create a global and local temp with the same name, when I query the table name I get the local temp table.

Code: Select all

dbadmin=> create global temp table temp_table (g_a int);
CREATE TABLE
dbadmin=> create local temp table temp_table (l_a int);
CREATE TABLE
dbadmin=> select * from temp_table;
 l_a
-----
(0 rows)

So how can I reference the global temp table?

[JimKnicely]

Hi,

You can specify a schema for for a global temp table but not a local temp table. Locale temp tables are always created in the v_temp_schema schema.

If you do not specify a schema name for the global temp table it will be created in the default schema in your search path. Most likely the public schema.

Try checking v_catalog.tables for the schema names of your temp tables:

select table_schema from tables where table_name = 'temp_table';

[Josh]

Great explanation, Jim! Thanks for your help