How to Perform the Postgres Create Table As Statement
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