PostgreSQL Get Started

Connecting to PostgreSQL

psql -U postgres

Open pgAdmin 4
→ Connect to server

import psycopg2
conn = psycopg2.connect()

psql -h host -U user

🔹 Basic psql Commands

The psql command-line interface provides powerful commands for database management. These meta-commands start with backslash and help you navigate and inspect your databases efficiently.

-- List all databases
\l

-- Connect to a database
\c mydb

-- List all tables
\dt

-- Describe a table structure
\d tablename

-- List all users
\du

-- Get help
\?

-- Quit psql
\q
                            

🔹 Creating Your First Database

Databases are containers for organizing related tables and data. Creating a database is the first step in building any application that needs to store information.

-- Create a new database
CREATE DATABASE bookstore;

-- Connect to the database
\c bookstore

-- Verify connection
SELECT current_database();
                            

🔹 Creating Tables

Tables store data in rows and columns. Each column has a specific data type that defines what kind of information it can hold, ensuring data integrity.

-- Create a books table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price NUMERIC(10, 2),
    published_date DATE,
    in_stock BOOLEAN DEFAULT true
);

-- View table structure
\d books
                            

🔹 Inserting Data

The INSERT statement adds new rows to your tables. You can insert single or multiple records at once, making it easy to populate your database with information.

-- Insert a single book
INSERT INTO books (title, author, price, published_date)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 12.99, '1925-04-10');

-- Insert multiple books
INSERT INTO books (title, author, price, published_date)
VALUES 
    ('1984', 'George Orwell', 14.99, '1949-06-08'),
    ('To Kill a Mockingbird', 'Harper Lee', 13.99, '1960-07-11'),
    ('Pride and Prejudice', 'Jane Austen', 11.99, '1813-01-28');

-- View inserted data
SELECT * FROM books;
                            

🔹 Querying Data

SELECT statements retrieve data from tables. You can filter, sort, and limit results to find exactly the information you need from your database.

-- Select all books
SELECT * FROM books;

-- Select specific columns
SELECT title, author FROM books;

-- Filter with WHERE clause
SELECT * FROM books WHERE price < 14.00;

-- Sort results
SELECT * FROM books ORDER BY price DESC;

-- Limit results
SELECT * FROM books LIMIT 3;
                            

🔹 Updating Data

UPDATE statements modify existing records in your tables. Always use a WHERE clause to specify which rows to update, or you'll change all records.

-- Update a single book's price
UPDATE books 
SET price = 15.99 
WHERE title = '1984';

-- Update multiple columns
UPDATE books 
SET price = 10.99, in_stock = false 
WHERE book_id = 3;

-- View updated data
SELECT title, price, in_stock FROM books WHERE book_id IN (2, 3);
                            

🔹 Deleting Data

DELETE statements remove rows from tables. Be careful with DELETE operations and always use WHERE clauses to avoid accidentally removing all your data.

-- Delete a specific book
DELETE FROM books WHERE book_id = 4;

-- Delete books matching a condition
DELETE FROM books WHERE price > 20.00;

-- View remaining books
SELECT COUNT(*) FROM books;
                            

🔹 Practice Exercise

Try creating your own database and table. This exercise reinforces what you've learned about creating databases, tables, and performing basic CRUD operations.

-- Solution
CREATE DATABASE school;
\c school

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade CHAR(1)
);

INSERT INTO students (name, age, grade) VALUES
    ('Alice Johnson', 20, 'A'),
    ('Bob Smith', 19, 'B'),
    ('Carol White', 21, 'A');