Thread: Temporary schemas
Hello, I have created a temporary table using create temporary table foo ( id integer ); and noticed this was created in a schema called "pg_temp_2" My question is: is this always "pg_temp_2"? Or will the name of the "temp schema" change? If it isn't always the same, is there a way I can retrieve the schema name for temporary tables? Regards Thomas
On 1 November 2010 10:46, Thomas Kellerer <spam_eater@gmx.net> wrote:
Hello,
I have created a temporary table using
create temporary table foo
(
id integer
);
and noticed this was created in a schema called "pg_temp_2"
My question is:
is this always "pg_temp_2"?
Or will the name of the "temp schema" change?
If it isn't always the same, is there a way I can retrieve the schema name for temporary tables?
Regards
Thomas
You can use:
SELECT nspname
FROM pg_namespace
WHERE oid = pg_my_temp_schema();
to get the name of the current temporary schema for your session.
And it's always pg_temp_[nnn] as far as I'm aware, with a corresponding pg_toast_temp_[nnn] schema.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
Thom Brown wrote on 01.11.2010 12:33: > You can use: > > SELECT nspname > FROM pg_namespace > WHERE oid = pg_my_temp_schema(); > > to get the name of the current temporary schema for your session. Thanks that's what I was looking for. Regards Thomas
On Mon, Nov 1, 2010 at 6:46 AM, Thomas Kellerer <spam_eater@gmx.net> wrote: > Hello, > > I have created a temporary table using > > create temporary table foo > ( > id integer > ); > > and noticed this was created in a schema called "pg_temp_2" > > My question is: > > is this always "pg_temp_2"? > Or will the name of the "temp schema" change? > > If it isn't always the same, is there a way I can retrieve the schema name > for temporary tables? Curious why you'd want to do this -- temporary magic schemas are an implementation artifact, and there shouldn't ever be a reason to directly reference them. merlin
Merlin Moncure wrote on 01.11.2010 21:13: > On Mon, Nov 1, 2010 at 6:46 AM, Thomas Kellerer<spam_eater@gmx.net> wrote: >> Hello, >> >> I have created a temporary table using >> >> create temporary table foo >> ( >> id integer >> ); >> >> and noticed this was created in a schema called "pg_temp_2" >> >> My question is: >> >> is this always "pg_temp_2"? >> Or will the name of the "temp schema" change? >> >> If it isn't always the same, is there a way I can retrieve the schema name >> for temporary tables? > > Curious why you'd want to do this -- temporary magic schemas are an > implementation artifact, and there shouldn't ever be a reason to > directly reference them. > Yes and no ;) The problem is, that the JDBC driver only returns information about the temp tables, if I specify that schema directly. Thomas
On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer <spam_eater@gmx.net> wrote: > The problem is, that the JDBC driver only returns information about the temp > tables, if I specify that schema directly. Have you filed a bug report to jdbc yet? :-D. merlin
On 11/01/2010 04:13 PM, Merlin Moncure wrote: > On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer <spam_eater@gmx.net> wrote: >> The problem is, that the JDBC driver only returns information about the temp >> tables, if I specify that schema directly. > > Have you filed a bug report to jdbc yet? :-D. > > merlin > But can you consider temp tables as part of the schema when they last only the duration of the session?
Merlin Moncure wrote on 01.11.2010 23:13: > On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer<spam_eater@gmx.net> wrote: >> The problem is, that the JDBC driver only returns information about the temp >> tables, if I specify that schema directly. > > Have you filed a bug report to jdbc yet? :-D. I thought about it initially, but then realized that it works as documented by the JDBC API. When requesting the table information without specifying a schema, it is returned. But in my application I use the current schema to request information about non-qualified tables which obviously fails asthe current schema is usually public or another user schema but never pg_temp_xxx. So even though a select from a temptable (whithout a schema) works fine from within JDBC, retrieving metadata only works when either specifying no schema,or the correct one - which is a bit confusing but absolutely according to the JDBC specs. Regards Thomas
Thanks! very useful for me! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-schemas-tp3244865p5742352.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.