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.
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table_name
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).
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:
INTO TABLE film_r
rating = 'R'
AND rental_duration = 5
Now we can verify the created table using the below statement:
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:
INTO TEMP TABLE short_film
length < 60
Now we verify the table short_film using the below statement:
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.
Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses
are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!