Creating Databases & Tables

A single PostgreSQL server can host many separate databases, each fully isolated from the others — this lesson covers creating one, connecting to it, and building tables inside it.

Creating and connecting to a database

SQL postgres=#

CREATE DATABASE storefront;
\c storefront

Output

CREATE DATABASE
You are now connected to database "storefront" as user "postgres".
storefront=#

Notice the prompt itself changes from postgres=# to storefront=# after \c — a clear, constant reminder of which database your next query will run against.

Creating a table

SQL storefront=#

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT now()
);

Output

CREATE TABLE

This should look familiar from the SQL course's Creating Tables lesson — NOT NULL and UNIQUE are standard SQL constraints. DEFAULT now() is worth calling out: now() is a PostgreSQL function returning the current timestamp, so any row inserted without an explicit created_at gets stamped automatically.

Inspecting a table's structure

psql storefront=#

\d customers

Output

                                     Table "public.customers"
   Column   |            Type             | Collation | Nullable |               Default                
------------+------------------------------+-----------+----------+---------------------------------------
 id         | integer                     |           | not null | nextval('customers_id_seq'::regclass)
 full_name  | text                        |           | not null | 
 email      | text                        |           | not null | 
 created_at | timestamp without time zone |           |          | now()
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

Note: \d tablename is one of the most useful commands you'll run in psql — it shows every column's exact type, nullability, default, and every index and constraint on the table, all in one place, without writing a query against PostgreSQL's internal system catalogs yourself.

Dropping tables and databases

SQL storefront=#

DROP TABLE IF EXISTS customers;

Output

DROP TABLE

IF EXISTS means the statement succeeds quietly even if the table's already gone, instead of throwing an error — useful in setup scripts you might re-run. DROP DATABASE works the same way, but can't be run while you're connected to the database you're trying to drop — you'd need to \c to a different one first.

PreviousData Types NextIndexes & EXPLAIN ANALYZE