PostgreSQL SELECT INTO - Creating a table from a result set

The PostgreSQL SELECT INTO statement allows you to create a new table from the result set of a query and insert the result set into the new table, and it does the same thing as the CREATE TABLE ... AS statement.

PostgreSQL SELECT INTO syntax

To create a new table using a PostgreSQL SELECT INTO statement, follow this syntax:

SELECT column_list
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[other_clauses]

Explanation:

• The column_list is a list of columns or expressions in the query to return. These columns will be those columns in the new table. You can use DISTINCT here.

• The new_table after the INTO keyword is the name of the table to be created. The TEMPORARY or TEMP indicates that the new table is a temporary table . The TABLE keyword can be omitted.

• The other_clauses are available clauses in the SELECT statement, this includes:

  • FORM clause
  • WHERE clause
  • ORDER BY clause
  • GROUP BY clause
  • HAVING clause
  • LIMIT clause
  • FETCH clause
  • OFFSET clause
  • Set operations: UNION, INTERSECT, orEXCEPT
  • Table Join

PostgreSQL SELECT INTO Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the Sakila sample database, the film table stores all the films available for rent in a DVD store.

Use SELECT INTO copy the film table

To copy all the rows of the film table to a new table film_copy, use the following statement:

SELECT *
INTO TABLE film_copy
FROM film;

SELECT 1000

The following statement querying the number of rows from the new table to verify that the table was created successfully:

SELECT count(*) FROM film_copy;

count
-------
 1000
(1 row)

Copy partial rows and columns using SELECT INTO

To copy titles of all films rating G from the film table to a new table film_ranting_g_title, use the following statement:

SELECT title
INTO TABLE film_ranting_g_title
FROM film
WHERE rating = 'G';

SELECT 178

The following statement querying all films from the new table to verify that the table was created successfully:

SELECT * FROM film_ranting_g_title;

          title
---------------------------
ACE GOLDFINGER
AFFAIR PREJUDICE
AFRICAN EGG
ALAMO VIDEOTAPE
AMISTAD MIDSUMMER
ANGELS LIFE
ANNIE IDENTITY
ARMAGEDDON LOST
ATLANTIS CAUSE
AUTUMN CROW
BAKED CLEOPATRA
...
(178 row)

Conclusion

In PostgreSQL, you can use the SELECT INTO statement to create a new table from a query result set.