Create Database

A database is where PostgreSQL keeps all your data. Before you can create tables or run queries, you need a database to work with. This lesson covers everything about creating and managing databases in PostgreSQL.

Creating a Database

Creating a database is straightforward:

CREATE DATABASE school;

That single line creates a brand new, empty database called school. PostgreSQL copies the template database (template1 by default) to create it. You will not usually need to worry about templates, but it is good to know they exist.

You can add options to control the database encoding, locale, and other settings:

CREATE DATABASE school
  WITH ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TEMPLATE = template0;

Using template0 with explicit encoding ensures a clean database with the correct character set. This is important for international applications.

Listing Databases

To see all databases on your PostgreSQL server:

\l

This shows a list with the database name, owner, encoding, and other details. The default databases you will always see are:

postgres — A default database for administrative tasks
template0 and template1 — Templates used when creating new databases

Connecting to a Database

To connect to a specific database with psql:

psql -U postgres -d school

Or switch to it from within psql:

\c school

The prompt will change to show which database you are connected to, like school=#.

Renaming and Dropping Databases

To rename a database:

ALTER DATABASE school RENAME TO academy;

To delete a database (be careful — this is permanent):

DROP DATABASE school;

You cannot drop a database while others are connected to it. You need to disconnect first or use:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'school' AND pid != pg_backend_pid();

This forcefully disconnects all other users before dropping.

Database Properties

You can check a database's properties with:

\l+

This shows additional details like size, tablespace, and connection limits. Useful for monitoring and administration.