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
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).
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;
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;
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.