In PostgreSQL, the SELECT INTO statement allows users to create a new table and inserts data returned by a query. The new table columns have names and data types linked with the output columns of the SELECT clause. Unlike the SELECT statement, the SELECT INTO statement does not return data to the client.
Syntax: SELECT column_list INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table_name FROM table_name WHERE condition;
In the process of creating a new table with the structure and data derived from a query result set, one has to specify the new table name in the INTO clause. The TEMP or TEMPORARY keyword is optional; it allows users to create a temporary table instead.
The WHERE clause allows users to specify the data from the original tables that should be inserted into the new table. Besides the WHERE clause, one can use other clauses in the SELECT statement for the SELECT INTO statement such as INNER JOIN, LEFT JOIN, GROUP BY, and HAVING.
In this example, we will use the
film table from the
dvdrental database to create a new table named
film_r that contains all films with the rating of
R and has a 5-day rental duration using the below statement:
SELECT film_id, title, rental_rate INTO TABLE film_r FROM film WHERE rating = 'R' AND rental_duration = 5 ORDER BY title;
Now we can verify the created table using the below statement:
SELECT * FROM film_r;
In this example, we create a temporary table named
short_film that has all films whose lengths are under 60 minutes using the below statement:
SELECT film_id, title, length INTO TEMP TABLE short_film FROM film WHERE length < 60 ORDER BY title;
Now we verify the table short_film using the below statement:
SELECT * FROM short_film;
Note: One cannot use the SELECT INTO statement in PL/pgSQL or ECPG because they interpret the INTO clause differently. In this case, you can use the CREATE TABLE AS statement which provides more functionality than the SELECT INTO statement.