On this page

Setting Up PostgreSQL 18

12 min read TextCh. 1 — Database Fundamentals

Setting Up Your PostgreSQL Environment

Before you can write SQL, you need a running PostgreSQL instance. This lesson walks you through installing PostgreSQL 18, connecting with both psql (the command-line client) and pgAdmin (the graphical interface), and creating your first real database with tables.

Installing PostgreSQL 18

macOS

The easiest way on macOS is through the official installer from postgresql.org, or via Homebrew:

brew install postgresql@18
brew services start postgresql@18

Homebrew will configure PostgreSQL to run automatically at login and create a default database cluster in /opt/homebrew/var/postgresql@18.

Ubuntu / Debian Linux

# Add the official PostgreSQL APT repository
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Install PostgreSQL 18
sudo apt-get update
sudo apt-get install postgresql-18

# Start the service
sudo systemctl start postgresql
sudo systemctl enable postgresql

Windows

Download the official installer from https://www.postgresql.org/download/windows/. The installer includes:

  • The PostgreSQL server (version 18)
  • pgAdmin 4 (graphical interface)
  • The psql command-line client
  • Stack Builder (for extensions)

Run the installer as Administrator, choose your installation directory, set a password for the postgres superuser, and keep the default port (5432).

Docker is often the fastest way to spin up a PostgreSQL instance without touching your system configuration:

docker run --name pg18 \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -d postgres:18

This starts a PostgreSQL 18 container with the password mysecretpassword, exposing port 5432. Connect with:

docker exec -it pg18 psql -U postgres

The psql Command-Line Client

psql is the official PostgreSQL interactive terminal. Despite being a command-line tool, it is extremely powerful and the preferred tool of experienced database administrators.

Connecting to PostgreSQL

# Connect as the postgres user to the default database
psql -U postgres

# Connect to a specific database
psql -U postgres -d bookstore

# Connect to a remote server
psql -h hostname -U username -d dbname -p 5432

Essential psql Meta-Commands

Once inside psql, these backslash commands help you navigate:

\l                  -- List all databases
\c bookstore        -- Connect to the 'bookstore' database
\dt                 -- List all tables in current database
\d authors          -- Describe the 'authors' table (columns, types, constraints)
\di                 -- List indexes
\du                 -- List roles/users
\x                  -- Toggle expanded output mode (great for wide rows)
\timing             -- Show query execution time
\e                  -- Open your $EDITOR to write a query
\q                  -- Quit psql

Running SQL Files

You can execute a .sql file directly from psql:

psql -U postgres -d bookstore -f schema.sql

Or from within psql:

\i /path/to/schema.sql

pgAdmin 4 — The Graphical Interface

