CTE(공통 테이블 식)는 메인 문장 앞의 WITH 절에 둔, 이름이 붙은 서브쿼리입니다: WITH name AS (SELECT ...) SELECT ... FROM name. 메인 쿼리는 CTE를 테이블처럼 읽으므로, 중첩된 서브쿼리가 위에서 아래로 읽히는 단계가 됩니다.
CTE totals가 고객별 주문 합계를 구하고, 메인 쿼리는 100을 넘는 고객만 남깁니다. 300인 cy와 180인 ana입니다. CTE는 자신이 속한 문장 안에서만 존재합니다.
여러 CTE 연결하기
CTE는 쉼표로 구분합니다. 각 CTE는 앞에서 정의한 CTE를 읽을 수 있습니다.
average는 totals를 읽고, 메인 쿼리는 둘 다 읽습니다. 고객 합계는 180, 70, 300이고 평균은 약 183이므로, 평균을 넘는 것은 cy뿐입니다.
WITH RECURSIVE로 트리 탐색하기
WITH RECURSIVE를 쓰면 CTE가 자기 자신을 참조할 수 있습니다. UNION ALL로 이어진 두 부분으로 이루어집니다:
- 시작 행을 돌려주는 앵커 쿼리
- CTE를 테이블과 조인해 다음 단계를 찾는 재귀 쿼리
PostgreSQL은 이전 단계가 만든 행에 재귀 부분을 실행하고, 어떤 단계가 행을 돌려주지 않으면 멈춥니다.
각 단계는 이전 단계의 행에 보고하는 사람을 찾습니다. Mia는 깊이 3, 경로 Rosa > Omar > Ivo > Mia가 됩니다. 트리 전체 대신 한 관리자 아래의 모든 사람을 보려면 앵커의 WHERE를 id = 2로 바꾸세요.
직원에서 맨 위로 거슬러 올라가려면 조인 조건을 s.id = c.manager_id로 바꿉니다.
끝나지 않는 재귀 CTE 멈추기
재귀 CTE는 어떤 단계가 아무것도 돌려주지 않을 때만 끝납니다. 데이터가 순환하면(A가 B에게, B가 A에게 보고) 끝나지 않습니다. WHERE c.depth < 20 같은 깊이 제한을 두거나, 처음 반복된 행을 표시하고 더 따라가지 않는 CYCLE 절(PostgreSQL 14+)을 쓰세요:
WITH RECURSIVE chain AS (
SELECT id, manager_id FROM staff WHERE id = 5
UNION ALL
SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;
재귀 CTE로 행 만들기
재귀 부분에 테이블이 꼭 필요하지는 않습니다. 다음은 각 행의 값을 다시 넣어 1부터 5까지 셉니다:
단순한 숫자나 날짜 시리즈라면 generate_series가 더 짧습니다.
데이터를 바꾸는 CTE로 행 옮기기
CTE는 RETURNING 절을 가진 INSERT, UPDATE, DELETE가 될 수 있고, 메인 문장은 반환된 행을 읽습니다. 다음은 한 문장으로 오래된 주문을 보관 테이블로 옮깁니다:
주문 1과 3은 함께 삭제되고 보관 테이블에 삽입되므로, 실패하면 두 테이블 모두 원래대로 남습니다. 문장의 모든 부분은 같은 스냅샷을 봅니다. 메인 쿼리는 DELETE가 orders에 준 영향은 보지 못하고, RETURNING이 넘겨준 것만 봅니다(RETURNING 참고).
MATERIALIZED로 인라인 제어하기
PostgreSQL 12부터 부수 효과가 없고 메인 쿼리가 한 번만 참조하는 CTE는 인라인됩니다. 플래너가 바깥 쿼리와 함께 최적화하므로, CTE 밖에 쓴 WHERE도 인덱스를 쓸 수 있습니다. 두 번 이상 참조되는 CTE는 한 번 계산되고 그 결과가 재사용됩니다. 어느 쪽이든 강제할 수 있습니다:
WITH recent AS MATERIALIZED (
SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;
WITH recent AS NOT MATERIALIZED (...)
MATERIALIZED는 CTE를 따로 한 번 계산하고, 바깥 WHERE를 그 안에 넣지 않습니다. 인라인된 실행 계획이 더 느릴 때 쓰세요. NOT MATERIALIZED는 CTE가 여러 번 참조되더라도 인라인을 요청합니다. 재귀 CTE와 데이터를 바꾸는 CTE는 항상 따로 계산됩니다.
자주 묻는 질문
PostgreSQL에서 CTE가 서브쿼리보다 빠른가요?
대개 어느 쪽도 더 빠르지 않습니다. PostgreSQL 12부터는 한 번만 참조되고 부수 효과가 없는 CTE가 메인 쿼리에 인라인되므로, 같은 내용의 서브쿼리와 같은 실행 계획을 받습니다. 12 이전에는 모든 CTE가 따로 계산되어 인덱스를 못 쓰게 되기도 했습니다.
UPDATE나 DELETE에서도 CTE를 쓸 수 있나요?
네. WITH는 INSERT, UPDATE, DELETE, MERGE 앞에 올 수 있고, CTE 자체가 RETURNING을 가진 INSERT, UPDATE, DELETE가 될 수도 있습니다. 메인 문장은 그 반환된 행을 읽습니다.
재귀 CTE의 무한 루프는 어떻게 막나요?
재귀 부분에 언젠가는 행을 돌려주지 않게 되는 조건을 두세요. 예를 들어 깊이 제한(WHERE depth < 10)입니다. 자기 자신으로 되돌아올 수 있는 데이터라면 CYCLE 절(PostgreSQL 14+)이 반복된 행을 표시하고 거기서 멈춥니다.
CTE를 여러 쿼리에서 쓸 수 있나요?
아니요. CTE는 자신이 쓰인 문장 안에서만 존재합니다. 여러 문장에서 쿼리를 재사용하려면 뷰나 임시 테이블을 만드세요.