For Engineering

[PostgreSQL] DEFERRABLE INITIALLY DEFERRED — 순환 FK를 커밋 시점에 검증한다

설치·접속: PostgreSQL 설치와 접속부제: 두 행이 서로를 FK로 참조(A↔B)해서 어느 쪽을 먼저 INSERT해도 막힐 때-- 즉시 검사되는 일반 FK: 순환 참조를 못 넣는다CREATE TABLE node_immediate ( id integer PRIMARY KEY, name text, partner_id integer REFERENCES node_immediate(id));BEGIN;INSERT INTO node_immediate (id, name, partner_id) VALUES (1, 'A', 2); -- 2가 아직 없음INSERT INTO node_immediate (id, name, partner_id) VALUES (2, 'B', 1);..

[PostgreSQL] ntile(4) / percent_rank() / cume_dist() — 분위수 버킷으로 나누기

설치·접속: PostgreSQL 설치와 접속부제: 학생 성적(또는 고객 결제액)을 사분위(4등분)로 나눠 "상위 25%"를 라벨링할 때SELECT student_id, score, ntile(4) OVER w AS quartile, round(percent_rank() OVER w::numeric, 3) AS pct_rank, round(cume_dist() OVER w::numeric, 3) AS cume_distFROM scoresWINDOW w AS (ORDER BY score)ORDER BY score; student_id | score | quartile | pct_rank | cume_dist ----..

[PostgreSQL] multirange — 불연속 기간을 한 값으로 다룬다

설치·접속: PostgreSQL 설치와 접속부제: 강사의 주간 가용 시간이 오전·오후로 쪼개져 있는데, 예약 요청이 그 안에 통째로 들어가는지 한 번에 판정할 때-- 겹치지 않는 여러 range 를 하나의 값으로: int4multirangeSELECT '{[9,12), [14,18)}'::int4multirange AS availability;-- 예약 구간이 가용시간 안에 완전히 들어가는가 (@>)SELECT '{[9,12), [14,18)}'::int4multirange @> '[10,11)'::int4range AS ok_morning, '{[9,12), [14,18)}'::int4multirange @> '[11,15)'::int4range AS spans_gap, '{[9,..

[PostgreSQL] range 반열림 구간 [) — 인접 기간이 안 겹치는 이유

설치·접속: PostgreSQL 설치와 접속부제: 구독 기간을 [시작, 종료) 로 저장했더니 종료일과 다음 구독 시작일이 딱 붙어도 겹침 판정이 안 나야 할 때-- range 는 무조건 반열림 [) 표준형으로 정규화된다SELECT '[3,7]'::int4range AS closed, -- 7 포함 -> [3,8) '[3,7)'::int4range AS half, -- [3,7) '(3,7)'::int4range AS open; -- 3 제외 -> [4,7)-- 인접 구간은 겹치지 않는다 (경계값 공유해도)SELECT int4range(10,20) && int4range(20,30) AS overlap, -- 겹침? int4range(10,20) -..

[PostgreSQL] "배열은 집합이 아니다" — 언제 배열, 언제 조인 테이블

설치·접속: PostgreSQL 설치와 접속부제: 게시글 태그를 tags int[] 로 넣을지 article_tag 조인 테이블로 뺄지, 공식 문서 기준으로 판단할 때공식 배열 문서에는 이런 경고가 박혀 있다.Arrays are not sets; searching for specific array elements can be a sign of database misdesign.(배열은 집합이 아니다. 특정 원소를 검색하는 일이 잦다면 설계가 잘못됐다는 신호일 수 있다.)즉 원소 검색이 잦으면 정규화(별도 행 테이블)를 권한다. 배열은 "한 행에 딸린 순서 있는 값 묶음"을 통째로 다룰 때 좋고, "이 원소를 가진 행 찾기"가 핵심 질의라면 관계형으로 펼치는 게 정석이다. 다만 실측해 보면 배열 + 인덱스..

[PostgreSQL] array_position 으로 상태값을 커스텀 순서로 정렬한다

설치·접속: PostgreSQL 설치와 접속부제: 티켓 상태를 '접수 → 처리중 → 완료' 순으로 보여줘야 하는데 ORDER BY status 는 사전순으로 흩어질 때CREATE TABLE ticket (id int, title text, status text);-- '접수' '처리중' '완료' 가 섞여 들어있다-- 사전순(원치 않음): '완료'가 맨 앞SELECT id, status FROM ticket ORDER BY status, id;-- array_position 으로 커스텀 순서: 배열에서의 위치를 정렬 키로SELECT id, status, array_position(ARRAY['접수','처리중','완료'], status) AS ordFROM ticketORDER BY array_p..

[PostgreSQL] 배열 검색 = ANY / && / @> 와 GIN 인덱스

설치·접속: PostgreSQL 설치와 접속부제: 게시글 tags text[] 컬럼에서 '리눅스'나 'DB' 태그가 붙은 글을 조인 테이블 없이 빠르게 찾을 때CREATE TABLE post (id int PRIMARY KEY, title text, tags text[]);-- ... 5000행 적재, id ARRAY['리눅스','DB'];-- 단일 원소: '리눅스'가 배열 안에 있나SELECT count(*) FROM post WHERE '리눅스' = ANY(tags);EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)SELECT id FROM post WHERE tags && ARRAY['리눅스','DB'];-- && (겹침) 결과 count -------..

[PostgreSQL] LATERAL 조인 — 행마다 상관 서브쿼리를 조인처럼 펼친다

설치·접속: PostgreSQL 설치와 접속부제: "상품마다 최근 주문 2건", "카테고리별 최신 N개"처럼 바깥 행 값을 참조하는 Top-N을 조인 한 번으로 뽑을 때-- 상품마다, 그 상품의 최근 주문 2건씩 (per-row Top-N)SELECT p.id, p.name, o.ord_id, o.qtyFROM products pCROSS JOIN LATERAL ( SELECT id AS ord_id, qty FROM orders WHERE product_id = p.id -- ← 바깥 행 p를 참조 ORDER BY id DESC LIMIT 2) oWHERE p.id id | name | ord_id | qty----+-------+--------+----- 1 ..

[PostgreSQL] 집계 안의 ORDER BY — string_agg/array_agg 결과 순서를 고정한다

설치·접속: PostgreSQL 설치와 접속부제: 주문별 상품명을 콤마 리스트로 뽑는 리포트가 실행할 때마다 순서가 바뀌어 diff가 지저분할 때-- 정렬 없는 string_agg: 입력 순서에 좌우돼 재현이 보장되지 않는다SELECT order_id, string_agg(product, ', ') AS itemsFROM lines GROUP BY order_id ORDER BY order_id; order_id | items----------+--------------------------------- 1 | Keyboard, Mouse, Monitor, Cable 2 | Desk, Chair(2 rows)-- 집계 함수 괄호 안에 ORDER BY를 넣어..

[PostgreSQL] 윈도우 last_value 프레임 함정 — "파티션 최종값"이 자기 값으로 나올 때

설치·접속: PostgreSQL 설치와 접속부제: 부서별 최고 급여를 last_value로 뽑았더니 매 행이 자기 급여를 반환할 때-- 부서별 급여를 오름차순 정렬하고, 그 파티션의 "마지막(=최고) 급여"를 붙이려는 의도SELECT dept, emp, salary, last_value(salary) OVER (PARTITION BY dept ORDER BY salary) AS wrong_lastFROM sal ORDER BY dept, salary; dept | emp | salary | wrong_last-------+-----+--------+------------ dev | Fay | 4800 | 4800 dev | Dan | 5500 | 5500 ..