Can my DB?

-- Session-scoped (default)
CREATE TEMPORARY TABLE session_work (
  id SERIAL PRIMARY KEY,
  value TEXT
);

-- Transaction-scoped: rows deleted at COMMIT
CREATE TEMP TABLE txn_work (id INT)
  ON COMMIT DELETE ROWS;

-- Transaction-scoped: table dropped at COMMIT
CREATE TEMP TABLE txn_work2 (id INT)
  ON COMMIT DROP;

• Lives in a session-specific pg_temp_N schema that sits in front of the search path.
• ON COMMIT clause choices: PRESERVE ROWS (default), DELETE ROWS (empty at each COMMIT), DROP (drop at transaction end).
• Indexes, constraints, and triggers all work.

CREATE TEMPORARY TABLE session_work (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(255)
);

-- Insert and query as a normal table
INSERT INTO session_work (value) VALUES ('hello');
SELECT * FROM session_work;

-- Explicit cleanup (optional - dropped on session end)
DROP TEMPORARY TABLE IF EXISTS session_work;

• Session-scoped. A temporary table shadows a regular table of the same name within that session.
• InnoDB or MEMORY engine. Foreign keys cannot reference a temporary table.

CREATE TEMPORARY TABLE session_work (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(255)
);

DROP TEMPORARY TABLE IF EXISTS session_work;

• Same session-scoped behavior as MySQL. Aria, InnoDB, and MEMORY are all supported engines.

-- Transaction-scoped data (deleted at COMMIT)
CREATE GLOBAL TEMPORARY TABLE session_work (
  id NUMBER,
  value VARCHAR2(255)
) ON COMMIT DELETE ROWS;

-- Session-scoped data (deleted at session end)
CREATE GLOBAL TEMPORARY TABLE session_work2 (
  id NUMBER,
  value VARCHAR2(255)
) ON COMMIT PRESERVE ROWS;

• Global Temporary Tables (GTTs) keep the definition permanent and shared, with data private per session.
• ON COMMIT DELETE ROWS makes data transaction-scoped; ON COMMIT PRESERVE ROWS makes it session-scoped.
• 18c+ adds Private Temporary Tables (CREATE PRIVATE TEMPORARY TABLE) where the definition is also per-session.

-- Local temp table (session-scoped, # prefix)
CREATE TABLE #session_work (
  id INT IDENTITY PRIMARY KEY,
  value NVARCHAR(255)
);

-- Global temp table (## prefix)
CREATE TABLE ##shared_work (
  id INT,
  value NVARCHAR(255)
);

-- Table variable (scope-local, no tempdb stats)
DECLARE @work TABLE (
  id INT,
  value NVARCHAR(255)
);

• #name is session-scoped; ##name is global, visible to every session and dropped when the creator exits and no one is still referencing it.
• Table variables (DECLARE @t TABLE) are batch-scoped; they can't carry statistics and only support a primary-key index.
• #name supports full indexing, statistics, and use in dynamic SQL.

CREATE TEMPORARY TABLE session_work (
  id INTEGER PRIMARY KEY,
  value TEXT
);

INSERT INTO session_work (value) VALUES ('hello');
SELECT * FROM session_work;

DROP TABLE IF EXISTS session_work;

• Lives in a separate temp database (in-memory or temp file) distinct from the main database.
• Can be qualified as temp.name to disambiguate. Supports the same DDL as regular tables.

CREATE TEMPORARY TABLE session_work (
  id INT,
  value VARCHAR(255)
);

-- Also accepted
CREATE TEMP TABLE session_work2 (
  id INT,
  value VARCHAR(255)
);

• Session-scoped; lives in the pg_temp schema that fronts the search path.
• Accepts DISTKEY, SORTKEY, and DISTSTYLE like regular Redshift tables.

-- Session-scoped, rows preserved across commits
DECLARE GLOBAL TEMPORARY TABLE session_work (
  id INT,
  value VARCHAR(255)
) ON COMMIT PRESERVE ROWS
NOT LOGGED;

INSERT INTO session.session_work VALUES (1, 'hello');
SELECT * FROM session.session_work;

• Spelled DECLARE GLOBAL TEMPORARY TABLE (DGTT). Unlike Oracle GTTs, the definition itself is session-scoped.
• Requires a USER TEMPORARY tablespace. NOT LOGGED skips the transaction log for big write throughput.
• ON COMMIT PRESERVE/DELETE ROWS controls whether data survives a COMMIT.

-- Temporary table (session-scoped)
CREATE TEMPORARY TABLE session_work (
  id INT,
  value STRING
);

-- Transient table (persistent but no Fail-safe, lower storage cost)
CREATE TRANSIENT TABLE transient_work (
  id INT,
  value STRING
);

• Session-scoped and not visible to other sessions.
• TRANSIENT tables live past the session but skip Fail-safe, making them cheaper to store.
• Time Travel on TEMPORARY and TRANSIENT is capped at 1 day (vs. up to 90 days for permanent tables on Enterprise).

-- Temp table within a script or multi-statement transaction
CREATE TEMP TABLE session_work AS
  SELECT id, value FROM mydataset.source_table;

SELECT * FROM session_work;

-- Time-expiring table as an alternative
CREATE TABLE mydataset.expiring_work (id INT64, value STRING)
OPTIONS(expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY));

• CREATE TEMP TABLE is only valid inside a multi-statement script or transaction; dropped when the script ends.
• For longer-lived scratch tables, use a regular CREATE TABLE with the expiration_timestamp option.
• TEMP tables do not count against dataset storage quotas.