Select Into

SELECT INTO is a PostgreSQL extension to the standard SELECT statement that creates a new table and fills it with data from a query in a single step. Unlike a regular SELECT, it does not return rows to the client — instead it writes them directly into the new table. This is useful for creating working copies of data, archiving subsets of rows, or building staging tables for data processing pipelines.

SELECT INTO 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 clause output.
• The TEMP or TEMPORARY keyword creates a temporary table that is automatically dropped at the end of the session.
• The TABLE keyword is optional and only improves readability.
• You can use JOIN, GROUP BY, HAVING, and other SELECT clauses.

Note: You cannot use SELECT INTO inside a PL/pgSQL function because PL/pgSQL interprets the INTO clause as an assignment to a variable. Use CREATE TABLE AS instead in that context.

SELECT INTO examples

Example 1 — create a permanent table from a filtered query:

SELECT film_id, title, rental_rate
INTO TABLE film_r
FROM film
WHERE rating = 'R'
  AND rental_duration = 5
ORDER BY title;

Query the new table to verify the data:

SELECT * FROM film_r LIMIT 5;

Example 2 — create a temporary table for short films:

SELECT film_id, title, length
INTO TEMP TABLE short_film
FROM film
WHERE length < 60
ORDER BY title;

Check the results:

SELECT * FROM short_film ORDER BY length DESC;

 film_id |        title         | length
---------+----------------------+--------
     486 | Jet Neighbors        |     59
     465 | Interview Liaisons   |     59
     409 | Heartbreakers Bright |     59
     947 | Vision Torque        |     59
...

SELECT INTO tips

• Prefer CREATE TABLE AS over SELECT INTO for new code — it is SQL-standard, works inside PL/pgSQL, and supports IF NOT EXISTS and UNLOGGED options.
• The new table created by SELECT INTO does not inherit indexes, constraints, or sequences from the source table — add them explicitly after creation.
• Use TEMP TABLE for intermediate computations within a session to avoid polluting the schema with one-off tables.
• Name the target table clearly to distinguish permanent archive tables from ad-hoc working copies.

Reference: PostgreSQL documentation — SELECT INTO.