# How to create and delete databases and tables in PostgreSQL

> Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt.

Learn how to create and delete database and table structures using SQL and command line utilities.

Canonical URL: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables
Authors: Justin Ellingwood
Content revision: 91b5ea3b95a0824bcd0e0d61f68d6121c6e45039fde2b5f874c4c65eec29030e
Source revision: b6716699f6439c3ba6ae3bf7116b7da84963c978

## 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](https://www.prisma.io/dataguide/intro/database-glossary#database):** separate different sets of structures and data from one another
- **[tables](https://www.prisma.io/dataguide/intro/database-glossary#table):** 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](https://www.postgresql.org/docs/current/ddl-schemas.html):** a namespace within a database that contains tables, [indexes](https://www.prisma.io/dataguide/intro/database-glossary#index), [views](https://www.prisma.io/dataguide/intro/database-glossary#view), and other items.

![Relationship between PostgreSQL databases, schemas, and tables](https://www.prisma.io/dataguide/static/db938bca6b8561c7a9a83eb5dd4b08dd/7223d/object-hierarchy.png)

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " ``` "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " @startuml "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " database \"Database\" { "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# "   node \"Schema\" { "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " 	node \"Table\" { "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# "     } "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# "   } "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " } "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " @enduml "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " ``` "

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](https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables#using-administrative-command-line-tools-to-create-and-delete-databases), we'll show you how to do a few of these tasks using the [command line](https://www.prisma.io/dataguide/postgresql/connecting-to-postgresql-databases). 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](https://www.postgresql.org/docs/current/sql-createtable.html).

## 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](https://www.postgresql.org/docs/current/app-psql.html). Your PostgreSQL instance can be [installed locally, remotely, or provisioned by a provider](https://www.prisma.io/dataguide/postgresql/5-ways-to-host-postgresql).

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:

```sql
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](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html). You can view the properties of the default `template1` template using the following `psql` meta-command:

```sql
\l template1
```

```sql
                                  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_](https://www.prisma.io/dataguide/intro/database-glossary#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:

```sql
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:

```sql
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:

```sql
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:

```sql
\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](https://www.prisma.io/dataguide/postgresql/introduction-to-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:

```sql
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](https://www.prisma.io/dataguide/postgresql/introduction-to-data-types) 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](https://www.prisma.io/dataguide/postgresql/column-and-table-constraints#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](https://www.prisma.io/dataguide/postgresql/column-and-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:

```sql
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`:

![Entity relationship diagrams for supplies and teachers tables](https://www.prisma.io/dataguide/static/ede1857ab3d7b75ccc860c9285f020cb/6fe3b/entity-relationship-diagram.png)

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " ``` "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " @startuml "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " entity \"supplies\" as sup { "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * id : int <<not null, unique>> "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " -- "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * name : varchar "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * description : varchar "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * manufacturer : varchar "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * color : varchar "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * inventory : int <<greater than 0>> "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " } "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " entity \"teachers\" as teach { "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * id : int <<not null, unique>> "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " -- "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * first_name : varchar "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * last_name : varchar "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * subject : varchar "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " * grade_level : int "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " } "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " @enduml "

[comment]: https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables# " ``` "

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:

```sql
\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:

```sql
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`](https://www.prisma.io/dataguide/intro/database-glossary#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](https://www.prisma.io/dataguide/intro/database-glossary#index) for primary key columns to increase querying speed.

Verify that the new table is present by typing:

```sql
\dt
```

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

Verify that the schema reflects the intended design by typing:

```sql
\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:

```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](https://www.prisma.io/dataguide/intro/database-glossary#constraint) within PostgreSQL.

A [primary key](https://www.prisma.io/dataguide/intro/database-glossary#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](https://www.prisma.io/dataguide/intro/database-glossary#foreign-key) 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:

```sql
\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:

```sql
\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:

```sql
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:

```sql
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:

```sql
ALTER TABLE some_table DROP COLUMN useless_column;
```

### Changing the data type of a column

To change the [data type](https://www.prisma.io/dataguide/intro/database-glossary#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:

```sql
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`](https://www.postgresql.org/docs/current/sql-altertable.html).

## 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
DROP DATABASE some_database;
```

```sql
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:

```sql
DROP DATABASE IF EXISTS some_database;
```

```sql
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:

```sql
\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:

```sql
\c defaultdb
```

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

```sql
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](https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables#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`](https://www.postgresql.org/docs/current/app-createdb.html) and [`dropdb`](https://www.postgresql.org/docs/current/app-dropdb.html) 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:

```bash
createdb db_name
```

This will create a database called `db_name` within PostgreSQL using the [default settings](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html).

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](https://www.postgresql.org/docs/current/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED) for the database.
- **`--locale=`** : sets the [locale](https://www.postgresql.org/docs/current/locale.html) 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:

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
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](https://www.postgresql.org/docs/current/sql-createtable.html).

## FAQ

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

Yes, PostgreSQL supports the use of [`IF NOT EXISTS`](https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables#create-tables-conditionally-with-the-if-not-exists-clause) when creating both databases and tables. The below demonstrates using the clause for table creation.

```sql
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](https://www.postgresql.org/docs/current/app-pgdump.html)), PostgreSQL provides the [utility program `pg_restore`](https://www.postgresql.org/docs/current/app-pgrestore.html).

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:

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

**Which command line creates a database in PostgreSQL?**

To create a database in PostgreSQL, use the [`createdb` command](https://www.postgresql.org/docs/current/app-createdb.html). The syntax is as follows:

```
createdb db_name
```

**How do you drop a database in PostgreSQL?**

The [`DROP DATABASE`](https://www.prisma.io/dataguide/postgresql/create-and-delete-databases-and-tables#drop-databases) statement tells PostgreSQL to delete the specified database. The basic syntax looks like this:

```sql
DROP DATABASE database_name;
```

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

To change the [data type](https://www.prisma.io/dataguide/intro/database-glossary#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.

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