SQL LOCAL

What is the SQL LOCAL keyword?

LOCAL marks an object or setting as scoped to the current session or transaction, most commonly for temporary tables or parameter changes.

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 LOCAL: PostgreSQL (SET LOCAL only, temp tables implied with TEMP), DB2, MariaDB, Derby, H2, SQL Standard compliant engines. Not directly supported in MySQL or SQLite, and SQL Server uses alternative syntax (#temp tables).

SQL LOCAL Full Explanation

In the SQL standard, LOCAL is a scope modifier that limits the lifetime and visibility of the object or setting it qualifies to the current SQL-session or current transaction. The most frequent appearance is in temporary table definitions (CREATE LOCAL TEMPORARY TABLE or DECLARE LOCAL TEMPORARY TABLE). Such a table is instantiated only for the connecting session, is invisible to other sessions, and is automatically dropped at session end (or optionally at COMMIT, depending on the ON COMMIT clause).Some dialects, notably PostgreSQL, also allow LOCAL in SET statements (SET LOCAL parameter = value) to change configuration parameters only for the running transaction. Once the transaction ends, the parameter reverts to its previous value.Key points:- LOCAL implies session-local or transaction-local scope.- Used mainly with temporary tables, but also with parameter changes.- Contrasts with GLOBAL (shared scope) or a plain setting that lasts for the whole session.- Lifetime ends automatically, so no explicit DROP is required.- Behavior can vary slightly by dialect: verify exact support and syntax.

SQL LOCAL Syntax

-- Standard / ISO style temporary table
CREATE LOCAL TEMPORARY TABLE table_name (
    column1 datatype,
    column2 datatype
) ON COMMIT PRESERVE ROWS;

-- DB2 style declaration
DECLARE LOCAL TEMPORARY TABLE session.table_name (
    id INT,
    created_at TIMESTAMP
);

-- PostgreSQL parameter change scoped to the current transaction
SET LOCAL work_mem = '128MB';

SQL LOCAL Parameters

Example Queries Using SQL LOCAL

-- Example 1: Session-local temporary table for intermediate results
CREATE LOCAL TEMPORARY TABLE tmp_sales AS
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id;

-- Use the temp table in further logic
SELECT * FROM tmp_sales WHERE total > 1000;

-- Example 2: Transaction-local GUC change (PostgreSQL)
BEGIN;
SET LOCAL statement_timeout = '5s';
-- long query runs here but times out after 5 seconds
COMMIT;

Expected Output Using SQL LOCAL

Example 1 creates a table visible only to the current session
Data can be queried within the session and disappears automatically when the session ends
Example 2 sets statement_timeout to 5 seconds for the current transaction; after COMMIT, the parameter reverts to its previous value

Use Cases with SQL LOCAL

Building complex reports that require staging data without polluting the main schema.
Running ETL steps where intermediate results should remain private to the session.
Safely adjusting configuration parameters for a single transaction without affecting other users.
Creating scratch tables in ad-hoc analysis or automated test suites.

Common Mistakes with SQL LOCAL

Assuming the temporary table persists after the session closes.
Forgetting ON COMMIT behavior, which may drop or truncate the table unexpectedly.
Using LOCAL where the dialect supports only GLOBAL (or vice-versa).
Thinking SET LOCAL affects the entire session instead of just the active transaction.

First Introduced In

SQL:1999

Frequently Asked Questions

What is the purpose of LOCAL in a temporary table definition?

LOCAL restricts the temporary table to the creating session, ensuring other sessions cannot see or modify it.

Will a LOCAL TEMPORARY table survive a COMMIT?

It depends on the ON COMMIT clause. ON COMMIT PRESERVE ROWS keeps the data; ON COMMIT DELETE ROWS truncates it; ON COMMIT DROP removes the table.

Can I drop a LOCAL TEMPORARY table manually?

Yes. Use DROP TABLE table_name; inside the session if you need to reclaim resources earlier.

Does SET LOCAL affect the whole session?

No. SET LOCAL changes the parameter only for the current transaction. Once the transaction ends, the previous setting is restored.