PostgreSQL – SELECT INTO

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.

My Personal Notes arrow_drop_up

Save