How to Perform the PostgreSQL Create Table

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

PostgreSQL create table

The PostgreSQL CREATE TABLE statement makes a new table within your current database or some specified database. After you create your table, you can insert data into it.

The basic syntax for the CREATE TABLE statement looks like this:

1
2
3
4
CREATE TABLE TABLE_NAME(
        col_1 DATATYPE CONSTRAINTS [OPTIONAL],
        col_2 DATATYPE CONSTRAINTS [OPTIONAL]
);

Introduction

When you need to add a new table to your PostgreSQL database, you’ll need to make use of the CREATE TABLE statement. With the CREATE TABLE statement, you can name your new table and define the columns that will exist within it. In this article, we’ll take a closer look at the PostgreSQL CREATE TABLE statement and show some examples of its use.

Prerequisites

Before proceeding with the instructions and examples in this tutorial, you’ll need to make sure a few important prerequisites are in place: You’ll need to have PostgreSQL installed and configured on your machine, and you’ll also need to know how to use the psql command line interface. We’ll be using psql to create a table in our examples. You can use the command psql -V to return the version number of PostgreSQL that’s installed on your machine.

Using psql to connect to the database

There are a few different ways you can access psql, the interactive command-line interface for PostgreSQL that is used for performing queries and database operations. The simplest method is to use the command shown below:

1
sudo su - postgres

This command elevates our privileges to those of the postgres superuser. After entering the password, use this command:

1
psql demo

You should see output that looks like the following:

1
2
3
4
5
postgres@linux-NECq:~$ psql demo
psql (12.1 (Ubuntu 12.1-1.pgdg18.04+1))
Type "help" for help.

demo=#

Using this method, you can easily connect to the Postgres database and enter your queries or SQL statements.

NOTE: You can also use the command \c followed by the database name to connect to a particular database.

Create a table in Postgres

Tables play a valuable role in a PostgreSQL database because they allow you to store different types of data in their columns. Columns are defined when you create your table.

Let’s look at an example of how to create a table in PostgreSQL:

1
2
3
4
5
6
CREATE TABLE book(
        id SERIAL PRIMARY KEY,
        author TEXT NOT NULL,
        isbn TEXT,
        title TEXT
);

When you create a table, it’s important to include the data type for a column; otherwise, you’ll need to include an optional constraint so that it won’t throw an error due to missing values. Let’s look at the example below:

1
2
3
demo=# CREATE TABLE book(id SERIAL PRIMARY KEY, author TEXT NOT NULL, isbn, title TEXT);
ERROR:  syntax error at OR near ","
LINE 1: ...(id serial PRIMARY KEY, author text NOT NULL, isbn, title te...

As you can see, we received an error message because we didn’t indicate the data type to use for our column isbn.

Use Postgres to create a table as statement

We can also create a table using the CREATE TABLE AS statement. This allows you to create a new table by duplicating columns from another table. Not only will the new table have the specified column structure ftom the old table, but it will also contain the records from the old table.

1
CREATE TABLE new_book AS SELECT author, title FROM book WHERE id = 1;

We can use a SELECT statement to return the contents of the new table and verify that our CREATE TABLE AS operation was successful:

1
2
3
4
5
demo=# SELECT * FROM new_book;
    author     |     title      
---------------+----------------
 Thomas Harvey | The Programmer
(1 ROW)

You can use the command \d new_book to retrieve detailed information for this table.

Conclusion

If you’re getting started with PostgreSQL, it’s important to learn the proper way to create a table. In this article, we showed you how to use the PostgreSQL CREATE TABLE statement to name your table and define the columns that will exist within the table. We also provided an example of the CREATE TABLE AS statement to show how you can create a new table by copying the column structure of an existing table. With these examples to guide you, you’ll have no problem creating tables 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.