PostgreSQL – CREATE TABLE AS
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
Share your thoughts in the comments
Please Login to comment...