PostgreSQL: CREATE TABLE AS Statement

In this PostgreSQL post explains how to use the PostgreSQL CREATE TABLE AS statement with syntax and examples.

Description

The PostgreSQL CREATE TABLE AS statement is used to create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax

The syntax for the CREATE TABLE AS statement in PostgreSQL is:

CREATE TABLE new_table AS 
  SELECT expressions
  FROM existing_tables
  [WHERE conditions];

Parameters or Arguments

new_table

The name of the table that you wish to create.

expressions

The columns from the existing_tables that you would like created in the new_table. The column definitions from those columns listed will be transferred to the new_table that you create.

existing_tables

The existing tables from which to copy the column definitions and the associated records (as per the WHERE clause).

WHERE conditions

Optional. The conditions that must be met for the records to be copied to the new_table.

Note

• The column definitions from the existing_tables will be copied to the new_table.
• The new_table will be populated with records based on the conditions in the WHERE clause.

Example

Let's look at a PostgreSQL CREATE TABLE AS example that shows how to create a table by copying all columns from another table.

CREATE TABLE current_inventory AS 
  SELECT *
  FROM products
  WHERE quantity > 0;

This example would create a new table called current_inventory that included all columns from the products table.

If there were records in the products table, then the new current_inventory table would be populated with the records returned by the SELECT statement. In other words, all records from the products table with a quantity greater than 0 would be inserted into the current_inventory table when it was created.

Next, let's look at a CREATE TABLE AS example that shows how to create a table by copying selected columns from multiple tables.

For example:

CREATE TABLE current_inventory AS
  SELECT products.product_id, products.product_name, categories.category_name
  FROM products
  INNER JOIN categories
  ON products.category_id = categories.category_id
  WHERE products.quantity > 0;

This example would create a new table called current_inventory based on column definitions from both the products and categories tables.

Again, only the records that met the conditions of the SELECT statement would be populated into the new current_inventory table.