PostgreSQL CREATE TABLE AS statement

CREATE TABLE AS statement

PostgreSQL CREATE TABLE AS statement is used to create a table from an existing table by copying columns of a current table. It is important to note that building a table will fill the new table with records from the existing table (based on the SELECT operator).

​The syntax for CREATE TABLE AS in PostgreSQL

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

Parameters and arguments of the statement

• table_name – The name of the table you want to create.
• expressions – Columns from existing_tables that you want to create in new_table. Will move the definitions of the columns from these columns to the new_table you made.
• existing_tables – Existing tables from which you can copy the column definitions and related entries (as suggested by WHERE).
• WHERE conditions – Optional. Requirements that must meet to copy records to the new_table.

Note:

• will copy column definitions from existing_tables to the new_table.
• new_table will be filled with entries based on conditions in the WHERE proposal.

Take the example of PostgreSQLCREATE TABLE, which shows how to create a table by copying all columns from another table.

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

In this example, we will create a new table named current_inventory, including all columns from the products table. If the products table has records, fill the new current_inventory table with descriptions returned by the SELECT operator. Meanwhile, all entries from the product table with a number greater than 0 will be inserted into the current_inventory table when it is created.

Next, consider CREATE TABLE AS, which 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 will create a new table named current_inventory based on the column definitions from the products and categories tables. Also, the new current_inventory table will only add entries that satisfy the SELECT operator conditions.

PostgreSQL: Creating Tables with Constraints | Course

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.