1
2
3
4
5
6
# Connect to a database
psql -U username -d database_name

# Connect with host and port
psql -h hostname -p 5432 -U username -d database_name

1
2
3
4
5
6
7
\l          -- List all databases
\c database_name  -- Connect to a specific database
\dt         -- List tables in current database
\d table_name    -- Describe table structure
\du         -- List users
\q          -- Quit psql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Basic creation
CREATE DATABASE my_database;

-- With additional options
CREATE DATABASE my_database
    WITH 
    OWNER = username
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- Drop a database
DROP DATABASE IF EXISTS my_database;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Table with constraints
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    content TEXT,
    tags TEXT[] DEFAULT '{}',
    metadata JSONB
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Add a column
ALTER TABLE users 
ADD COLUMN last_login TIMESTAMP;

-- Modify column type
ALTER TABLE users 
ALTER COLUMN username TYPE VARCHAR(100);

-- Add constraint
ALTER TABLE posts 
ADD CONSTRAINT unique_title UNIQUE (title);

-- Drop a column
ALTER TABLE users 
DROP COLUMN last_login;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- JSON column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

-- Insert JSON
INSERT INTO products (name, details) VALUES 
(
    'Laptop', 
    '{"brand": "Dell", "specs": {"ram": "16GB", "storage": "512GB SSD"}}'::JSONB
);

-- Query JSON
SELECT name, details->'specs'->>'ram' AS ram_size 
FROM products 
WHERE details->>'brand' = 'Dell';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Create table with array
CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

-- Insert with array
INSERT INTO blog_posts (title, tags) 
VALUES ('PostgreSQL Guide', ARRAY['database', 'tutorial', 'postgresql']);

-- Query array
SELECT * FROM blog_posts WHERE 'postgresql' = ANY(tags);

1
2
3
4
5
6
7
8
9
10
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create table with UUID
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Basic insert
INSERT INTO users (username, email) 
VALUES ('johndoe', 'john@example.com');

-- Insert multiple rows
INSERT INTO users (username, email) VALUES 
('alice', 'alice@example.com'),
('bob', 'bob@example.com');

-- Insert with returning
INSERT INTO users (username, email) 
VALUES ('charlie', 'charlie@example.com')
RETURNING id, username;

1
2
3
4
5
6
7
8
9
10
11
12
-- Basic select
SELECT * FROM users;

-- Filtered select
SELECT username, email FROM users 
WHERE is_active = TRUE;

-- Ordering and limiting
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 10;

1
2
3
4
5
6
7
8
9
10
-- Basic update
UPDATE users 
SET is_active = FALSE 
WHERE username = 'johndoe';

-- Conditional update
UPDATE products 
SET price = price * 1.1 
WHERE category = 'electronics';

1
2
3
4
5
6
7
8
-- Basic delete
DELETE FROM users 
WHERE username = 'johndoe';

-- Prevent delete with foreign key
DELETE FROM users 
WHERE id NOT IN (SELECT DISTINCT user_id FROM posts);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Inner Join
SELECT u.username, p.title 
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- Left Join
SELECT u.username, p.title 
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

-- Complex Join with Aggregation
SELECT 
    u.username, 
    COUNT(p.id) as post_count,
    MAX(p.created_at) as latest_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.username
ORDER BY post_count DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- B-Tree Index (default)
CREATE INDEX idx_users_email ON users(email);

-- Unique Constraint
ALTER TABLE users 
ADD CONSTRAINT unique_email UNIQUE (email);

-- Partial Index
CREATE INDEX idx_active_users 
ON users(username) 
WHERE is_active = TRUE;

-- GIN Index for JSON/Array
CREATE INDEX idx_product_tags 
ON products USING GIN (tags);

1
2
3
4
5
6
7
-- Concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Pattern Matching
SELECT * FROM users 
WHERE email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';

1
2
3
4
5
6
7
8
9
10
-- Current timestamp
SELECT CURRENT_TIMESTAMP;

-- Date arithmetic
SELECT 
    created_at, 
    created_at + INTERVAL '1 day' AS tomorrow,
    AGE(CURRENT_DATE, created_at) AS account_age
FROM users;

1
2
3
4
5
6
7
8
9
10
11
WITH top_users AS (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
    ORDER BY post_count DESC
    LIMIT 5
)
SELECT u.username, tu.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id;

1
2
3
4
5
6
SELECT 
    username,
    created_at,
    RANK() OVER (ORDER BY created_at) as registration_rank
FROM users;

1
2
3
4
5
-- Update nested JSONB
UPDATE products
SET details = details || '{"warranty": "2 years"}'::JSONB
WHERE id = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Scalar Function
CREATE OR REPLACE FUNCTION calculate_user_age(birthdate DATE)
RETURNS INTEGER AS $$
BEGIN
    RETURN DATE_PART('year', AGE(CURRENT_DATE, birthdate));
END;
$$ LANGUAGE plpgsql;

-- Trigger Function
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

1
2
3
4
5
-- Explain query plan
EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE created_at > NOW() - INTERVAL '30 days';

1
2
3
4
5
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;