PostgreSQL

How to create and delete databases and tables in PostgreSQL

CONTENT

Introduction
Prerequisites
Create a new database
List existing databases
Create tables within databases
How to view tables in PostgreSQL
Alter tables
Drop tables
Drop databases
Using administrative command line tools to create and delete databases
Conclusion
FAQ

Share on

Introduction

PostgreSQL and other relational database management systems use databases and tables to structure and organize their data. We can review the definition of those two terms quickly:

databases: separate different sets of structures and data from one another
tables: define the data structure and store the actual data values within databases

In PostgreSQL, there is also an intermediary object between databases and tables called schema:

schema: a namespace within a database that contains tables, indexes, views, and other items.

This guide won't deal directly with PostgreSQL's concept of a schema, but it's good to know it's there.

Instead, we'll be focusing on how to create and destroy PostgreSQL databases and tables. The examples will primarily use SQL, but towards the end, we'll show you how to do a few of these tasks using the command line. These alternatives use tools included in the standard PostgreSQL installation that are available if you have administrative access to the PostgreSQL host.

Some of the statements covered in this guide, particularly the PostgreSQL CREATE TABLE statement, have many additional options that were outside of the scope of this article. If you'd like additional information, find out more by checking out the official PostgreSQL documentation.

Prerequisites

To follow along with this guide, you will need to log in to a PostgreSQL instance with a user with administrative privileges using the psql command line client. Your PostgreSQL instance can be installed locally, remotely, or provisioned by a provider.

Specifically, your PostgreSQL user will need the CREATE DB privilege or be a Superuser, which you can check with the \du meta-command in psql:

\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

The postgres superuser, which is created automatically upon installation, has the required privileges, but you can use any user with the Create DB privilege.

Create a new database

Once you are connected to your PostgreSQL instance using psql or any other SQL client, you can create a database using SQL.

The basic syntax for creating a database is:

CREATE DATABASE db_name;

This will create a database called db_name on the current server with the current user set as the new database's owner using the default database settings. You can view the properties of the default template1 template using the following psql meta-command:

\l template1

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(1 row)

You can add additional parameters to alter the way your database is created. These are some common options:

ENCODING: sets the character encoding for the database.
LC_COLLATE: sets the collation, or sort, order for the database. This is a localization option that determines how items are organized when they are ordered.
LC_CTYPE: sets the character classification for the new database. This is a localization option that affects what characters are considered uppercase, lowercase, and digits.

These can help ensure that the database can store data in the formats you plan to support and with your project's localization preferences.

For example, to ensure that your database is created with Unicode support and to override the server's own locale to use American English localization (these all happen to match the values in the template1 shown above, so no change will actually occur), you could type:

CREATE DATABASE db_name
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

To follow along with the examples in this guide, create a database called school using your instance's default locale settings and the UTF8 character encoding:

CREATE DATABASE school ENCODING 'UTF8';

This will create your new database using the specifications you provided.

List existing databases

To determine what databases are currently available on your server or cluster, you can use the following SQL statement:

SELECT datname FROM pg_database;

This will list each of the databases currently defined within the environment:

  datname
-----------
 _dodb
 template1
 template0
 defaultdb
 school
(5 rows)

As mentioned before, if you are connected using the psql client, you can also get this information \l meta-command:

\l

This will show the available database names along with their owners, encoding, locale settings, and privileges:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

The school database that we created is displayed among the other databases on the system. This is a good way to get an overview of the databases within your server or cluster.

Create tables within databases

After creating one or more databases, you can begin to define tables to store your data. Tables consist of a name and a defined schema which determines the fields and data types that each record must contain.

PostgreSQL `CREATE TABLE` syntax

You can create tables using the CREATE TABLE statement. A simplified basic syntax for the command looks like the following:

CREATE TABLE table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

The components of the above syntax include the following:

