How to Perform the PostgreSQL Create Table
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