Open In App

PostgreSQL – CREATE TABLE AS

Improve
Improve
Like Article
Like
Save
Share
Report

In PostgreSQL, the CREATE TABLE AS statement is used to create a new table and fill it with the data returned by a query.

Syntax:
CREATE TABLE new_table_name
AS query;

Let’s analyze the above syntax:

  • First, specify the new table name after the CREATE TABLE clause.
  • Finally, provide a query whose result set is added to the new table after the AS keyword.

The TEMPORARY or TEMP keyword allows you to to create a temporary table:

Syntax:
CREATE TEMP TABLE new_table_name 
AS query;

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

Syntax:
CREATE UNLOGGED TABLE new_table_name
AS query;

The columns of the new table will have the names and data types associated with the output columns of the SELECT clause.If you want the table columns to have different names, you can specify the new table columns after the new table name as below:

Syntax:
CREATE TABLE new_table_name ( column_name_list)
AS query;

In case one wants to avoid an error by creating a new table that already exists, you can use the IF NOT EXISTS option as follows:

Syntax:
CREATE TABLE IF NOT EXISTS new_table_name
AS query;

For examples we will be using the sample database (ie, dvdrental).
Example 1:
In this example, we will use the film and film_category table of the sample database to create a table of action films which belongs to category 1 using the below statement:

CREATE TABLE action_film AS
SELECT
    film_id,
    title,
    release_year,
    length,
    rating
FROM
    film
INNER JOIN film_category USING (film_id)
WHERE
    category_id = 1 ;

Now we can verify the new table using the below statement:

SELECT * FROM action_film ORDER BY title;

Output:

Example 2:
In this example we will create a new table film_rating and fill it with the summary data from the film table using the below statement:

CREATE TABLE IF NOT EXISTS film_rating (rating, film_count) 
AS 
SELECT
    rating,
    COUNT (film_id)
FROM
    film
GROUP BY
    rating;

Now to verify the new table, use the below statement:

SELECT * FROM film_rating;

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads