---
title: "Temporary tables, views, sequences, and indexes"
url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/creating-and-using-temporary-schema-objects/temporary-tables-views-sequences-and-indexes/"
---

# Temporary tables, views, sequences, and indexes

Describes the dedicated syntax for creating temporary tables, views, and sequences. Shows that the index on a temporary table is necessarily temporary. [YSQL]

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

There's no dedicated syntax to create a temporary index.

The [*create index*](/stable/api/ysql/the-sql-language/statements/ddl_create_index/ "create index") statement does not accept a schema-qualified identifier for the to-be-created index. This reflects the fact that an index is always, non-negotiably, in the same schema as the table upon whose column(s) it's created. As an extension of this, an index that's created on a temporary table's column(s) will be in the session's temporary schema—in other words, it will be a temporary index.

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

```plpgsql
-- 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:

```plpgsql
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:

```output
 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](https://www.postgresql.org/docs/15/functions-info.html "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:

```plpgsql
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.