Mastering PostgreSQL Queries: A Comprehensive Guide with Examples

• 테이블 생성 및 데이터 삽입 CREATE & INSERT

CREATE TABLE test_employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary NUMERIC
);

INSERT INTO test_employees (name, department, salary) VALUES
    ('John Doe', 'IT', 50000),
    ('Jane Smith', 'HR', 60000),
    ('Alice Johnson', 'Finance', 70000)

CREATE TABLE test_employee_audit (
    audit_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    action TEXT,
    changed_at TIMESTAMP
);

CREATE TABLE test_departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO test_departments (name)
VALUES ('Human Resources'),
       ('Finance'),
       ('Marketing'),
       ('IT');


---
CREATE TABLE test_employees (
    id SERIAL PRIMARY KEY,
    name INTEGER,
    department VARCHAR(100),
    salary NUMERIC
);

DML : Select, Insert Into, Update, Delete

• 조회 Select & 리밋 Limit

SELECT * FROM test_employees LIMIT 1;

• 수정 Update

UPDATE test_departments
SET name = 'HR'
WHERE name = 'Human Resources';

DROP TABLE IF EXISTS employee_audit;

• 데이터 삭제 Delete

DELETE FROM test_employees
WHERE salary > 50000;

DDL: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX

• 컬럼 수정 Alter & Drop

CREATE TABLE example_table (
    example_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    email VARCHAR(255)
);
ALTER TABLE example_table
ADD COLUMN address VARCHAR(255);
DROP TABLE IF EXISTS example_table;

인덱스 Index

• 인덱스 생성

CREATE INDEX idx_department ON test_employees(department);

• 부분 인덱스 Partial Index (인덱스 크기 줄이고 쿼리 성능 향상)

CREATE INDEX idx_high_salary ON test_employees (id) WHERE salary > 50000;

• 표현식 인덱스 Expression Index

CREATE INDEX idx_uppercase_name ON test_employees (UPPER(LEFT(name, 1)));

• 커버링 인덱스 Covering Index

CREATE INDEX idx_covering_index ON test_employees (name, salary);

• 제약 조건 추가 Constraints

ALTER TABLE test_employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);

ALTER TABLE test_employees ADD CONSTRAINT unique_id UNIQUE (id);

• 트리거 생성 Trigger

CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO test_employee_audit (employee_id, action, changed_at)
    VALUES (OLD.id, TG_OP, now());
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employee_changes
AFTER INSERT OR UPDATE OR DELETE ON test_employees
FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();

윈도우 함수 Window Function

https://adjh54.tistory.com/261

• 순위 함수 (RANK OVER) ex) 1233566

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM test_employees;

• 순위 함수 (DENSE_RANK OVER) ex) 1233455

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM test_employees;

• 순위 함수 (PERCENTAGE_RANK OVER) ex) 0.1666, 0.3333, 0.3333, 0.6666, 0.8333, 0.8333

SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary DESC) AS rank
FROM test_employees;

• FIRST_VALUE / LAST_VALUE

SELECT name, salary, FIRST_VALUE(name) OVER (ORDER BY salary DESC) AS first_value
FROM test_employees;

• ROW NUM

SELECT name, salary, department, ROW_NUMBER() OVER (ORDER BY salary) AS salary_row_num
FROM test_employees ORDER BY salary ASC;

• CUME_DIST (퍼센티지 계산)

SELECT name, salary, CUME_DIST() OVER (ORDER BY salary DESC) AS relative_rank_percentage
FROM test_employees;

• LAG (이전 로우값 반환)

SELECT name, salary, LAG(salary) OVER (ORDER BY salary DESC) AS relative_rank_percentage
FROM test_employees;
SELECT name, salary, LAG(salary, 2) OVER (ORDER BY salary DESC) AS relative_rank_percentage
FROM test_employees;

• LEAD (다음 로우값 반)

SELECT name, salary, LEAD(salary) OVER (ORDER BY salary DESC) AS relative_rank_percentage
FROM test_employees;
SELECT name, salary, LEAD(salary, 2, 0) OVER (ORDER BY salary DESC) AS relative_rank_percentage
FROM test_employees;

