Temporary tables

CREATE TEMP TABLE (or CREATE TEMPORARY TABLE — they are synonyms) declares a session-local scratch table. It behaves like an ordinary table — full INSERT / SELECT / UPDATE / DELETE, plus PRIMARY KEY, NOT NULL, DEFAULT, CHECK, and UNIQUE constraints — with three deliberate differences:

• It makes zero writes to the database file. A temp table lives entirely in memory, outside the durable on-disk image, so creating or filling one never touches the file.
• It is private to your session and is dropped automatically when the session ends.
• Its storage is bounded. A per-session byte budget (temp_buffers) caps how much a session’s temp tables can hold, so a temp table is safe to expose even to untrusted queries.

Constraints work exactly as they do on a persistent table — try these in the panel above:

• UNIQUE — INSERT INTO scratch VALUES (4, 'delta', 10); → error 23505 (n = 10 exists)
• PRIMARY KEY — INSERT INTO scratch VALUES (1, 'dup', 99); → error 23505
• NOT NULL — INSERT INTO scratch VALUES (5, NULL, 99); → error 23502
• CRUD — UPDATE scratch SET tag = 'BETA' WHERE id = 2; DELETE FROM scratch WHERE id = 3; SELECT * FROM scratch ORDER BY id;

Names can’t collide

Unlike PostgreSQL — which lets a temp table shadow a permanent one of the same name — jed precludes overlaps: a temp-table name may not collide with any existing table, index, or sequence (and vice-versa). The conflict is reported at CREATE time:

CREATE TABLE t (id i32 PRIMARY KEY);
CREATE TEMP TABLE t (id i32 PRIMARY KEY);   -- error 42P07: relation already exists

TEMP / TEMPORARY are recognized only between CREATE and TABLE, so a table named temp is still an ordinary persistent table:

CREATE TABLE temp (id i32 PRIMARY KEY);     -- a persistent table called "temp"

Bounded storage

Temp tables retain rows across statements, which the per-statement cost ceiling does not bound — so a session carries a temp_buffers budget (in bytes; the host sets it, default 32 MiB, 0 means unlimited). A write that would push the session’s total temp storage past the budget is rejected with error 54P03 and rolled back, leaving the already-committed rows intact. This makes a temp table a safe, bounded scratch space for untrusted SQL — paired with the per-statement cost limit, a query can be given scratch space without risking unbounded memory.

Indexes

A standalone CREATE INDEX works on a temp table — the index lives in the same in-memory temp snapshot, so it makes no writes to the file, is built and used to speed up queries, and is dropped with its table. Its DDL is gated by the same allow_temp_ddl capability as the table.

Auto-numbered columns

serial / bigserial / smallserial and GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY columns work on a temp table too, exactly as on a persistent table:

CREATE TEMP TABLE log (id bigserial PRIMARY KEY, msg text NOT NULL);
INSERT INTO log (msg) VALUES ('first'), ('second');   -- id auto-numbers 1, 2

The auto-created backing sequence lives in the same in-memory temp snapshot, so — like the temp table’s rows and indexes — it makes zero writes to the file, and it is dropped automatically when the table is (DROP TABLE removes both). It is reachable by its derived name (nextval('log_id_seq'), currval, setval), shares the relation namespace (a name collision is 42P07). Its DDL is gated by the same allow_temp_ddl capability.

Composite-typed columns

A column of a user-defined composite (row) type works on a temp table too, exactly as on a persistent table:

CREATE TYPE addr AS (street text, zip i32);
CREATE TEMP TABLE person (id i32 PRIMARY KEY, home addr);
INSERT INTO person VALUES (1, ROW('Main', 90210));
SELECT (home).zip FROM person WHERE id = 1;   -- 90210

The composite type itself is always permanent — CREATE TYPE is ordinary persistent DDL — and a temp table only references it. The column’s storage codec is resolved against the type catalog when the table is created and is self-contained thereafter, so a temp table with a composite column still makes zero writes to the file. ROW(...) construction, rendering, field access, and comparison/ordering all behave exactly as on a persistent column. Because a composite value is never a key, a composite column cannot be a PRIMARY KEY (0A000). Dropping a type that a temp table still references is blocked (2BP01), just as for a permanent table.

Not yet supported on a temp table

This release keeps a few things off temp tables (each reported as 0A000, feature not supported), to be lifted in later releases: FOREIGN KEY constraints and COLLATE columns.