How to Perform the Postgres Create Table As Statement

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
CREATE TABLE new_table AS statement;

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:

1
sudo su - postgres

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

1
psql

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:

1
postgres@linux-NECq:~$ createdb sample_db

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

1
psql sample_db

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:

1
2
CREATE TABLE TABLE_NAME(
        COLUMN + DATA_TYPE + CONSTRAINTS [OPTIONAL]);

Here’s an example of the CREATE TABLE statement:

1
2
3
4
5
CREATE TABLE movies(
        id SERIAL PRIMARY KEY,
        movie_name TEXT,
        year_released INT
);

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:

1
2
CREATE TABLE new_movie AS SELECT id, movie_name FROM
movies WHERE id = 1;

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:

1
2
3
4
5
sample_db=# SELECT * FROM new_movie;
 id |        movie_name        
----+--------------------------
  1 | The Adventures of Tintin
(1 row)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.