pgAdmin 4 is the official open-source graphical interface for PostgreSQL. After installation, open it in your browser (usually at http://localhost:5050) and connect to your server.

Key pgAdmin features:

  • Schema browser: navigate databases, schemas, tables, views, and functions in a tree view
  • Query Tool: a full SQL editor with syntax highlighting, autocomplete, and execution plans
  • Table data viewer: browse and edit table rows visually
  • ERD tool: generate entity-relationship diagrams from existing schemas

Creating Your First Database

In PostgreSQL, every database is isolated. Tables in one database are not visible from another. Create a new database for your project:

-- Run this as the postgres superuser
CREATE DATABASE bookstore
    WITH
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';

Always specify UTF-8 encoding. It supports all languages and emoji, and is the standard for modern applications. If you omit it, PostgreSQL inherits the locale from the operating system, which may not be UTF-8 on all systems.

Core Data Types Quick Reference

Before creating tables, you need to know your type options. Here are the most important PostgreSQL data types with guidance on when to use each:

Numbers

-- Use INTEGER for most IDs and counts (4 bytes, up to ~2.1 billion)
age INTEGER

-- Use BIGINT for very large numbers (8 bytes, up to ~9.2 quintillion)
view_count BIGINT

-- Use NUMERIC for money and exact decimals — never FLOAT for money!
price NUMERIC(10, 2)   -- 10 total digits, 2 decimal places

-- SERIAL is shorthand for auto-incrementing INTEGER
id SERIAL PRIMARY KEY

Text

-- VARCHAR(n) for strings with a known maximum length
email VARCHAR(320)
first_name VARCHAR(100)

-- TEXT for unlimited strings (blog posts, descriptions)
-- In PostgreSQL, TEXT and VARCHAR without length are equivalent in performance
body TEXT

-- CHAR(n) for fixed-length strings (almost never needed)
country_code CHAR(2)

Dates and Times

-- DATE: just the date, no time
birth_date DATE

-- TIMESTAMPTZ: date + time + time zone (use this for most timestamps)
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

-- INTERVAL for durations
trial_period INTERVAL DEFAULT '30 days'

Booleans and JSON

-- BOOLEAN for flags
is_active BOOLEAN NOT NULL DEFAULT TRUE

-- JSONB for semi-structured data (indexed, binary format)
metadata JSONB

Column Constraints

Constraints enforce data integrity at the database level, not just the application level:

CREATE TABLE employees (
    employee_id  SERIAL PRIMARY KEY,
    email        VARCHAR(320) NOT NULL UNIQUE,
    full_name    VARCHAR(200) NOT NULL,
    salary       NUMERIC(12, 2) NOT NULL CHECK (salary >= 0),
    department   VARCHAR(100) NOT NULL DEFAULT 'General',
    hired_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
  • NOT NULL: the column must have a value — empty string is still a value, but NULL is not allowed
  • UNIQUE: no two rows can have the same value in this column
  • CHECK (condition): the value must satisfy a boolean expression
  • DEFAULT value: if no value is provided, use this default

These constraints run inside the database engine. Even if your application has a bug and tries to insert invalid data, PostgreSQL will reject it and return an error. This is your last line of defense for data quality.

Verifying Your Setup

After creating your tables and inserting some data, verify everything is working:

-- See all your tables
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- Check column details for a table
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'books'
ORDER BY ordinal_position;

The information_schema is a standard SQL schema that PostgreSQL populates with metadata about your database structure. It is the programmatic equivalent of psql's \d command.

You now have a working PostgreSQL 18 environment and understand how to create tables with proper data types and constraints. In the next lesson, you will start querying this data with SELECT, WHERE, and ORDER BY.

Use pgAdmin for Visual Schema Management
pgAdmin 4 provides a graphical interface for PostgreSQL. You can browse tables, run queries, view execution plans, and manage users without memorizing every psql command. It is especially useful when you are learning and want to see your schema visually.
psql Meta-Commands
Inside psql, commands starting with backslash are meta-commands (not SQL): \l lists databases, \c dbname connects to a database, \dt lists tables, \d tablename describes a table, and \q quits. These are essential for navigating your database from the terminal.
Never Run PostgreSQL as the Superuser in Production
In development, using the default postgres superuser is fine. In production, always create a dedicated application user with only the permissions it needs. A superuser can drop any table, disable any constraint, and bypass row-level security — far more privilege than an application should have.
sql
-- Create a database for our project
CREATE DATABASE bookstore;

-- Connect to it (psql command, not SQL)
-- \c bookstore

-- Create the authors table
CREATE TABLE authors (
    author_id   SERIAL PRIMARY KEY,
    name        VARCHAR(200)  NOT NULL,
    nationality VARCHAR(100),
    born_year   INTEGER       CHECK (born_year > 1000 AND born_year <= 2026),
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- Create the books table with a foreign key
CREATE TABLE books (
    book_id      SERIAL PRIMARY KEY,
    author_id    INTEGER       NOT NULL REFERENCES authors(author_id),
    title        VARCHAR(500)  NOT NULL,
    isbn         VARCHAR(20)   UNIQUE,
    price        NUMERIC(8, 2) NOT NULL CHECK (price >= 0),
    published_at DATE
);

-- Insert some test data
INSERT INTO authors (name, nationality, born_year)
VALUES
    ('George Orwell',     'British',   1903),
    ('Gabriel García Márquez', 'Colombian', 1927),
    ('Ursula K. Le Guin', 'American', 1929);

INSERT INTO books (author_id, title, isbn, price, published_at)
VALUES
    (1, '1984',                 '9780451524935', 12.99, '1949-06-08'),
    (1, 'Animal Farm',          '9780451526342',  9.99, '1945-08-17'),
    (2, 'One Hundred Years of Solitude', '9780060883287', 15.99, '1967-05-30');

-- Verify the data
SELECT * FROM authors;
SELECT * FROM books;