CREATE TABLE table_name: The basic creation statement that signals that you wish to define a table. The table_name placeholder should be replaced with the name of the table you wish to use.
column_name TYPE: Defines a basic column within the table. The column_name placeholder should be replaced with the name you wish to use for your column. The TYPE specifies the PostgreSQL data type for the column. Data stored within the table must conform to the column structure and column data types to be accepted.
column_constraint: Column constraints are optional restraints to add further restrictions on the data that can be stored in the column. For example, you can require that entries be not null, unique, or positive integers.
table_constraints: Table constraints are similar to column constraints but involve the interaction of multiple columns. For instance, you could have a table constraint that checks that a DATE_OF_BIRTH is before DATE_OF_DEATH in a table.

Create tables conditionally with the `IF NOT EXISTS` clause

By default, if you attempt to create a table in PostgreSQL that already exists within the database, an error will occur. To work around this problem in cases where you want to create a table if it isn't present, but just continue on if it already exists, you can use the IF NOT EXISTS clause. The IF NOT EXISTS optional qualifier that tells PostgreSQL to ignore the statement if the database already exists.

To use the IF NOT EXISTS clause, insert it into the command after the CREATE TABLE syntax and before the table name:

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

This variant will attempt to create the table. If a table with that name already exists within the specified database, PostgreSQL will throw a warning indicating that the table name was already taken instead of failing with an error.

How to create tables in PostgreSQL

The above syntax is enough to create basic tables. As an example, we'll create two tables within our school database. One table will be called supplies and the other will be called teachers:

In the supplies table, we want to have the following fields:

ID: A unique ID for each type of school supply.
Name: The name of a specific school item.
Description: A short description of the item.
Manufacturer: The name of the item manufacturer.
Color: The color of the item.
Inventory: The number of items we have for a certain type of school supply. This should never be less than 0.

We can create the supplies table with the above qualities using the following SQL.

First, change to the school database you created with psql by typing:

\c school

This will change the database that our future commands will target. Your prompt should change to reflect the database.

Next, create the supplies table with the following statement:

CREATE TABLE supplies (
  id INT PRIMARY KEY,
  name VARCHAR,
  description VARCHAR,
  manufacturer VARCHAR,
  color VARCHAR,
  inventory int CHECK (inventory > 0)
);

This will create the supplies table within the school database. The PRIMARY KEY column constraint is a special constraint used to indicate columns that can uniquely identify records within the table. As such, the constraint specifies that the column cannot be null and must be unique. PostgreSQL creates indexes for primary key columns to increase querying speed.

Verify that the new table is present by typing:

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
(1 row)

Verify that the schema reflects the intended design by typing:

\d supplies

                      Table "public.supplies"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 id           | integer           |           | not null |
 name         | character varying |           |          |
 description  | character varying |           |          |
 manufacturer | character varying |           |          |
 color        | character varying |           |          |
 inventory    | integer           |           |          |
Indexes:
    "supplies_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "supplies_inventory_check" CHECK (inventory > 0)

We can see each of the columns and data types that we specified. The column constraint that we defined for the inventory column is listed towards the end.

Next, we will create a teachers table. In this table, the following columns should be present:

Employee ID: A unique employee identification number.
First name: The teacher's first name.
Last name: The teacher's last name.
Subject: The subject that the teacher is hired to teach.
Grade level: The grade level of students that the teach is hired to teach.

Create the teachers table with the above schema with the following SQL:

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR,
  grade_level int
);

How to create tables with primary keys and foreign keys

You can find information about creating tables with primary and foreign keys in some of our other PostgreSQL guides. Primary keys and foreign keys are both types of database constraint within PostgreSQL.

A primary key is a special column or column that is guaranteed to be unique across rows within the same table. All primary keys can be used to uniquely identify a specific row. Primary keys not only ensure that each row has a unique value for the primary key columns, they also ensure that no rows contain NULL values for that column. Often, the primary key in PostgreSQL uses the following format to specify an automatically assigned incrementing primary key: id SERIAL PRIMARY KEY.

Foreign keys are a way to ensure that a column or columns in one table match the values contained within another table. This helps ensure referential integrity between tables.

How to view tables in PostgreSQL

