SELECT INTO in PostgreSQL

SELECT INTO is a PostgreSQL extension that combines a SELECT query with a table-creation step: it creates a new table and fills it with the query’s result set in a single statement. It is commonly used for ad-hoc data exploration, creating working copies, and staging data for pipeline processing.

Syntax

SELECT select_list
INTO [TEMPORARY | TEMP] [TABLE] new_table_name
FROM source_table
[WHERE condition];

• The new table inherits column names and data types from the SELECT output.
• TEMP or TEMPORARY creates a session-scoped table dropped automatically at session end.
• The TABLE keyword is optional and exists only for readability.
• Any SELECT clause is valid: JOIN, GROUP BY, HAVING, ORDER BY, subqueries.

Note: You cannot use SELECT INTO inside a PL/pgSQL function — PL/pgSQL interprets INTO as a variable assignment. Use CREATE TABLE AS in that context.

Practical Example

Create a snapshot of in-progress orders for analysis:

-- Source tables
CREATE TABLE orders (
  order_id     SERIAL PRIMARY KEY,
  customer_ref VARCHAR(50) NOT NULL,
  status       VARCHAR(20) NOT NULL,
  total        NUMERIC(10,2) NOT NULL,
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Snapshot active orders into a working table
SELECT order_id, customer_ref, total, placed_at
INTO TABLE active_orders_snapshot
FROM orders
WHERE status = 'in_progress'
ORDER BY placed_at;

Verify the snapshot:

SELECT * FROM active_orders_snapshot LIMIT 5;

Create a temporary staging table for a single session:

SELECT order_id, total
INTO TEMP TABLE order_totals_tmp
FROM orders
WHERE placed_at >= now() - INTERVAL '30 days';

-- Use it within the session
SELECT SUM(total) FROM order_totals_tmp;

Inspect the new table — no indexes will be present:

\d active_orders_snapshot

SELECT INTO vs CREATE TABLE AS

For all new code, prefer CREATE TABLE AS.

Testing with Vela

When building a data pipeline that stages intermediate results with SELECT INTO, Vela database branching lets you test the full pipeline on a production-sized copy of the database before running it against live data. Create a branch, execute your staging workflow, validate the result, then tear down the branch — no risk to production data.

Production Tips

• Prefer CREATE TABLE AS in application code and migration scripts — it is standard, works in functions, and supports IF NOT EXISTS.
• SELECT INTO is best suited for quick ad-hoc work in psql or notebook-style sessions.
• The new table does not inherit indexes, primary key, or foreign key constraints — add them explicitly after creation.
• Use TEMP for intermediate computation tables within a session to avoid cluttering the permanent schema.
• Use RETURNING in your source INSERT statements to feed SELECT INTO directly without re-querying when building audit trails.