Working directly with Postgres

Introduction to Databases

A database allows applications to store data permanently. Relational databases use Structured Query Language (SQL) to manage and interact with the data.

Installing and Accessing PostgreSQL

1. After installing PostgreSQL, launch it from a bash prompt with psql.
2. Commands are issued at the postgres=# prompt.

Navigating PostgreSQL

• Use \list to display all databases currently in PostgreSQL.
• If command output is long, :▮ indicates more content. Scroll with return (line by line) or space (page by page), and press Q to exit.

Creating Databases

• Create a new database with the following command:

  CREATE DATABASE database_name;

• A semicolon is required at the end of SQL commands.
• Confirmation is shown as CREATE DATABASE.

Managing Databases

• Switch to a new database with:

  \connect database_name

• The prompt will change to reflect the new database context, e.g., database_name=#.

Creating Tables

• Example of creating a table named contacts:

  CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    date_of_birth DATE,
    street_address_1 TEXT,
    street_address_2 TEXT,
    city TEXT,
    state TEXT,
    zip TEXT,
    phone TEXT,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
  );

Viewing Tables

• To see all tables, use:

  \dt

Data Types

• Common data types include:
  • TEXT: Similar to String.
  • INTEGER: A whole number.
  • BOOLEAN: true, false, or nil.
  • DATE: A date value without a time part.
  • TIME: A time value without a date part.
  • TIMESTAMP: For values that have both date and time parts.
  • DECIMAL: Similar to Float.

Inserting Data

• To insert data into the contacts table:

  INSERT INTO contacts (
    first_name, 
    last_name, 
    date_of_birth, 
    street_address_1, 
    street_address_2, 
    city, 
    state, 
    zip,
    phone,
    notes
  ) VALUES (
    'Carol', 
    'Reynolds', 
    '2016-10-20', 
    '4556 Mirna Shores', 
    'Apt. 111', 
    'Stromanhaven', 
    'DE', 
    '13654-8312',
    '308-571-8066 x3565',
    'We met at the networking event'
  );