How to Perform the Postgres Create Table As Statement
PostgreSQL CREATE TABLE AS
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:
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
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.
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
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
sudo su - postgres
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:
postgres@linux-NECq:~$ createdb sample_db
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:
CREATE TABLE TABLE_NAME(
COLUMN + DATA_TYPE + CONSTRAINTS [OPTIONAL]);
Here’s an example of the
CREATE TABLE statement:
CREATE TABLE movies(
id SERIAL PRIMARY KEY,
Let’s assume that we’ve already inserted some records into the
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:
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:
sample_db=# SELECT * FROM new_movie;
id | movie_name
1 | The Adventures of Tintin
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.
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