Try a preview of AI-powered search below!

You can create a temporary table, a temporary view, or a temporary sequence by using dedicated syntax.

Start a new session as an ordinary role that has all privileges on the current database. Try this:

-- For example, connect as the role "d0$u0" to the database "d0".
\c d0 d0$u0

create temporary table    t(k int, v int) on commit delete rows;
create temporary view     v as select k, v from t where k > 0;
create temporary sequence s as int start with 10 increment by 5;
create           index    i on pg_temp.t(k);

Check the outcome thus:

prepare qry as
with c(name, kind, is_my_temp, schema, owner) as (
  select
    c.relname,
    case c.relkind
      when 'r' then 'table'
      when 'v' then 'view'
      when 'S' then 'sequence'
      when 'i' then 'index'
      else          'other'
    end,
    c.relnamespace = pg_my_temp_schema(),
    n.nspname,
    r.rolname
  from
    pg_class as c
    inner join
    pg_namespace as n
    on c.relnamespace = n.oid
    inner join
    pg_roles as r
    on c.relowner = r.oid)
select name, kind, is_my_temp::text, schema
from c
where owner = current_role
order by (replace(schema::text, 'pg_temp_', ''))::int;
execute qry;

This is the result:

 name |   kind   | is_my_temp |  schema   
------+----------+------------+-----------
 t    | table    | true       | pg_temp_2
 v    | view     | true       | pg_temp_2
 s    | sequence | true       | pg_temp_2
 i    | index    | true       | pg_temp_2

Notice that the pg_class catalog table has no column that denotes the status of the listed schema-objects as temporary or permanent. Rather, you infer this status from the schema where the object lives. The pg_my_temp_schema() builtin-function (see the PostgreSQL documentation section System Information Functions) returns the oid of the current session's temporary schema, or zero if it has no temporary schema. Temporary schema names start with pg_temp_ and then one or a few digits. You might see something other than "2" when you try this test.

When you use the temporary keyword, you cannot denote the to-be-created schema-object with a qualified identifier because its temporary status implies that it will live in the current session's temporary schema. Such schemas are not created as a side effect of creating a database. And nor is there syntax to let you create a temporary schema later. Rather, it's created as a side-effect of creating a session's first temporary schema-object. Moreover, you cannot predict what digits will be appended to pg_temp_ to form its name. However, you can use the alias pg_temp in a schema-qualified identifier to denote whatever temporary schema you end up with. This gives you a clue to an alternative syntax to create temporary tables, views, and synonyms. Try this:

drop table    if exists pg_temp.t cascade;
drop view     if exists pg_temp.v cascade;
drop sequence if exists pg_temp.s cascade;

create table    pg_temp.t(k int, v int) on commit delete rows;
create view     pg_temp.v as select k, v from t where k > 0;
create sequence pg_temp.s as int start with 10 increment by 5;

create index            i on pg_temp.t(k);

execute qry;

The result of execute qry is exactly the same as when you created the temporary schema-objects with the syntax that uses the temporary keyword.