• NTILE(n) (n개의 그룹으로 분할해서 배정)

SELECT name, salary, NTILE(3) OVER (ORDER BY salary DESC) AS relative_rank_percentage
FROM test_employees;

• 피벗 쿼리 Pivot Query

1)
SELECT
    name,
    MAX(CASE WHEN department = 'Finance' THEN salary ELSE NULL END) AS finance,
    MAX(CASE WHEN department = 'HR' THEN salary ELSE NULL END) AS hr,
    MAX(CASE WHEN department = 'IT' THEN salary ELSE NULL END) AS it
FROM
    test_employees
GROUP BY
    name;
2)
SELECT * FROM crosstab(
    'SELECT name, department, salary FROM test_employees ORDER BY 1',
    'SELECT DISTINCT department FROM test_employees ORDER BY 1'
) AS ct (name VARCHAR(100), "Finance" NUMERIC, "HR" NUMERIC, "IT" NUMERIC);

• 함수 생성 function

CREATE FUNCTION get_employee_count(dept_name VARCHAR) RETURNS INTEGER AS $$
DECLARE
    employee_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO employee_count FROM employees WHERE department = dept_name;
    RETURN employee_count;
END;
$$ LANGUAGE plpgsql;

SELECT get_employee_count('HR');

CREATE FUNCTION get_employee_count(dept_name VARCHAR) RETURNS INTEGER AS '
DECLARE
    employee_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO employee_count FROM employees WHERE department = dept_name;
    RETURN employee_count;
END;
' LANGUAGE plpgsql;

• 프로시저 생성 Procedure

CREATE OR REPLACE PROCEDURE update_employee_salary(emp_id INTEGER, new_salary NUMERIC) AS $$
BEGIN
    UPDATE test_employees SET salary = new_salary WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;

• 시퀀스 생성 및 사용 Sequence

CREATE SEQUENCE employee_id_seq;
DROP SEQUENCE IF EXISTS sequence_name;

INSERT INTO employees (id, name, department, salary)
VALUES (nextval('employee_id_seq'), 'New Employee', 'IT', 55000);

• 외래키 Foreign Key

-- Step 1: Add a new column department_id to test_employees
ALTER TABLE test_employees
ADD COLUMN department_id INTEGER;

-- Step 2: Populate the department_id column with the corresponding department IDs
UPDATE test_employees e
SET department_id = d.id
FROM test_departments d
WHERE e.department = d.name;

-- Step 3: Drop the existing department column
ALTER TABLE test_employees
DROP COLUMN department CASCADE;

-- Step 4: Rename the department_id column to department
ALTER TABLE test_employees
RENAME COLUMN department_id TO department;

-- Step 5: Add a foreign key constraint
ALTER TABLE test_employees
ADD CONSTRAINT fk_department_id
FOREIGN KEY (department)
REFERENCES test_departments(id);

INSERT INTO test_employees (name, department, salary) VALUES
    ('Peggy Thomson', 1, 50000),
    ('Barack Obama', 2, 60000),
    ('Tom Cruise', 3, 65000);

• 서브 쿼리 Subquery

SELECT d.name, (SELECT COUNT(*) FROM test_employees) AS total_employees, MAX(e.salary)
FROM test_employees e
JOIN test_departments d ON e.department = d.id
WHERE e.salary > (SELECT AVG(salary) FROM test_employees)
GROUP BY d.name;

조인 Join

• 조인 쿼리

SELECT test_employees.name, test_departments.name
FROM test_employees
INNER JOIN test_departments ON test_employees.department = test_departments.name;

• 셀프 조인

SELECT TA.name, TB.name
FROM test_employees as TA
JOIN test_employees as TB ON TA.name = TB.name;

• 테이블 4개 조인

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    start_date DATE,
    end_date DATE
);

CREATE TABLE project_assignments (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES test_employees(id),
    project_id INTEGER REFERENCES projects(id),
    role VARCHAR(100)
);

