Menu
CoddyTech

PostgreSQL 치트시트

마지막 업데이트

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
무작위 UUIDSELECT 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과 OFFSETSELECT * 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 플레이그라운드에 붙여 넣으세요.

PostgreSQL 치트시트 자주 묻는 질문

psql 명령어란 무엇인가요?
psql은 PostgreSQL의 터미널 클라이언트입니다. SQL 외에도 백슬래시로 시작하는 메타 명령어를 이해합니다. \l은 데이터베이스 목록을 보여 주고, \c는 데이터베이스에 접속하며, \dt는 테이블 목록을, \d users는 테이블 구조를 보여 주고, \q는 종료합니다. psql 자체에서 실행되므로 세미콜론이 필요 없고 다른 클라이언트에서는 동작하지 않습니다.
PostgreSQL에서 모든 테이블 목록을 보려면 어떻게 하나요?
psql에서는 \dt(모든 스키마는 \dt *.*)를 입력하세요. 어떤 클라이언트에서든 SQL로는 다음과 같습니다: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
PostgreSQL의 SQL은 MySQL과 다른가요?
핵심(SELECT, JOIN, GROUP BY)은 같습니다. PostgreSQL은 ILIKE, :: 형 변환, RETURNING, DISTINCT ON, ON CONFLICT upsert, 배열과 JSONB를 추가로 제공하며, MySQL이 AUTO_INCREMENT를 쓰는 곳에 identity 열을 씁니다.
JSON과 JSONB의 차이는 무엇인가요?
json은 입력한 텍스트를 그대로 저장하고, jsonb는 파싱한 바이너리 형태로 저장하며 중복 키와 공백을 없애고 인덱스(GIN)와 @>, ? 같은 연산자를 지원합니다. 원본 텍스트가 정확히 필요한 경우가 아니라면 jsonb를 쓰세요.
이 쿼리들을 온라인에서 연습할 수 있나요?
네. PostgreSQL 플레이그라운드는 브라우저에서 실제 PostgreSQL 17 데이터베이스로 어떤 쿼리든 실행하며, 수정할 수 있는 샘플 테이블이 들어 있습니다. CREATE DATABASE나 CREATE ROLE처럼 서버 권한이 필요한 문은 직접 설치한 PostgreSQL이 필요합니다.
이 PostgreSQL 치트시트는 무료인가요?

네. 무료로 읽고 복사하고 인쇄할 수 있으며 회원 가입이 필요 없습니다.

Coddy 프로그래밍 언어 일러스트

Coddy로 PostgreSQL 배우기

시작하기