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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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