INSERT INTO projects (name, start_date, end_date) VALUES
    ('Project A', '2024-01-01', '2024-12-31'),
    ('Project B', '2024-02-01', '2024-11-30'),
    ('Project C', '2024-03-01', '2024-10-31');

INSERT INTO project_assignments (employee_id, project_id, role) VALUES
    (1, 1, 'Project Manager'),
    (2, 1, 'Developer'),
    (3, 2, 'Tester'),
    (1, 3, 'Designer');

SELECT 
    e.name AS employee_name,
    d.name AS department_name,
    p.name AS project_name,
    pa.role AS project_role
FROM 
    test_employees e
JOIN 
    test_departments d ON e.department = d.id
JOIN 
    project_assignments pa ON e.id = pa.id
JOIN 
    projects p ON pa.id = p.id;

• 시간대

SHOW TIMEZONE
# Etc/UTC
SELECT NOW(), TIMEOFDAY(), CURRENT_TIME AS current_time, CURRENT_DATE;

SELECT EXTRACT(year FROM NOW());
SELECT EXTRACT(year FROM TIMEOFDAY());
SELECT EXTRACT(hour FROM CURRENT_TIME);
SELECT EXTRACT(hour FROM CURRENT_DATE);

• 집계 함수 (Count, Sum, Round, Avg, Max, Min)

SELECT COUNT(*) AS total_rows FROM test_employees;
SELECT SUM(salary) AS total_salary FROM test_employees;
SELECT ROUND(AVG(salary), 2) AS average_salary FROM test_employees;
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM test_employees;
SELECT department, COUNT(*) AS employee_count FROM test_employees GROUP BY department;
SELECT COUNT(*) AS high_salary_employees FROM test_employees WHERE salary > 50000;

• EXPLAIN (쿼리 성능 파악)

EXPLAIN SELECT COUNT(*) FROM test_employees WHERE salary >= 50000;
EXPLAIN ANALYZE SELECT COUNT(*) FROM test_employees WHERE salary >= 50000;

• WITH

WITH high_salary_employees AS (
    SELECT * FROM test_employees WHERE salary >= 50000
    );
SELECT COUNT(*) AS high_salary_employee_count FROM high_salary_employees;

• IN / EXIST

SELECT *
FROM test_employees
WHERE department IN (
    SELECT id
    FROM test_departments
    WHERE id = 1 OR id = 2
);

SELECT *
FROM test_employees e
WHERE EXISTS  (
    SELECT 1
    FROM test_departments d
    WHERE d.id = 1 OR d.id = 2
    AND e.department = d.id
);

• 어레이 함수 (데이터로 어레이 저장 가능) Array Function

ALTER TABLE test_employees ADD COLUMN skills VARCHAR(100) ARRAY;
UPDATE test_employees SET skills = ARRAY['SQL', 'Java'] WHERE id = 1;
UPDATE test_employees SET skills = ARRAY['Java', 'Python'] WHERE id = 2;
UPDATE test_employees SET skills = ARRAY['Python', 'JavaScript'] WHERE id = 3;
UPDATE test_employees SET skills = ARRAY['Java', 'AWS'] WHERE id = 4;
UPDATE test_employees SET skills = ARRAY['Java', 'JavaScript'] WHERE id = 5;
UPDATE test_employees SET skills = ARRAY['SQL', 'JavaScript'] WHERE id = 6;
UPDATE test_employees SET skills = ARRAY['SQL', 'Python'] WHERE id = 7;

• JSON 함수JSON Function

ALTER TABLE test_employees ADD COLUMN additional_data JSON;
UPDATE test_employees SET additional_data = '{"address": "111 Main St", "phone": "111-1234"}' WHERE id = 1;
UPDATE test_employees SET additional_data = '{"address": "222 Main St", "phone": "222-1234"}' WHERE id = 2;
UPDATE test_employees SET additional_data = '{"address": "333 Main St", "phone": "333-1234"}' WHERE id = 3;
UPDATE test_employees SET additional_data = '{"address": "444 Main St", "phone": "444-1234"}' WHERE id = 4;
UPDATE test_employees SET additional_data = '{"address": "555 Main St", "phone": "555-1234"}' WHERE id = 5;
UPDATE test_employees SET additional_data = '{"address": "666 Main St", "phone": "666-1234"}' WHERE id = 6;
UPDATE test_employees SET additional_data = '{"address": "777 Main St", "phone": "777-1234"}' WHERE id = 7;

