[PostgreSQL] JSONB 변환 오류 및 CSV 인코딩 깨짐 해결

 

[PostgreSQL] JSONB 변환 오류 및 CSV 인코딩 깨짐 해결 사례

🧩 오류 개요

PostgreSQL 환경에서 JSON 데이터를 처리하던 중 다음과 같은 오류가 발생했습니다.

ERROR: function jsonb_array_elements(json) does not exist
HINT: You might need to add explicit type casts.

또한 CSV 파일을 Windows 환경에서 열었을 때, 한글이 깨지는 현상도 함께 보고되었습니다.

⚙️ 원인 분석

• JSONB 변환 오류 — 함수 jsonb_array_elements() 는 jsonb 타입만 지원하므로, json 타입 데이터에 대해 명시적 캐스팅이 필요합니다.
• CSV 한글 깨짐 — PostgreSQL의 기본 인코딩(UTF-8)으로 저장된 CSV 파일을 Windows Excel이 CP949(EUC-KR)로 열기 때문에 발생하는 인코딩 불일치 현상입니다.

🧠 해결 방법

1️⃣ JSONB 캐스팅 적용

모든 JSON 컬럼 접근 시 명시적으로 ::jsonb 캐스팅을 추가합니다.

-- 예시 SQL
FROM jsonb_array_elements((target_data::jsonb)->'dataSystemAccnts'->'dataSystemAccnt') AS acc,
     jsonb_array_elements(acc->'accntInfos') AS ai

이후 LEFT JOIN LATERAL 구문을 사용해 하위 스코프의 acc를 참조하도록 수정했습니다.

LEFT JOIN LATERAL (
    SELECT name, login_id FROM config.max_user mu
    WHERE mu.id::text = acc->>'userId'
    LIMIT 1
) mu ON TRUE

이를 통해 column "acc" does not exist 오류가 제거되었습니다.

2️⃣ CSV 인코딩 문제 해결

CSV 추출 후 UTF-8 BOM(Byte Order Mark)을 추가하여 Windows Excel에서 한글이 정상 표시되도록 개선했습니다.

# UTF-8 BOM 추가 예시
echo -ne '\xEF\xBB\xBF' > temp.csv
cat user_account.csv >> temp.csv
mv temp.csv user_account.csv

이 방식은 PostgreSQL 인코딩 설정과 관계없이 모든 OS에서 안정적으로 동작합니다.

💻 스크립트 구조

전체 동작은 Bash 스크립트로 자동화되며, 주요 흐름은 아래와 같습니다.

1. 조회 기간 입력 (YYYY-MM-DD)
2. UNIX 타임 변환 → start_unix, end_unix 계산
3. log.user_account 뷰 생성 (category=315)
4. CSV 추출 및 UTF-8 BOM 추가
5. 결과 요약 로그 기록

뷰 내부에서는 JSON 데이터를 가공하여 userId, accnt, startTime, expiryTime 필드만 추출합니다.

before_data / after_data 결과 예시:
[
  {
    "user": "홍길동 (hong123)",
    "accnt": "SYS",
    "startTime": "2025-10-20",
    "expiryTime": "2025-12-31"
  }
]

✅ 결과 및 검증

• JSON → JSONB 캐스팅 후 모든 함수 정상 동작
• CSV 인코딩 문제 해결 (Excel에서 한글 100%)
• 자동 로그 누적 기능 정상 작동

💡 Tip: PostgreSQL의 JSON 함수를 사용할 때는 데이터 타입을 반드시 확인하고, 필요 시 ::jsonb 캐스팅을 통해 일관성 있는 구조를 유지하는 것이 좋습니다.

🔍 결론

이번 사례는 PostgreSQL의 JSON 타입 처리와 OS 간 인코딩 차이에서 비롯된 문제를 모두 해결한 예시입니다. 실제 운영 환경에서는 CSV 추출 시 UTF-8 BOM 추가를 기본 옵션으로 적용하는 것이 가장 안전하며, JSON 구조의 일관성을 유지하기 위해 jsonb 캐스팅을 권장합니다.

반응형