Postgres allows us to create a table via the SELECT command; for this purpose, the CREATE TABLE statement is used along with an AS clause followed by a SELECT statement. The newly created table will have the same table structure (e.g., column names, data types, etc.) as the columns in the SELECT query.

This blog post will consider various examples to demonstrate the working of the CREATE TABLE AS statement in Postgres. For this purpose, the following content will be covered in this write-up:

• How Do I Create a Table Via the CREATE TABLE AS SELECT Statement in Postgres?
• How to Create a TEMPORARY Table Via the CREATE TABLE AS SELECT Command in Postgres?
• How to Create an UNLOGGED Table Via the CREATE TABLE AS SELECT Command in Postgres?
• How to Avoid Table Already Existing Error in Postgres?

So, let’s begin!

How Do I Create a Table Via CREATE TABLE AS SELECT Statement in Postgres?

In Postgres, the CREATE TABLE AS statement allows us to create a table from an existing one. It creates the table based on the result-set retrieved by the SELECT query. Follow the below syntax to avail the functionality of the Postgres’ CREATE TABLE AS statement:

CREATE TABLE new_tab AS 
SELECT col_list | expression
FROM existing_tab_name
[WHERE criteria];

In the above snippet, new_tab represents the table name to be created. Col_list represents the existing table's columns based on which a new table will be defined/created. WHERE is an optional clause used to specify a specific condition/criteria.

Note: If the user wants a table with a different name, they can specify different table columns after the new table name.

Example 1: How Do I Create a Table via CREATE TABLE AS Statement?

We have already created a table named “author_details” that has the following structure:

SELECT * FROM author_details;

Let's say we want to create a table named "author_info" with the same columns and data as "author_details". For this purpose, the CREATE TABLE AS statement will be executed as follows:

CREATE TABLE author_info AS
SELECT * 
FROM author_details;

Let’s execute the SELECT * command to see the newly created table:

SELECT * FROM author_info;

The output shows that the “author_info” table has been created with the same data as in the “author_details” table.

Example 2: How Do I Create a Table Without Data Using the CREATE TABLE AS Statement?

Executing the CREATE TABLE AS Statement with the collaboration of the “WITH NO DATA” clause will copy only the table’s structure(without any data).

CREATE TABLE author_information AS
SELECT * 
FROM author_details
WITH NO DATA;

The output snippet indicates that the CREATE TABLE AS SELECT Statement gets executed successfully. Here is the verification snippet:

SELECT * FROM author_information;

The output snippet authenticates the working of the “CREATE TABLE AS SELECT” statement.

How to Create a TEMPORARY Table Via the CREATE TABLE AS SELECT Command in Postgres?

Use the TEMP keyword along with the CREATE TABLE AS command to create a temporary table in Postgres:

CREATE TEMP TABLE tab_name AS
SELECT col_list | expression
FROM existing_tab_name
[WHERE criteria];

Note: A temporary table in Postgres has a short lifespan. These tables are available only in the current database session and disappear once a session has expired.

Let’s learn how to create a temporary table via the below example:

Example: How Do I Create a Temporary Table in Postgres?

Let’s say we need to create a temporary table with the same structure as the “author_details” table. To do this, the “CREATE TABLE AS SELECT” statement will be executed as follows:

CREATE TEMP TABLE temp_author AS
SELECT *
FROM author_details;

To verify the table’s creation, execute the SELECT * command as follows:

SELECT * FROM temp_author;

The output snippet proves that a temporary table has been created with the same data as the “author_details” table.

How to Create an UNLOGGED Table Via the CREATE TABLE AS SELECT Command in Postgres?

Postgres' UNLOGGED tables are special types of tables intended to store temporary or intermediate results that can be regenerated if necessary. They are faster as compared to regular tables because they do not need to be logged and do not require the overhead of maintaining a transaction log.

To create an UNLOGGED table using the CREATE TABLE AS statement in Postgres, you can use the following syntax:

CREATE UNLOGGED TABLE new_tab_name AS 
SELECT * FROM existing_tab_name;

Executing the above query will create a new table named “new_tab_name,” which is an exact copy of an existing table named “existing_tab_name”, with the additional property that it is an UNLOGGED table.

Example: How Do I Create an Unlogged Table in Postgres?

Let’s execute the CREATE TABLE AS SELECT statement with an UNLOGGED keyword:

CREATE UNLOGGED TABLE unlogged_example AS
SELECT *
FROM author_details
WITH NO DATA;

This way, an unlogged table can be created in Postgres via the CREATE TABLE AS SELECT statement.

How to Avoid Table Already Exist Error in Postgres?

Users may encounter the “table already exists” error while working with the “CREATE TABLE AS SELECT” statement. To avoid such an error, the “IF NOT EXISTS” option must be used alongside the “CREATE TABLE AS SELECT” statement.

CREATE TABLE IF NOT EXIST new_tab AS 
SELECT col_list | expression
FROM existing_tab_name;

Example: How Do I Avoid Table Already Exists Error in Postgres?

We have already created a table named “author_information”; trying to create a table with the same name will throw an error. However, if we specify the “IF NOT EXISTS” option, then we can avoid such errors:

CREATE TABLE IF NOT EXISTS author_information 
AS SELECT * FROM author_details;

Postgres generates a notice instead of throwing an error. It proves the working of the “IF NOT EXISTS” option.

Conclusion

In PostgreSQL, a new table can be created via the SELECT command; for this purpose, the CREATE TABLE statement is used along with an AS clause followed by a SELECT statement. The newly created table will have the same table structure (e.g., column names, data types, etc.) as the columns in the SELECT query. This Postgres blog has provided an in-depth understanding of the CREATE TABLE AS SELECT statement via examples.