Database

PostgreSQL Basics

Learn to work with PostgreSQL in Piglet Run.

Connect

Using psql

psql postgres://postgres@localhost/postgres

Using Python

import psycopg

conn = psycopg.connect("postgres://postgres@localhost/postgres")

Create Database

CREATE DATABASE myapp;

Install Extensions

PostgreSQL 18 with 400+ extensions available:

-- Vector search
CREATE EXTENSION vector;

-- Time series
CREATE EXTENSION timescaledb;

-- Full text search (Chinese)
CREATE EXTENSION zhparser;

Basic Operations

-- Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Query
SELECT * FROM users;

Monitoring

View database performance at:

http://<ip>/ui/d/pgsql-overview

Next Steps