PostgreSQL CREATE TABLE AS

The PostgreSQL CREATE TABLE AS statement will create a table in the current database and insert data into the table based on the results returned by the query.

The syntax for using the CREATE TABLE AS statement is shown below:

Put simply, this statement allows you to use another table to populate your new table using the conditions specified in the SELECT.

Introduction

Creating tables is an important skill to have when you’re working with data in PostgreSQL. Although you can use the CREATE TABLE statement to define columns for your new table, you may not necessarily need to start from scratch every time you create a table. Instead, you may want to use the column structure found in an existing table to define and populate your new table. In this article, we’ll learn how to accomplish this using the Postgres CREATE TABLE AS statement.

Prerequisites

Before you attempt to follow along with this tutorial, be sure to install a PostgreSQL database cluster on your machine. If you need to determine whether you already have Postgres installed, you can use the command pg_config --version.

It will return something like this:

PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1)

You should also have some knowledge of PostgreSQL commands and basic database management in order to get the most out of this tutorial.

Access and connect to the psql

We’ll need to access psql, the interactive terminal for PostgreSQL, in order to execute commands such as CREATE TABLE AS.

To connect to psql, we’ll first need to switch to the postgres superuser:

After entering the root password, we can use the command:

This automatically gives the user postgres privileges along with access to the database.

Use psql to create a database

Once we’ve accessed psql as the postgres superuser, we can create a database to use in our examples:

To connect to this database in psql, we can use this command:

Create a table for Postgres database sample_db

Next, let’s try creating a table in our sample_db database. In PostgreSQL, the basic syntax for creating a table is:

Here’s an example of the CREATE TABLE statement:

Let’s assume that we’ve already inserted some records into the movies table.

Use the PostgreSQL create table as statement

We can now create a table which will contain the records from the movies table that we created earlier. The command shown below uses the CREATE TABLE AS statement, where we SELECT the result from the other table in order to populate our new table:

Let’s see if our CREATE TABLE AS operation was successful. We can view the contents of the new table by using the SELECT statement on that table:

If you review the result of this query, you’ll see that the columns in our new table have the same data types defined in the first table that we created.

Conclusion

When you need to create a new table in PostgreSQL, there are a couple of different ways you can accomplish the task. You can start from scratch, defining each column that should exist in the table and then populating your table with data after it’s created. However, it’s also possible to create a new table by copying the column structure and contents of an existing table. In this article, we showed you how to use the Postgres CREATE TABLE AS statement to populate a new table with the contents of another table. With our step-by-step instructions, you’ll be able to create tables using the same method in your own PostgreSQL database.