docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=mypassword \
  -p 5432:5432 \
  postgres:16

sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo -u postgres psql  # connect as postgres superuser

# Connect to a local database
psql -h localhost -U postgres -d postgres

# Common psql commands
\l          -- list databases
\c mydb     -- connect to a database
\dt         -- list tables in current schema
\d users    -- describe table structure
\q          -- quit

-- Create a database
CREATE DATABASE bookstore;

-- Connect to it
\c bookstore

-- Authors table
CREATE TABLE authors (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(200) NOT NULL,
    email      VARCHAR(200) UNIQUE,
    country    VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Books table
CREATE TABLE books (
    id           SERIAL PRIMARY KEY,
    title        VARCHAR(300) NOT NULL,
    author_id    INT NOT NULL REFERENCES authors(id),
    price        NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    published_at DATE,
    genre        VARCHAR(100),
    in_stock     BOOLEAN DEFAULT TRUE
);

INSERT INTO authors (name, email, country)
VALUES
    ('Alice Johnson', 'alice@example.com', 'Norway'),
    ('Bob Smith',     'bob@example.com',   'UK'),
    ('Clara Berg',    'clara@example.com', 'Sweden');

INSERT INTO books (title, author_id, price, published_at, genre)
VALUES
    ('PostgreSQL Mastery', 1, 39.99, '2024-03-01', 'Technology'),
    ('Clean Code',         2, 29.99, '2023-11-15', 'Technology'),
    ('Oslo Stories',       1, 19.99, '2025-01-10', 'Fiction'),
    ('Database Design',    3, 44.99, '2024-06-20', 'Technology');

-- Basic select
SELECT title, price FROM books;

-- Filter with WHERE
SELECT title, price
FROM books
WHERE genre = 'Technology' AND price < 40;

-- Pattern matching
SELECT name FROM authors WHERE name ILIKE 'a%';  -- case-insensitive LIKE

-- NULL handling
SELECT * FROM books WHERE published_at IS NOT NULL;

-- Sorting
SELECT title, price FROM books ORDER BY price DESC;

-- Limit and offset (pagination)
SELECT title FROM books ORDER BY title LIMIT 10 OFFSET 20;

-- Get books with author names
SELECT b.title, a.name AS author, b.price
FROM books b
JOIN authors a ON b.author_id = a.id
ORDER BY b.price DESC;

-- Left join — include authors with no books
SELECT a.name, COUNT(b.id) AS book_count
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.name
ORDER BY book_count DESC;

-- Count, sum, average
SELECT
    COUNT(*)              AS total_books,
    AVG(price)::NUMERIC(10,2) AS avg_price,
    MAX(price)            AS most_expensive,
    SUM(price)            AS total_value
FROM books;

-- Group by genre
SELECT genre, COUNT(*) AS count, ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY genre
HAVING COUNT(*) > 1
ORDER BY count DESC;

CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  TEXT,
    tags  TEXT[]
);

INSERT INTO products (name, tags) VALUES ('Widget', ARRAY['electronics', 'gadget']);

SELECT * FROM products WHERE 'electronics' = ANY(tags);

ALTER TABLE books ADD COLUMN metadata JSONB;

UPDATE books SET metadata = '{"pages": 350, "edition": 2}' WHERE id = 1;

SELECT title, metadata->>'pages' AS pages FROM books WHERE metadata IS NOT NULL;
SELECT * FROM books WHERE (metadata->>'pages')::int > 300;

-- Create a search index
CREATE INDEX books_fts ON books USING GIN (to_tsvector('english', title));

-- Search
SELECT title FROM books
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'design & database');

-- B-tree index (default) — great for equality and range queries
CREATE INDEX idx_books_genre ON books(genre);

-- Partial index — only index rows matching a condition
CREATE INDEX idx_books_in_stock ON books(price) WHERE in_stock = TRUE;

-- Composite index — for queries filtering on multiple columns
CREATE INDEX idx_books_genre_price ON books(genre, price);

-- Check which indexes exist
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'books';

-- See query plan with index usage
EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'Technology';

// Install: dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Postgres")));

// appsettings.json
{
  "ConnectionStrings": {
    "Postgres": "Host=localhost;Database=bookstore;Username=postgres;Password=mypassword"
  }
}