Introduction to the PostgreSQL CREATE TABLE statement

TheCREATE TABLE AS statementcreates a new table and fills it with the data returned by a query. The following shows the syntax of theCREATE TABLE AS statement:

CREATE TABLE new_table_nameAS query;

In this syntax:

1. First, specify the new table name after theCREATE TABLE clause.
2. Second, provide a query whose result set is added to the new table after theAS keyword.

TheTEMPORARY orTEMP keyword allows you to create atemporary table:

CREATE TEMPTABLE new_table_nameAS query;

TheUNLOGGED keyword allows the new table to be created as an unlogged table:

CREATE UNLOGGEDTABLE new_table_nameAS query;

The columns of the new table will have the names and data types associated with the output columns of theSELECT clause.

If you want the table columns to have different names, you can specify the new table columns after the new table name:

CREATE TABLE new_table_name ( column_name_list)AS query;

If you want to avoid an error by creating a new table that already exists, you can use theIF NOT EXISTS option as follows:

CREATE TABLE IF NOT EXISTS new_table_nameAS query;

PostgreSQL CREATE TABLE AS statement examples

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

The following example uses the CREATE TABLE AS statement to create a new table that contains the films whose category is 1:

CREATE TABLE action_filmASSELECT    film_id,    title,    release_year,    length,    ratingFROM    filmINNER JOIN film_categoryUSING (film_id)WHERE    category_id= 1;

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

SELECT * FROM action_filmORDER BY title;

To check the structure of theaction_film, you can use the following command in the psql tool:

\d action_film;

It returns the following output:

As clearly shown in the output, the names and data types of theaction_film table are derived from the columns of theSELECT clause.

If theSELECT clause contains expressions, it is a good practice to override the columns, for example:

CREATE TABLE IF NOT EXISTS film_rating (rating, film_count)ASSELECT    rating,    COUNT (film_id)FROM    filmGROUP BY    rating;

This example statement created a new tablefilm_rating and filled it with the summary data from thefilm table. It explicitly specified the column names for the new table instead of using the column names from theSELECT clause.

To check the structure of thefilm_rating table, you use the following command in psql tool:

\d film_rating

The following is the output:

Note that theCREATE TABLE AS statement is similar to theSELECT INTO statement, but theCREATE TABLE AS statement is preferred because it is not confused with other uses of theSELECT INTO syntax inPL/pgSQL. In addition, theCREATE TABLE AS statement provides a superset of the functionality offered by theSELECT INTO statement.

Summary

• Use the PostgreSQLCREATE TABLE AS statement to create a new table from the result of a query.

Last updated onFebruary 13, 2024

On this page

• Introduction to the PostgreSQL CREATE TABLE statement
• PostgreSQL CREATE TABLE AS statement examples
• Summary