For Engineering

[PostgreSQL] seg — 오차 범위째 저장하는 측정값 구간 타입

설치·접속: PostgreSQL 설치와 접속부제: 실험실 pH 계측값을 6.25~6.50처럼 오차 구간으로 저장하고, "이 구간과 겹치는 샘플"을 인덱스로 찾을 때CREATE EXTENSION IF NOT EXISTS seg;CREATE TABLE meas (id serial primary key, sample text, ph seg);INSERT INTO meas(sample, ph) VALUES ('A', '6.25 .. 6.50'), ('B', '5(+-)0.3'), ('C', '6.4 .. 6.9'), ('D', '7.0 .. 7.2');SELECT sample, ph FROM meas ORDER BY id; sample | ph--------+-------------- A ..

[PostgreSQL] citext — 대소문자 무시 타입으로 이메일 UNIQUE를 건다

설치·접속: PostgreSQL 설치와 접속부제: 회원가입에서 'Tom@X.com'과 'tom@x.com'을 같은 계정으로 막고 싶은데 lower(email) 함수 인덱스 도배가 지겨울 때CREATE EXTENSION IF NOT EXISTS citext;CREATE TABLE acct (id serial primary key, email citext UNIQUE);INSERT INTO acct(email) VALUES ('Tom@X.com');-- 대소문자만 다른 값으로 재가입 시도INSERT INTO acct(email) VALUES ('tom@x.com');ERROR: duplicate key value violates unique constraint "acct_email_key"DETAIL: ..

[PostgreSQL] isn — 잘못된 ISBN을 DB단에서 원천 차단한다

설치·접속: PostgreSQL 설치와 접속부제: 도서 카탈로그에 ISBN을 varchar로 받다가 체크섬 틀린 값이 줄줄이 들어와, 검증을 앱이 아니라 DB에서 걸고 싶을 때CREATE EXTENSION IF NOT EXISTS isn;CREATE TABLE books (id serial primary key, code isbn13);INSERT INTO books(code) VALUES ('978-0-306-40615-7');SELECT code FROM books; code------------------- 978-0-306-40615-7(1 row)isbn13 타입은 입력 시 체크섬(마지막 자리 check digit)을 자동 검증하고, 출력 시 규격에 맞는 하이픈을 붙여준다. 하이픈 없..

[PostgreSQL] jsonb_set — 문서 전체 덮어쓰기 없이 JSONB 한 필드만 갱신한다

설치·접속: PostgreSQL 설치와 접속부제: 사용자 설정 JSONB에서 notifications.email 플래그 하나만 토글하고 싶은데, 문서 전체를 앱에서 읽고 다시 쓰는(read-modify-write) 방식은 그사이 다른 갱신을 덮어쓸 위험이 있을 때jsonb_set 은 경로를 지정해 문서의 그 부분만 갱신한다. 앱이 JSONB를 통째로 읽어 수정하고 다시 UPDATE하는 대신, UPDATE ... SET prefs = jsonb_set(prefs, '{경로}', 새값) 한 문장으로 끝낸다. 나머지 필드는 DB 안에서 그대로 보존되므로 동시 갱신이 서로를 덮어쓸 창이 좁아진다.CREATE TABLE settings (user_id int primary key, prefs jsonb);INSE..

[PostgreSQL] jsonb_to_recordset — JSON 객체 배열을 관계형 행으로 펼쳐 집계한다

