SQL PRESERVE

What is the SQL PRESERVE keyword used for?

PRESERVE tells the database to keep data or formatting intact during an operation, most notably to keep rows in a temporary table after COMMIT.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PRESERVE: PostgreSQL, Oracle, Db2, Snowflake, Redshift. Not supported in MySQL, SQL Server, or SQLite.

SQL PRESERVE Full Explanation

PRESERVE is a reserved word defined in the SQL Standard and implemented by several databases to indicate that something should be retained instead of discarded or reformatted. The most common appearance is in the clause ON COMMIT PRESERVE ROWS when creating a temporary table. In that context, PRESERVE instructs the database engine to keep the rows that were inserted into the temporary table even after a COMMIT statement is issued. The rows remain visible for the lifetime of the session and are deleted automatically only when the session ends or the temporary table is dropped.Other vendor-specific uses exist (for example, PRESERVE BLANKS in Db2 and PRESERVE ROWS in Oracle materialized views), but they share the same semantic idea: do not remove or collapse the referenced data. Because PRESERVE is not a standalone statement, it must appear inside a larger DDL or DML command. Attempting to run PRESERVE by itself results in a syntax error.

SQL PRESERVE Syntax

CREATE [GLOBAL] TEMPORARY TABLE table_name (
    column_defs
) ON COMMIT PRESERVE ROWS;

SQL PRESERVE Parameters

Example Queries Using SQL PRESERVE

-- Keep rows in a session-scoped temporary table
CREATE TEMP TABLE temp_sales (
    sale_id   INT,
    amount    NUMERIC(10,2)
) ON COMMIT PRESERVE ROWS;

INSERT INTO temp_sales VALUES (1, 99.99);
COMMIT;  -- rows are still present

SELECT * FROM temp_sales;  -- returns 1 row

-- Contrast: rows disappear after COMMIT without PRESERVE
CREATE TEMP TABLE temp_log (
    msg TEXT
) ON COMMIT DELETE ROWS;

Expected Output Using SQL PRESERVE

Rows inserted into temp_sales stay available after COMMIT for the rest of the session
Rows in temp_log are automatically removed at each COMMIT

Use Cases with SQL PRESERVE

Keep intermediate results in a temporary table across multiple transactions in the same session
Maintain staging data while running several reports that all need the same temporary dataset
Compare behavior with ON COMMIT DELETE ROWS to decide whether data should persist or be cleared automatically

Common Mistakes with SQL PRESERVE

Running PRESERVE as a standalone command
Expecting rows to survive a disconnect; they survive only until the session ends
Using PRESERVE in dialects that do not implement ON COMMIT PRESERVE ROWS (for example, MySQL)
Confusing PRESERVE with the standard COMMIT statement

First Introduced In

SQL:1999 (temporary table definition)

Frequently Asked Questions

What is the difference between ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS?

PRESERVE keeps rows in the temporary table after each COMMIT, while DELETE removes them automatically. Choose PRESERVE when you need the data for multiple transactions.

Do I have to drop the temporary table manually when using PRESERVE?

No. The table is dropped automatically at session end unless you explicitly execute DROP TABLE.

Can I combine PRESERVE with GLOBAL TEMPORARY TABLE?

Yes. In Oracle and Standard SQL you may write CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS; to make the table visible to all sessions but keep each session’s data separate.

Why do I get a syntax error near PRESERVE?

Your database dialect might not support the PRESERVE clause, or you placed it outside the ON COMMIT clause. Verify that the command is supported and that PRESERVE appears immediately after ON COMMIT.