Getting Started with PostgreSQLLesson 1.3

How to create a PostgreSQL database and connect to it

CREATE DATABASE, DROP DATABASE, OWNER clause, template databases, database encoding, connecting with \c

Creating Databases

A PostgreSQL server (cluster) can host many databases. Each is isolated — tables in one database are not directly accessible from another.

Create a database

CREATE DATABASE app_db
  OWNER postgres
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

Most of the time you only need the name: CREATE DATABASE app_db;. PostgreSQL copies settings from the template1 template database by default. Specify TEMPLATE template0 when you need a clean encoding.

Drop a database

DROP DATABASE app_db;

This is irreversible. You cannot drop a database while connected to it — connect to postgres first.

List and connect

\l            -- list databases
\c app_db     -- switch to app_db

From the shell you can also use the wrapper command: createdb app_db -U postgres and dropdb app_db -U postgres. These are thin wrappers around the SQL commands. Once connected, every statement you run targets the current database until you switch with \c.