psql 명령어
메타 명령어는 서버가 아니라 psql(PostgreSQL 터미널 클라이언트) 안에서 실행됩니다. 백슬래시로 시작하며 세미콜론이 필요 없습니다.
| 기능 | 명령어 |
|---|
| 데이터베이스 목록 | \l |
| 다른 데이터베이스에 접속 | \c shop |
| 테이블 목록 | \dt |
| 모든 스키마의 테이블 목록 | \dt *.* |
| 테이블 구조 보기(열, 인덱스, 키) | \d users |
| 스토리지와 주석까지 포함해 보기 | \d+ users |
| 스키마 목록 | \dn |
| 뷰 목록 | \dv |
| 인덱스 목록 | \di |
| 함수 목록 | \df |
| 역할(사용자) 목록 | \du |
| 현재 접속 정보 보기 | \conninfo |
| 파일의 SQL 실행 | \i setup.sql |
| 쿼리 실행 시간 표시 전환 | \timing |
| 확장 출력(한 줄에 열 하나) | \x |
| 마지막 쿼리를 편집기에서 수정 | \e |
| SQL 문법 도움말 | \h CREATE TABLE |
| 모든 메타 명령어 목록 | \? |
| psql 종료 | \q |
터미널에서 접속
| 기능 | 명령어 |
|---|
| 사용자를 지정해 데이터베이스에 접속 | psql -U postgres -d shop |
| 원격 호스트와 포트에 접속 | psql -h db.example.com -p 5432 -U app -d shop |
| 접속 URL로 접속 | psql postgresql://app:secret@localhost:5432/shop |
| 쿼리 하나를 실행하고 종료 | psql -d shop -c "SELECT count(*) FROM users;" |
| SQL 파일 실행 | psql -d shop -f setup.sql |
| 셸에서 데이터베이스 생성 | createdb shop |
| 셸에서 데이터베이스 삭제 | dropdb shop |
데이터베이스와 스키마
| 기능 | SQL |
|---|
| 데이터베이스 생성 | CREATE DATABASE shop; |
| 데이터베이스 삭제 | DROP DATABASE shop; |
| 현재 데이터베이스 보기 | SELECT current_database(); |
| 스키마 생성 | CREATE SCHEMA sales; |
| 스키마 검색 순서 보기 | SHOW search_path; |
| 특정 스키마를 먼저 검색 | SET search_path TO sales, public; |
| 서버 버전 보기 | SELECT version(); |
테이블과 데이터 타입
| 기능 | SQL |
|---|
| identity 키가 있는 테이블 생성 | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| 열 추가 | ALTER TABLE users ADD COLUMN phone text; |
| 열 이름 변경 | ALTER TABLE users RENAME COLUMN country TO country_code; |
| 열 타입 변경 | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| 기본값 설정 | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| 열 삭제 | ALTER TABLE users DROP COLUMN tags; |
| 테이블을 비우고 identity 초기화 | TRUNCATE orders, users RESTART IDENTITY; |
| 테이블이 있으면 삭제 | DROP TABLE IF EXISTS products; |
| 금액용 정확한 소수 | numeric(10,2) |
| 시간대가 포함된 날짜와 시간 | timestamptz |
| 무작위 UUID | SELECT gen_random_uuid(); |
| 열거형(enum) 타입 | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
제약 조건과 키
| 기능 | SQL |
|---|
| 기본 키 추가 | ALTER TABLE logs ADD PRIMARY KEY (id); |
| 자식 행까지 삭제하는 외래 키 | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| CHECK 제약 조건 추가 | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| NULL을 같은 값으로 보는 고유 제약(15 이상) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| 열을 필수로 지정 | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| 제약 조건 삭제 | ALTER TABLE users DROP CONSTRAINT users_email_key; |
조회(PostgreSQL 고유 기능)
표준 SELECT, WHERE, JOIN은 SQL 치트시트에 있습니다. 여기서는 PostgreSQL 고유의 형태를 다룹니다.
| 기능 | SQL |
|---|
| 대소문자를 구분하지 않는 일치 | SELECT * FROM users WHERE name ILIKE 'a%'; |
| 정규 표현식 일치 | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| 그룹별 첫 번째 행 | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| LIMIT과 OFFSET | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| 표준 행 개수 제한 | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| :: 로 형 변환 | SELECT '42'::integer + 1; |
| NULL 대체값 | SELECT name, COALESCE(email, 'none') FROM users; |
| NULL을 같은 값으로 보고 비교 | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| 숫자 시퀀스 생성 | SELECT * FROM generate_series(1, 5); |
| 문자열로 집계 | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| 조건부 집계 | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
삽입, 수정, 삭제와 upsert
| 기능 | SQL |
|---|
| 삽입하고 새 id 돌려받기 | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| 여러 행 삽입 | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| upsert(삽입 또는 수정) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| 삽입하되 중복은 무시 | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| 수정하고 바뀐 행 확인 | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| 다른 테이블 값으로 수정 | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| 삭제하고 삭제된 행 확인 | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| 다른 테이블을 이용해 삭제 | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| 행을 새 테이블로 복사 | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| 기능 | SQL |
|---|
| 필드를 JSON으로 가져오기 | SELECT profile -> 'plan' FROM users; |
| 필드를 텍스트로 가져오기 | SELECT profile ->> 'plan' FROM users; |
| 중첩 필드를 텍스트로 가져오기 | SELECT profile #>> '{address,city}' FROM users; |
| JSON에 값이 포함된 행 | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| 키가 있는 행 | SELECT * FROM users WHERE profile ? 'city'; |
| 키 설정 | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| 객체 병합 | UPDATE users SET profile = profile || '{"beta": true}'; |
| 키 제거 | UPDATE users SET profile = profile - 'city'; |
| 열로 JSON 만들기 | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| 행을 JSON 배열로 | SELECT jsonb_agg(users) FROM users; |
| @> 와 ? 검색용 인덱스 | CREATE INDEX users_profile_idx ON users USING gin (profile); |
배열
| 기능 | SQL |
|---|
| 배열 리터럴 | SELECT ARRAY[1, 2, 3]; |
| 배열에 값이 있는 행 | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| 배열이 지정한 값을 모두 포함하는 행 | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| 요소마다 한 행 | SELECT name, unnest(tags) FROM users; |
| 요소 추가 | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| 배열 길이 | SELECT name, cardinality(tags) FROM users; |
날짜와 시간
| 기능 | SQL |
|---|
| 현재 시각(시간대 포함) | SELECT now(); |
| 오늘 | SELECT current_date; |
| 간격 더하기 | SELECT current_date + INTERVAL '7 days'; |
| 최근 30일의 행 | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| 월 단위로 자르기 | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| 일부 추출 | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| 날짜 차이(일 수) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| 날짜 사이의 기간 | SELECT name, age(current_date, hired_on) FROM employees; |
| 날짜를 텍스트로 서식 지정 | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| 텍스트를 날짜로 변환 | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| 다른 시간대로 변환 | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
문자열 함수
| 기능 | SQL |
|---|
| 연결(NULL이 있으면 NULL) | SELECT name || ' <' || email || '>' FROM users; |
| NULL을 건너뛰고 연결 | SELECT concat_ws(' ', name, email) FROM users; |
| 대문자와 소문자 | SELECT upper(name), lower(name) FROM users; |
| 길이 | SELECT name, length(name) FROM users; |
| 문자열 일부 | SELECT substring(name FROM 1 FOR 2) FROM users; |
| 바꾸기 | SELECT replace(email, '@example.com', '') FROM users; |
| 구분자로 나눠 일부 가져오기 | SELECT split_part(email, '@', 2) FROM users; |
| 정규 표현식으로 바꾸기 | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| 공백 제거 | SELECT trim(' hi '); |
CTE와 윈도우 함수
| 기능 | SQL |
|---|
| WITH로 서브쿼리에 이름 붙이기 | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| 재귀 CTE(5까지 세기) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| 그룹별 행 번호 매기기 | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| 동점을 허용하는 순위 | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| 누적 합계 | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| 이전 행의 값 | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| 행마다 자체 서브쿼리와 조인 | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
인덱스
| 기능 | SQL |
|---|
| B-tree 인덱스(기본값) | CREATE INDEX orders_user_idx ON orders (user_id); |
| 고유 인덱스 | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| 다중 열 인덱스 | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| 부분 인덱스 | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| 표현식 인덱스 | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| 커버링 인덱스(index-only scan) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| 쓰기를 막지 않고 생성 | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| 인덱스 삭제 | DROP INDEX IF EXISTS orders_user_idx; |
| 실행 계획 보기 | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| 실제 소요 시간과 함께 실행 계획 보기 | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
트랜잭션과 잠금
| 기능 | SQL |
|---|
| 시작한 뒤 확정하거나 취소 | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| 트랜잭션 일부 취소 | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| 더 엄격한 격리 수준 | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| 읽은 행 잠그기 | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| 작업 큐: 잠긴 행 건너뛰기 | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
사용자, 역할, 권한
| 기능 | SQL |
|---|
| 로그인 역할 생성 | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| 테이블 읽기 전용 권한 | GRANT SELECT ON users TO app; |
| 스키마의 모든 테이블 읽기 권한 | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| 권한 회수 | REVOKE SELECT ON users FROM app; |
| 비밀번호 변경 | ALTER ROLE app PASSWORD 'new-secret'; |
| 현재 사용자 보기 | SELECT current_user; |
유지 관리와 모니터링
| 기능 | SQL |
|---|
| 실행 중인 쿼리 | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| 쿼리 취소 | SELECT pg_cancel_backend(12345); |
| 접속 종료 | SELECT pg_terminate_backend(12345); |
| 인덱스를 포함한 테이블 크기 | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| 데이터베이스 크기 | SELECT pg_size_pretty(pg_database_size(current_database())); |
| 플래너 통계 갱신 | ANALYZE orders; |
| 죽은 행의 공간 회수 | VACUUM orders; |
| 테이블을 최소 크기로 다시 쓰기(테이블이 잠김) | VACUUM FULL orders; |
| 설정 보기 | SHOW work_mem; |
| 이 세션의 설정 변경 | SET work_mem = '64MB'; |
백업과 복원
PostgreSQL 클라이언트 도구의 셸 명령어입니다.
| 기능 | 명령어 |
|---|
| 데이터베이스를 SQL 파일로 덤프 | pg_dump -U postgres shop > shop.sql |
| 커스텀 형식으로 덤프 | pg_dump -Fc -U postgres shop > shop.dump |
| 테이블 하나 덤프 | pg_dump -t orders shop > orders.sql |
| SQL 덤프 복원 | psql -d shop -f shop.sql |
| 커스텀 형식 덤프 복원 | pg_restore -d shop shop.dump |
| 모든 데이터베이스와 역할 덤프 | pg_dumpall > cluster.sql |
| 쿼리 결과를 CSV로 내보내기(psql에서) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| CSV 가져오기(psql에서) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
자주 쓰는 psql 명령어와 PostgreSQL 문을 복사해 바로 쓰는 표로 정리했습니다. 먼저 접속과 \d 명령어를 다루고, 이어서 테이블, upsert, JSONB, 날짜, 윈도우 함수, 인덱스, 역할, 백업, 유지 관리를 다룹니다.
여기 나오는 SQL은 PostgreSQL 17 문법이며, 대부분은 이전 버전에서도 동작합니다. SQL 문은 PostgreSQL 17에서 확인했습니다. 직접 실행해 보려면 샘플 테이블이 준비된 PostgreSQL 플레이그라운드에 붙여 넣으세요.