PostgreSQL – SELECT INTO
Last Updated :
28 Aug, 2020
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.
Now let’s look into some examples.For examples we will be using the sample database (ie, dvdrental).
Example 1:
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;
Output:
Example 2:
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;
Output:
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.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...