In PostgreSQL you can list tables in a few different ways depending on what information you are looking for.

If you'd like to see what tables are available within your database, you can use the \dt meta-command included with the psql client to list all tables, as we demonstrated above:

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
 public | teachers | table | doadmin
(2 rows)

You can also check that the schema for the table matches your specifications:

\d teachers

                     Table "public.teachers"
   Column    |       Type        | Collation | Nullable | Default
-------------+-------------------+-----------+----------+---------
 id          | integer           |           | not null |
 first_name  | character varying |           |          |
 last_name   | character varying |           |          |
 subject     | character varying |           |          |
 grade_level | integer           |           |          |
Indexes:
    "teachers_pkey" PRIMARY KEY, btree (id)

The teachers table seems to match our definition.

Alter tables

If you need to change the schema of an existing table in PostgreSQL, you can use the ALTER TABLE command. The ALTER TABLE command is very similar to the CREATE TABLE command, but operates on an existing table.

Alter table syntax

The basic syntax for modifying tables in PostgreSQL looks like this:

ALTER TABLE <table_name> <change_command> <change_parameters>

The <change_command> indicates the exact type of change you would like to make, whether it involves setting different options on the table, adding or removing columns, or changing types or constraints. The <change_parameters> part of the command contains any additional information that PostgreSQL needs to complete the change.

Adding columns to tables

You can add a column to a PostgreSQL table with the ADD COLUMN change command. The change parameters will include the column name, type, and options, just as you would specify them in the CREATE TABLE command.

For example, to add a column called missing_column of the text type to a table called some_table, you would type:

ALTER TABLE some_table ADD COLUMN missing_column text;

Removing columns from tables

If, instead, you'd like to remove an existing column, you can use the DROP COLUMN command instead. You need to specify the name of the column you wish to drop as a change parameter:

ALTER TABLE some_table DROP COLUMN useless_column;

Changing the data type of a column

To change the data type that PostgreSQL uses for a specific column, you can use ALTER COLUMN change command with the SET DATA TYPE column command. The parameters include the column name, its new type, and an optional USING clause to specify how the old type should be converted to the new type.

For example, to set the value of a id column in the resident table to a int using an explicit cast, we can type the following:

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;

Other table changes

Many other types of changes can be achieved with the ALTER TABLE command. For more information about the options available, check out the official PostgreSQL documentation for ALTER TABLE.

Drop tables

If you wish to delete a table, you can use the DROP TABLE SQL statement. This will delete the table as well as any data stored within it.

The basic syntax looks like this:

DROP TABLE table_name;

This will delete the table if it exists and throw an error if the table name does not exist.

If you wish to delete the table if it exists and do nothing if it does not exist, you can include the IF EXISTS qualifier within the statement:

DROP TABLE IF EXISTS table_name;

Tables that have dependencies on other tables or objects cannot be deleted by default while those dependencies exist. To avoid the error, you can optionally include the CASCADE parameter, which automatically drops any dependencies along with the table:

DROP TABLE table_name CASCADE;

If any tables have a foreign key constraint, which references the table that you are deleting, that constraint will automatically be deleted.

Delete the supplies table we created earlier by typing:

DROP TABLE supplies;

We will keep the teachers database to demonstrate that the statement to delete databases also removes all child objects like tables.

Drop databases

The DROP DATABASE statement tells PostgreSQL to delete the specified database. The basic syntax looks like this:

DROP DATABASE database_name;

Replace the database_name placeholder with the name of the database you wish to remove. This will delete the database if it is found. If the database cannot be found, an error will occur:

DROP DATABASE some_database;

ERROR:  database "some_database" does not exist

If you wish to delete the database if it exists and otherwise do nothing, include the optional IF EXISTS option:

DROP DATABASE IF EXISTS some_database;

NOTICE:  database "some_database" does not exist, skipping
DROP DATABASE

This will remove the database or do nothing if it cannot be found.

To remove the school database that we used in this guide, list the existing databases on your system:

\l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

Open a new connection to one of the databases you do not wish to delete:

