실행하며 배우는 PostgreSQL 튜토리얼
페이지에서 바로 실행되는 쿼리로 배우는 무료 PostgreSQL 튜토리얼입니다. 데이터 타입, CREATE TABLE, upsert, JSONB, 날짜, CTE, 윈도 함수, 인덱스와 VACUUM을 다룹니다. 쿼리를 고치고 다시 실행해 보세요.
코스 시작하기시작하기
- PostgreSQL vs MySQLPostgreSQL과 MySQL은 둘 다 무료 관계형 데이터베이스입니다. PostgreSQL은 SQL 기능, JSONB, 트랜잭션 안의 DDL이 더 풍부하고, MySQL은 이미 기본으로 쓰는 환경에서 운영하기 더 간단합니다.
- PostgreSQL vs SQL ServerPostgreSQL은 무료 오픈 소스이고, SQL Server는 T-SQL과 Windows 중심 도구를 갖춘 Microsoft의 상용 데이터베이스입니다. SQL, JSON, 잠금, 라이선스가 어떻게 다른지 정리했습니다.
- PostgreSQL vs MongoDBPostgreSQL은 고정된 스키마의 테이블에 행을 저장하고 jsonb 컬럼에 JSON 문서도 담을 수 있습니다. MongoDB는 스키마가 유연한 문서를 컬렉션에 저장하고 여러 서버에 샤딩합니다.
- 데이터베이스와 테이블 목록psql에서 \l은 데이터베이스 목록을, \c는 데이터베이스 전환을, \dt는 테이블 목록을 보여 줍니다. SQL로는 pg_database, information_schema.tables, information_schema.columns를 조회합니다.
테이블과 타입
- 데이터 타입거의 모든 컬럼에 쓰게 될 PostgreSQL 데이터 타입입니다. 정수, numeric, text, boolean, timestamptz, uuid, jsonb, 배열, enum을 언제 고르는지 함께 정리했습니다.
- CREATE TABLECREATE TABLE은 테이블의 컬럼, 타입, 제약 조건을 정의합니다. identity 키, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS, LIKE를 다룹니다.
- ALTER TABLEALTER TABLE은 이미 있는 테이블을 바꿉니다. 컬럼 추가, 이름 변경, 삭제, USING을 쓴 타입 변경, 기본값과 NOT NULL 설정, 제약 조건 추가를 다룹니다.
- 기본 키PostgreSQL에서 기본 키를 정의하고 생성하는 방법입니다. identity 컬럼, 예전 방식인 SERIAL, gen_random_uuid()를 쓰는 UUID, 복합 키와 시퀀스 재설정까지 다룹니다.
- 외래 키외래 키는 컬럼이 다른 테이블에 있는 값만 받도록 합니다. REFERENCES, ON DELETE CASCADE, SET NULL, RESTRICT, 기존 테이블에 키 추가하기, 인덱스 만들기를 다룹니다.
- Enum 타입enum 타입은 CREATE TYPE ... AS ENUM으로 만드는, 순서가 있는 고정된 허용 값 목록입니다. 값 추가와 이름 변경, 목록 조회, 정렬, 그리고 CHECK 제약 조건이 더 나은 경우를 다룹니다.
- 타임스탬프timestamptz는 시점을 저장하고 세션 타임존으로 보여 주며, timestamp는 타임존 없는 벽시계 시간을 저장합니다. 둘 다 입력한 타임존을 저장하지 않습니다. 거의 모든 경우에 timestamptz를 쓰세요.
쿼리
- 조인조인은 조건에 따라 두 테이블의 행을 합칩니다. INNER JOIN은 일치하는 행만, LEFT JOIN은 첫 테이블의 모든 행을, FULL JOIN은 양쪽 모두를 남기고, CROSS JOIN은 모든 조합을 만들며, LATERAL은 행마다 서브쿼리를 실행합니다.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort는 고객별 최신 주문처럼 각 그룹의 첫 행을 남깁니다. PostgreSQL에만 있는 기능입니다.
- COALESCE, NULLIF, CASECOALESCE(a, b, ...)는 NULL이 아닌 첫 번째 인수를 돌려주고, NULLIF(a, b)는 값을 NULL로 바꾸며, CASE WHEN은 조건에 따라 값을 고릅니다.
- LIKE, ILIKE, 정규식LIKE는 %와 _를 쓴 패턴으로 텍스트를 비교하고, ILIKE는 대소문자를 무시하고 같은 일을 하며, ~ 연산자는 POSIX 정규식으로 비교합니다.
- CAST와 ::PostgreSQL에서 CAST(value AS type)와 value::type은 값을 다른 타입으로 변환합니다. 둘은 같은 변환이며, ::는 PostgreSQL의 짧은 문법입니다.
데이터 변경
- Upsert (ON CONFLICT)PostgreSQL은 INSERT ... ON CONFLICT로 upsert합니다. EXCLUDED를 쓰는 DO UPDATE, DO NOTHING, 조건부 업데이트, 카운터, 추가된 행과 업데이트된 행 구분하기를 다룹니다.
- RETURNINGRETURNING을 쓰면 INSERT, UPDATE, DELETE, MERGE가 바꾼 행을 돌려받습니다. 생성된 id, 새 값, 삭제된 행, 그리고 MERGE가 실행한 동작까지 확인할 수 있습니다.
- 트랜잭션PostgreSQL 트랜잭션의 동작 방식: BEGIN, COMMIT, ROLLBACK, 세이브포인트, 열린 트랜잭션에서 오류가 나면 생기는 일, 그리고 격리 수준을 정리합니다.
JSON과 배열
- JSONBPostgreSQL에서 jsonb로 JSON을 저장하고 조회하는 방법: -> 와 ->> 연산자, @> 포함 검사, jsonb_set 등의 수정, 배열을 행으로 바꾸기, GIN 인덱스, json과 jsonb의 차이를 다룹니다.
- 배열PostgreSQL 배열 컬럼: 선언하는 법, 1부터 시작하는 인덱스, ANY, @>, && 로 배열에 값이 있는지 확인하기, unnest, 그리고 꼭 필요한 배열 함수를 다룹니다.
- string_agg와 array_aggstring_agg는 그룹의 값을 문자열 하나로 합치고, array_agg는 배열로 모읍니다. 정렬, DISTINCT, FILTER, NULL 처리, 그리고 자주 만나는 오류를 정리합니다.
문자열과 날짜
- 문자열 함수매일 쓰는 PostgreSQL 문자열 함수: substring, left와 right, ||와 concat, split_part, position, replace, regexp_replace, trim, lpad, format을 정리합니다.
- 날짜 함수PostgreSQL에서 날짜 다루기: now()와 current_date, interval 더하고 빼기, 그룹화를 위한 date_trunc, extract와 age, to_char로 서식 지정하기를 정리합니다.
- generate_seriesgenerate_series는 시작 값부터 끝 값까지의 행 집합을 돌려줍니다. 숫자, 날짜, 타임스탬프 모두 됩니다. 보고서의 빈 구간 채우기, 테스트 데이터 만들기, 달력 만들기에 씁니다.
고급 쿼리
성능과 유지보수
- 인덱스CREATE INDEX는 기본으로 B-tree 인덱스를 만들어, PostgreSQL이 테이블 전체를 읽지 않고 행을 찾게 합니다. 다른 종류(GIN, GiST, BRIN, hash)는 JSONB, 배열, 전문 검색, 범위, 아주 큰 테이블을 다룹니다.
- EXPLAIN ANALYZEEXPLAIN은 PostgreSQL이 쿼리에 고른 실행 계획을 보여 주고, EXPLAIN ANALYZE는 쿼리를 실제로 실행해 단계마다 실제 행 수와 시간을 더합니다. 실행 계획 트리와 자주 보는 노드 이름을 읽는 법을 알아봅니다.
- 뷰와 구체화된 뷰뷰는 읽을 때마다 실행되는 저장된 쿼리입니다. 구체화된 뷰는 쿼리 결과를 저장하고, REFRESH MATERIALIZED VIEW를 실행할 때만 바뀝니다.
- VACUUMVACUUM은 UPDATE와 DELETE가 남긴 죽은 행 버전을 지워 그 공간을 다시 쓰게 합니다. autovacuum이 이를 대신 실행합니다. 언제 실행되는지, 어떻게 조정하는지, VACUUM FULL의 잠금을 감수할 만한 때는 언제인지 알아봅니다.