If you want to select data into variables, check out thePL/pgSQL SELECT INTO statement.

Introduction to PostgreSQL SELECT INTO statement

The PostgreSQLSELECT INTO statementcreates a new table andinserts data returned from a query into the table.

The new table will have columns with the same names as the columns of the result set of the query. Unlike a regularSELECT statement, theSELECT INTO statement does not return a result to the client.

Here’s the basic syntax of the PostgreSQLSELECT INTO statement:

SELECT  select_listINTO [ TEMPORARY | TEMP ] [ TABLE ] new_table_nameFROM  table_nameWHERE  search_condition;

To create a new table with the structure and data derived from a result set, you specify the new table name after theINTO keyword.

TheTEMP orTEMPORARY keyword is optional; it allows you to create atemporary table instead.

TheTABLE keyword is optional, which enhances the clarity of the statement.

TheWHERE clause allows you to specify a condition that determines which rows from the original tables should be filled into the new table.

Besides theWHERE clause, you can use other clauses in theSELECT statement for theSELECT INTO statement such asINNER JOIN,LEFT JOIN,GROUP BY, andHAVING.

Note that you cannot use theSELECT INTO statement in PL/pgSQL because it interprets theINTO clause differently. In this case, you can use theCREATE TABLE AS statement which provides more functionality than theSELECT INTO statement.

PostgreSQL SELECT INTO examples

We will use thefilm table from thesample database for the demonstration.

1) Basic PostgreSQL SELECT INTO statement example

The following statement uses theSELECT INTO statement to create a new table calledfilm_r that contains films with the ratingR and rental duration 5 days from thefilm table.

SELECT    film_id,    title,    rental_rateINTO TABLE film_rFROM    filmWHERE    rating= 'R'AND rental_duration= 5ORDER BY    title;

To verify the table creation, you can query data from thefilm_r table:

SELECT * FROM film_r;

2) Using the SELECT INTO statement to create a new temporary table

The following example uses theSELECT INTO statement to create a temporary table namedshort_film that contains films whose lengths are under 60 minutes.

SELECT    film_id,    title,    lengthINTO TEMPTABLE short_filmFROM    filmWHERE    length < 60ORDER BY    title;

The following shows the data from theshort_film table:

SELECT * FROM short_filmORDER BY length DESC;

Output:

film_id |        title         | length---------+----------------------+--------     486 | Jet Neighbors        |     59     465 | Interview Liaisons   |     59     409 | Heartbreakers Bright |     59     947 | Vision Torque        |     59...

Summary

• Use the PostgreSQLSELECT INTO statement to create a new table from the result set of a query.

Last updated onJanuary 24, 2024

On this page

• Introduction to PostgreSQL SELECT INTO statement
• PostgreSQL SELECT INTO examples
• Summary