\c defaultdb

Once the new connection is open, delete the school database with the following command:

DROP DATABASE school;

This will remove the school database along with the teachers table defined within.

If you have been following along using SQL, you can end here or skip to the conclusion. If you'd like to learn about how to create and delete databases from the command line, continue on to the next section.

Using administrative command line tools to create and delete databases

If you have shell access to the server or cluster where PostgreSQL is installed, you may have access to some additional command line tools that can help create and delete databases. The createdb and dropdb commands are bundled with PostgreSQL when it is installed.

Create a new database from the command line

The basic syntax for the createdb command (which should be run by a system user with admin access to PostgreSQL) is:

createdb db_name

This will create a database called db_name within PostgreSQL using the default settings.

The command also accepts options to alter its behavior, much like the SQL variant you saw earlier. You can find out more about these options with man createdb. Some of the most important options are:

--encoding= : sets the character encoding for the database.
--locale= : sets the locale for the database.

These can help ensure that the database can store data in the formats you plan to support and with your project's localization preferences.

For example, to ensure that your database is created with Unicode support and to override the server's own locale to use American English localization, you could type:

createdb --encoding=UTF8 --locale=en_US db_name

Assuming you have the correct permissions, the database will be created according to your specifications.

To follow along with the examples in this guide, you could create a database called school using the default locale and the UTF8 character encoding by typing:

createdb --encoding=UTF8 school

You could then connect to the database using psql to set up your tables as usual.

Drop databases from the command line

The dropdb command mirrors the DROP DATABASE SQL statement. It has the following basic syntax:

dropdb database_name

Change the database_name placeholder to reference the database you wish to delete.

By default, this command will result in an error if the database specified cannot be found. To avoid this, you can include the optional --if-exists flag:

dropdb --if-exists database_name

This will delete the specified database if it exists. Otherwise, it will do nothing.

To delete the school database we created earlier, type:

dropdb school

This will remove the database and any child elements, like tables, within.

Conclusion

This article covered the basics of how to create and delete databases and tables within PostgreSQL. These are some of the most basic commands required to set up a database system and being defining the structure of your data.

As mentioned earlier, the SQL statements covered in this PostgreSQL tutorial, particularly the CREATE TABLE statement, have many additional parameters can be used to change PostgreSQL's behavior. You can find out more about these by checking out the official PostgreSQL documentation.

RELATED ON PRISMA.IO

When using Prisma to develop with PostgreSQL, you will usually create databases and tables with Prisma Migrate. You can learn how use it in our guide on developing with Prisma Migrate.

Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.

FAQ

Does PostgreSQL support the `IF NOT EXISTS` clause when using the `CREATE DATABASE` command?

Yes, PostgreSQL supports the use of IF NOT EXISTS when creating both databases and tables. The below demonstrates using the clause for table creation.

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

How do you create a database from a dump in PostgreSQL?

To create a database from a dump (pg_dump), PostgreSQL provides the utility program pg_restore.

This program recreates the database in the same state as it was at the time of the dump. Example syntax would look like the following:

pg_restore [connection-option...][option...][filename]

Which command line creates a database in PostgreSQL?

To create a database in PostgreSQL, use the createdb command. The syntax is as follows:

createdb db_name

How do you drop a database in PostgreSQL?

The DROP DATABASE statement tells PostgreSQL to delete the specified database. The basic syntax looks like this:

DROP DATABASE database_name;

How do you change a column's data type in PosgreSQL?

To change the data type for a specific column, use the ALTER COLUMN change command with the SET DATA TYPE column command.

The basic syntax includes column name, the new type, and an optional USING clause to specify the old type's conversion.

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;

About the Author(s)

Justin Ellingwood

Justin has been writing about databases, Linux, infrastructure, and developer tools since 2013. He currently lives in Berlin with his wife and two rabbits. He doesn't usually have to write in the third person, which is a relief for all parties involved.

Connecting to PostgreSQL databases

An introduction to PostgreSQL data types

Edit this page on GitHub