• 전체 텍스트 검색 Full Text Search

# 전체 텍스트 검색을 수행하기 위해 name 열을 텍스트 데이터 유형으로 변환
ALTER TABLE test_employees ALTER COLUMN name TYPE TEXT;

ALTER TABLE test_employees ADD COLUMN name_tsvector TSVECTOR;
UPDATE test_employees SET name_tsvector = to_tsvector(name);
CREATE INDEX idx_name_tsvector ON test_employees USING GIN(name_tsvector);

SELECT *
FROM test_employees
WHERE name_tsvector @@ to_tsquery('John');

View

• 뷰 View

CREATE VIEW high_earners AS
SELECT name, department, salary
FROM test_employees
WHERE salary > 60000;

SELECT * FROM high_earners;

• Materialized View (캐싱 기능)

CREATE MATERIALIZED VIEW mv_employee_avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM test_employees
GROUP BY department;
select * from mv_employee_avg_salary;

• 파티셔닝 Partitioning

-- 새 테이블 생성 및 파티션 정의 추가
CREATE TABLE employees_partitioned (
    id SERIAL PRIMARY KEY,
    name INTEGER,
    department VARCHAR(100),
    salary NUMERIC
) PARTITION BY RANGE (salary);

-- 파티션 테이블 생성
CREATE TABLE employees_partition_low PARTITION OF employees_partitioned
FOR VALUES FROM (0) TO (30000);

CREATE TABLE employees_partition_medium PARTITION OF employees_partitioned
FOR VALUES FROM (30000) TO (60000);

CREATE TABLE employees_partition_high PARTITION OF employees_partitioned
FOR VALUES FROM (60000) TO (MAXVALUE);

-- 기존 테이블 데이터 이동
INSERT INTO employees_partitioned (id, name, department, salary)
SELECT id, name, department, salary FROM employees;

-- 기존 테이블 삭제
DROP TABLE employees;

트랜잭션 Transaction

• MVCC (Multi-Version Concurrency Control)

BEGIN TRANSACTION;
UPDATE test_employees SET salary = salary * 1.1 WHERE department = 4;
COMMIT;

• 행 레벨 락 (Row-Level Locks)

BEGIN TRANSACTION;
LOCK TABLE test_employees IN ROW EXCLUSIVE MODE;
UPDATE test_employees SET salary = salary * 1.1 WHERE department = 4;
COMMIT;

• 명시적 락 명령 (Explicit Lock Commands)

BEGIN;
LOCK TABLE test_employees IN SHARE MODE;
COMMIT;

BEGIN;
UPDATE test_employees SET salary = salary * 2 WHERE department = 4;
COMMIT;

DCL

• 권한 Grant & Revoke

-- SELECT 권한을 부여하는 함수
CREATE FUNCTION grant_select_privilege() RETURNS VOID AS $$
BEGIN
    -- test_user라는 사용자에게 test_employees 테이블에 대한 SELECT 권한을 부여합니다.
    GRANT SELECT ON test_employees TO test_user;
    RAISE NOTICE 'SELECT 권한이 test_user에게 성공적으로 부여되었습니다.';
END;
$$ LANGUAGE plpgsql;

-- SELECT 권한을 취소하는 함수
CREATE FUNCTION revoke_select_privilege() RETURNS VOID AS $$
BEGIN
    -- test_user라는 사용자에게 부여된 test_employees 테이블에 대한 SELECT 권한을 취소합니다.
    REVOKE SELECT ON test_employees FROM test_user;
    RAISE NOTICE 'SELECT 권한이 test_user로부터 성공적으로 취소되었습니다.';
END;
$$ LANGUAGE plpgsql;