설치·접속: PostgreSQL 설치와 접속부제: 외부 API 응답을 통째로 JSONB에 저장했는데, 그 안의 주문 라인 배열을 ETL이나 임시 테이블 없이 그 자리에서 행으로 펼쳐 GROUP BY 하고 싶을 때jsonb_to_recordset 은 JSON 객체 배열을 타입 지정 SQL 행 집합으로 바꾼다. AS x(col type) 로 스키마를 즉석에서 붙이면, JSONB 안에 갇혀 있던 배열이 일반 테이블처럼 조인·집계 대상이 된다. 컬럼 정의 AS x(...) 는 필수다(빼면 에러).CREATE TABLE api (id int primary key, resp jsonb);INSERT INTO api VALUES (1, '{"order":"o1","lines":[{"cat":"book","qty":2..

[PostgreSQL] jsonpath 필터 — JSONB 내부에서 부등호·범위·정규식으로 검색한다

설치·접속: PostgreSQL 설치와 접속부제: 이벤트 로그 JSONB payload에서 items 중 amount가 100 이상인 항목만 뽑아야 하는데, @> 포함 연산으로는 등호밖에 안 돼서 못 할 때@> 는 "이 값을 포함하느냐"는 등호 매칭만 된다. 부등호·범위·정규식은 SQL/JSON path로 간다. @? 는 경로 조건을 만족하는 요소가 하나라도 있는지 판정하고, jsonb_path_query 는 조건에 맞는 요소만 집합으로 꺼낸다. $min/$max 변수 바인딩까지 된다.CREATE TABLE events (id int primary key, payload jsonb);INSERT INTO events VALUES (1, '{"user":"alice","items":[{"sku":"A1",..

[PostgreSQL] DISTINCT ON — 고객별 대표 1행만 뽑기

설치·접속: PostgreSQL 설치와 접속부제: 고객별로 "가장 최근 주문 1건"만 골라 대시보드에 노출할 때SELECT DISTINCT ON (user_id) user_id, ordered_at, totalFROM ordersORDER BY user_id, ordered_at DESC; user_id | ordered_at | total ---------+---------------------+-------- 1 | 2026-02-10 20:00:00 | 224.94 2 | 2026-02-17 13:00:00 | 353.75 3 | 2026-02-18 03:00:00 | 149.96 4 | 2026-02-19 00:00:00 | 176.91..

[PostgreSQL] primary가 죽었다 — standby를 pg_promote로 승격한다

설치·접속: PostgreSQL 설치와 접속부제: primary 서버가 응답이 없고 애플리케이션이 전부 쓰기 실패를 뱉을 때, 대기 중이던 standby를 새 primary로 올려 서비스를 되살릴 때-- 승격 직전: standby는 read-only 복구 모드다SELECT pg_is_in_recovery(); -- tSELECT timeline_id FROM pg_control_checkpoint(); -- 1-- primary가 죽었다. standby를 새 primary로 올린다.SELECT pg_promote();-- 승격 후: 복구 모드 해제 + 타임라인 증가 + 쓰기 가능SELECT pg_is_in_recovery(); -- fCHECKPOINT;SELECT t..

[PostgreSQL] 동기 복제로 커밋 무손실을 보장한다 — standby가 받았다고 확인해야 커밋 완료

설치·접속: PostgreSQL 설치와 접속부제: 결제 승인·정산 원장처럼 한 건이라도 잃으면 안 되는 트랜잭션인데, 기본 스트리밍 복제는 비동기라 primary가 죽으면 아직 안 넘어간 커밋이 사라질 때-- 처음엔 비동기(기본값). standby가 받기 전에 primary가 죽으면 그 커밋은 유실될 수 있다.SELECT application_name, state, sync_state FROM pg_stat_replication;-- standby가 WAL을 받았다고 확인해야만 COMMIT을 반환하도록 바꾼다ALTER SYSTEM SET synchronous_standby_names = '*';SELECT pg_reload_conf();SELECT application_name, state, sync_s..

[PostgreSQL] 논리 복제(publication/subscription) — 리포팅 서버에 주문 테이블만 실시간으로 넘긴다

설치·접속: PostgreSQL 설치와 접속부제: 물리 스트리밍 복제는 클러스터를 통째로 복제하지만, 리포팅 전용 서버엔 주문 테이블 하나만 실시간으로 흘리고 싶을 때-- [발행 서버] wal_level=logical 인 상태에서, 넘길 테이블만 골라 발행한다CREATE PUBLICATION pub_report FOR TABLE orders, products;-- [구독 서버] 같은 스키마의 테이블을 만들어 두고 구독을 건다CREATE TABLE orders (id int PRIMARY KEY, customer text, amount numeric);CREATE TABLE products (id int PRIMARY KEY, name text, price numeric);CREATE SUBSCRIPT..