PostgreSQL - creating and dropping databases

This article will explain how to create and drop databases using the psql client, and alternatively using the shell commands createdb and dropdb.


We're ready to create our first PostgreSQL databases and learn how to work with them.

As indicated in the previous article, I'll assume you have two normal database roles — named "demorole1" and "demorole2" — and an additional superuser role named "mysuperuser". If those roles don't exist in your postgres server, go ahead and create them now.

Login as Linux user "postgres"

To create a database, we'll need to connect as the postgres superuser role. This will first require a login as the Linux user named "postgres". This was explained in the article PostgreSQL - local connections as postgres.

Login to your slice as a normal Linux user, then:

sudo su - postgres

Connect with psql

Now connect to the database server using the psql client, as the postgres role:

postgres@demo:~$ psql -U postgres
...
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=#

Listing all databases

From within a psql session, you can list all databases using the '\l' command. That's a lowercase "L".

Let's try it now:

postgres=# \l
        List of databases
   Name    |  Owner   | Encoding  
-----------+----------+-----------
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(3 rows)

The three databases listed were created during the PostgreSQL installation process — we won't be working with them directly, though you can learn their purpose in Section 20.3 of the official documentation.

Creating a database

We'll create our first database with this SQL statement:

postgres=#CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

Note the required trailing semicolon ( ; ) at the end of the SQL statement. Single-quotes ( ' ' ) must enclose the specified character encoding.

We've specified 'demorole1' as the owner of this database. If we don't specify an owner, ownership defaults to the creating role.

Specifying the database's character encoding as UTF-8 (a.k.a. "Unicode") is not strictly necessary, but is a reasonable thing to do if our data will be coming from and viewed in a web application. If unspecified, a database's character set will default to 'SQL_ASCII'. Learn more about character encoding in Section 22.2 of the official documentation.

Did it work? We can check using the '\l' command:

postgres=# \l
         List of databases
   Name    |   Owner   | Encoding  
-----------+-----------+-----------
 demodb1   | demorole1 | UTF8
 postgres  | postgres  | SQL_ASCII
 template0 | postgres  | SQL_ASCII
 template1 | postgres  | SQL_ASCII
(4 rows)

Good deal!

Dropping a database

What if we want to drop (delete, destroy) a database? Easy:

postgres=#DROP DATABASE demodb1;

Note that dropping a database cannot be undone; once dropped, a database and its contents are permanently deleted.

If we check with the '\l' command we'll see that 'demodb1' is no longer listed.

An alternative: createdb and dropdb

Alternatively, we can create and drop databases using the createdb and dropdb shell commands, which are basically "wrappers" for the CREATE and DROP SQL statements. They are included in a standard postgres installation.

With our present setup, we can only run these commands (successfully) as the postgres Linux user. We're still connected with the psql client, so let's exit with Ctrl-D or the '\q' command:

postgres=# \q
...
postgres@demo:~$

We're back to our shell prompt, as the postgres Linux user.

createdb

With createdb and the '-O' flag we'll create a database owned by the 'demorole2' role:

postgres@demo:~$ createdb -O demorole2 -E UTF8 demodb2

With the '-E' flag we've specified the database's character encoding as UTF-8.

Now, If we reconnect with psql and run the '\l' command, we'll get this:

postgres=# \l
         List of databases
   Name    |   Owner   | Encoding  
-----------+-----------+-----------
 demodb2   | demorole2 | UTF8
 postgres  | postgres  | SQL_ASCII
 template0 | postgres  | SQL_ASCII
 template1 | postgres  | SQL_ASCII
(4 rows)

dropdb

We can drop (delete, destroy) a database with the dropdb shell command:

postgres@demo:~$ dropdb -i demodb2
Database "demodb2" will be permanently removed.
Are you sure? (y/n) y
...
postgres@demo:~$

The '-i' flag provides a confirmation prompt, which is a good safety measure when running an irreversibly destructive command.

Summary

We've looked at creating and dropping databases using the psql client, and alternatively using the createdb and dropdb shell commands.

Going forward, I'll assume you have two databases: 'demodb1' owned by 'demorole1', and 'demodb2' owned by 'demorole2'. To follow the remaining articles closely, go ahead and re-create those databases using either method.

In the next article, we'll configure our postgres server to allow less restricted local and remote connections.

Mike

Article Comments:

seanl commented Wed Sep 02 12:41:55 UTC 2009:

perhaps a link to the connections articles would be appropriate

Mike commented Wed Sep 02 20:22:34 UTC 2009:

Thanks for the suggestion. The "next article" in this series is now linked at the bottom.

Wil Chung commented Tue Dec 07 21:11:09 UTC 2010:

For postgresql 8.4, you need to include the template, or else it'll complain:

ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) HINT: Use the same encoding as in the template database, or use template0 as template.

So use the hint and include -T and use template0

createdb -O wil -E UTF8 noteleaf_production -T template0

Mel Riffe commented Fri Jun 24 19:37:56 UTC 2011:

Here's the SQL for 8.4:

CREATE DATABASE demodb1 WITH OWNER demorole1 TEMPLATE template0 ENCODING 'UTF8';

Cheers Mike for the great articles; working my way through them.

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)