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:

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:

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

You should see output that looks like the following:

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:

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:

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.

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

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.