PostgreSQL บน Ubuntu

# เข้าสู่ shell ของ postgres user
sudo -u postgres psql

# หรือเข้า shell ก่อนแล้วเข้า psql
sudo -u postgres bash
psql

-- เข้าสู่ psql แล้วพิมพ์คำสั่งนี้
CREATE USER appuser WITH PASSWORD 'your_secure_password';

-- หรือสร้างโดยมีสิทธิ์ create database
CREATE USER appuser WITH CREATEDB PASSWORD 'your_secure_password';

-- หรือสร้าง superuser (ระวัง!)
CREATE USER admin WITH SUPERUSER PASSWORD 'your_secure_password';

-- ตรวจสอบ user ที่มีอยู่
\du

-- สร้าง database ใหม่
CREATE DATABASE appdb;

-- สร้าง database พร้อมระบุ owner
CREATE DATABASE appdb OWNER appuser;

-- หรือใช้ชื่อ user ปัจจุบันเป็น owner
CREATE DATABASE appdb OWNER CURRENT_USER;

-- ตรวจสอบ database ที่มีอยู่
\l

-- เข้าเชื่อมต่อ database
\c appdb

-- ให้สิทธิ์ทั้งหมดบน database
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

-- ให้สิทธิ์บน public schema
GRANT ALL ON SCHEMA public TO appuser;

-- ให้สิทธิ์บนตารางทั้งหมด (สำหรับ table ที่สร้างใหม่)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;

-- ให้สิทธิ์บน sequence ทั้งหมด
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO appuser;

-- สำหรับ table ที่มีอยู่แล้ว
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

# เชื่อมต่อด้วย user ที่มีอยู่
psql -U appuser -d appdb -h localhost

# หรือระบุ password ใน command (ไม่แนะนำสำหรับ production)
PGPASSWORD='your_password' psql -U appuser -d appdb -h localhost

# ตรวจสอบการเชื่อมต่อ
SELECT current_user, current_database();

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role VARCHAR(20) DEFAULT 'user',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- เพิ่มข้อมูลข้อมูลเดียว
INSERT INTO users (username, email, password_hash, full_name, role)
VALUES ('john_doe', 'john@example.com', 'hashed_password_here', 'John Doe', 'admin');

-- เพิ่มข้อมูลหลายแถว
INSERT INTO users (username, email, password_hash, full_name, role)
VALUES
    ('jane_smith', 'jane@example.com', 'hashed_password2', 'Jane Smith', 'user'),
    ('bob_wilson', 'bob@example.com', 'hashed_password3', 'Bob Wilson', 'user'),
    ('alice_jones', 'alice@example.com', 'hashed_password4', 'Alice Jones', 'moderator');

-- เลือกทุกคอลัมน์ทุกแถว
SELECT * FROM users;

-- เลือกคอลัมน์ที่ต้องการ
SELECT id, username, email, role FROM users;

-- ใช้ WHERE filter
SELECT * FROM users WHERE role = 'admin';

-- ใช้ LIMIT และ OFFSET (สำหรับ pagination)
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 0;

-- ใช้ LIKE search
SELECT * FROM users WHERE username LIKE '%john%';

-- ใช้ aggregate functions
SELECT COUNT(*) as total_users, AVG(id) as avg_id FROM users;

-- ใช้ GROUP BY
SELECT role, COUNT(*) as count FROM users GROUP BY role;

-- ใช้ JOIN (ถ้ามี table อื่น)
-- SELECT u.username, p.title FROM users u JOIN posts p ON u.id = p.user_id;

-- อัปเดตข้อมูลทั้งหมด (ระวัง!)
UPDATE users SET role = 'guest';

-- อัปเดตข้อมูลด้วย WHERE
UPDATE users SET role = 'moderator' WHERE username = 'bob_wilson';

-- อัปเดตหลายคอลัมน์
UPDATE users 
SET email = 'new_email@example.com', 
    updated_at = CURRENT_TIMESTAMP 
WHERE id = 1;

-- อัปเดตด้วย subquery
UPDATE users 
SET role = 'admin' 
WHERE id IN (SELECT user_id FROM posts WHERE is_pinned = true);

-- ลบข้อมูลทั้งหมด (ระวัง!)
DELETE FROM users;

-- ลบด้วย WHERE
DELETE FROM users WHERE is_active = false;

-- ลบด้วย subquery
DELETE FROM users WHERE id NOT IN (SELECT DISTINCT user_id FROM posts);

-- ใช้ RETURNING เพื่อดูข้อมูลที่ถูกลบ
DELETE FROM users WHERE username = 'test_user' RETURNING *;

# สำรอง database เดียว (format SQL)
pg_dump -U appuser -d appdb -f /backup/appdb.sql

# สำรอง database เดียว (format custom - compressed)
pg_dump -U appuser -d appdb -Fc -f /backup/appdb.dump

# สำรองทุก database
pg_dumpall -U postgres -f /backup/all_databases.sql

# สำรองเฉพาะ schema (ไม่มีข้อมูล)
pg_dump -U appuser -d appdb --schema-only -f /backup/appdb_schema.sql

# สำรองเฉพาะข้อมูล (ไม่มี schema)
pg_dump -U appuser -d appdb --data-only -f /backup/appdb_data.sql

# สำรองเฉพาะ table ที่ต้องการ
pg_dump -U appuser -d appdb -t users -t posts -f /backup/users_posts.sql