Using the YSQL API, temporary tables exist in their own unique schema, so you can’t assign a schema name when you create this kind of table. A temporary table only exists as long as its database session – this means that YugabyteDB automatically drops the temporary table at the end of the session.
yugabyte=# CREATE TEMP TABLE t1 (c1 INT);
CREATE TABLE
yugabyte=# SELECT schemaname FROM pg_tables WHERE tablename = 't1';
schemaname
------------
pg_temp_1
(1 row)
When multiple sessions are creating temporary tables, they are all logged in the PG_TABLES system tables.
yugabyte=# CREATE TEMP TABLE t1 (c1 INT);
CREATE TABLE
yugabyte=# SELECT schemaname FROM pg_tables WHERE tablename = 't1';
schemaname
------------
pg_temp_1
(1 row)
yugabyte=# \! ysqlsh
ysqlsh (11.2-YB-2.15.1.0-b0)
Type "help" for help.
yugabyte=# CREATE TEMP TABLE t1 (c1 INT);
CREATE TABLE
yugabyte=# SELECT schemaname FROM pg_tables WHERE tablename = 't1';
schemaname
------------
pg_temp_1
pg_temp_2
(2 rows)
yugabyte=# \! ysqlsh
ysqlsh (11.2-YB-2.15.1.0-b0)
Type "help" for help.
yugabyte=# CREATE TEMP TABLE t1 (c1 INT);
CREATE TABLE
yugabyte=# SELECT schemaname FROM pg_tables WHERE tablename = 't1';
schemaname
------------
pg_temp_1
pg_temp_2
pg_temp_3
(3 rows)
So how do you know which schema contains your session’s temporary tables? For that answer, we can look to the pg_my_temp_schema() function!
yugabyte=# SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema();
nspname
-----------
pg_temp_3
(1 row)
yugabyte=# \q
yugabyte=# SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema();
nspname
-----------
pg_temp_2
(1 row)
yugabyte=# \q
yugabyte=# SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema();
nspname
-----------
pg_temp_1
(1 row)
Have Fun!