Skip site navigation (1) Skip section navigation (2)

Re: Naive schema questions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: btober(at)computer(dot)org
Cc: JanWieck(at)Yahoo(dot)com, rory(at)campbell-lange(dot)net,pgsql-general(at)postgresql(dot)org
Subject: Re: Naive schema questions
Date: 2004-05-28 04:47:39
Message-ID: 12076.1085719659@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
<btober(at)computer(dot)org> writes:
> And where do tables created with "CREATE LOCAL TEMPORARY TABLE..." fit
> into this, like if say a local temp table where created that has the same
> name as an existing normal (i.e., not a local temp) table?

Temp tables live in a schema that is effectively inserted into your
search path ahead of whatever schema(s) are explicitly listed there.
For instance, suppose

	CREATE SCHEMA a;
	SET search_path = a, public;
	CREATE TABLE t1 (...);
	-- t1 is created in schema a
	SELECT * FROM t1;
	-- same as SELECT * FROM a.t1;

Now if I do

	CREATE TEMP TABLE t1 (...);

then SELECT * FROM t1 will reference the temp table ... but I can still
get to the permanent table by explicitly qualifying it as "a.t1".

> And what if I
> do an explicit DROP of the local temp table rather than relying on the
> automatic, end-of-session clean-up?

You drop the temp table.

> Is there any risk of losing the normal table?

Only if you do it twice --- after the initial DROP, the permanent table
would come back "into view".

			regards, tom lane

In response to

pgsql-general by date

Next:From: Klint GoreDate: 2004-05-28 04:51:55
Subject: query results different after vacuum analyze? 7.4.0
Previous:From: PostgresqlDate: 2004-05-28 02:35:36
Subject: Re: PostgreSQL Logs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group