PostgreSQL Guide

PostgreSQLAdvanced Relational Database

Learn SQL, queries, joins, indexes, transactions, and advanced PostgreSQL features for production applications.

Contents

PostgreSQL Basics

PostgreSQL is an advanced open-source relational database management system. Learn table creation, data types, and basic operations.

sql
-- Create Database
CREATE DATABASE mydb;

-- Create Table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Data Types
-- INTEGER, BIGINT, SERIAL, BIGSERIAL
-- VARCHAR(n), TEXT, CHAR(n)
-- BOOLEAN
-- DATE, TIME, TIMESTAMP
-- NUMERIC(precision, scale), DECIMAL, FLOAT, DOUBLE PRECISION
-- JSON, JSONB
-- UUID
-- ARRAY

-- Insert Data
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

INSERT INTO users (name, email, age) 
VALUES 
    ('Jane Doe', 'jane@example.com', 25),
    ('Bob Smith', 'bob@example.com', 35);

-- Select Data
SELECT * FROM users;
SELECT name, email FROM users;
SELECT * FROM users WHERE age > 25;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users LIMIT 10;

-- Update Data
UPDATE users SET age = 31 WHERE id = 1;
UPDATE users SET age = age + 1 WHERE age < 30;

-- Delete Data
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;