[PostgreSQL] 기존 데이터를 참조하여 INSERT

PostgreSQL: 기존 데이터를 참조하여 INSERT 문 작성하기

이번 포스팅에서는 PostgreSQL에서 기존 데이터를 참조해 새로운 데이터를 삽입하는 INSERT 문을 작성하는 방법을 알아보겠습니다. 특히 JSON 형식 데이터를 다룰 때 특정 값을 변환하여 새 컬럼에 저장하는 방법을 중심으로 설명합니다.

문제 상황

• 테이블에 JSON 형식의 데이터를 가진 컬럼이 있습니다.
• JSON 데이터 중 intervals 키의 첫 번째 배열 값을 가져온 뒤, 문자열을 / 기준으로 나누어 두 번째 값을 새로운 컬럼에 삽입하려고 합니다.

예제 테이블 구조

테이블 이름: example_table

컬럼 이름	데이터 타입	설명
id	INTEGER	기본 키
data	JSON	JSON 형식 데이터
new_column	TEXT	새로운 데이터 컬럼

SQL 문 작성

INSERT INTO example_table (id, data, new_column)
SELECT
    id, 
    data,
    split_part((data::json->'intervals'->>0)::text, '/', 2) AS new_column
FROM example_table
WHERE 조건;

SQL 문 설명

1. data::json->'intervals'->>0
   • data 컬럼을 JSON으로 변환하고, intervals 키의 첫 번째 배열 값을 가져옵니다.
   • -> 연산자는 JSON 객체를 반환하고, ->> 연산자는 텍스트 값을 반환합니다.
2. split_part(..., '/', 2)
   • PostgreSQL의 split_part 함수를 사용해 문자열을 / 기준으로 나누고, 두 번째 값을 가져옵니다.
   • 예를 들어, "2024/01/01" 문자열에서 split_part를 적용하면 01이 추출됩니다.
3. INSERT INTO ... SELECT
   • 기존 데이터를 참조하여 새 데이터를 삽입하는 방식입니다.
   • SELECT 문으로 데이터를 가공한 결과를 INSERT를 통해 새로운 행으로 삽입합니다.
4. WHERE 조건
   • 데이터 삽입 대상 행을 필터링하기 위해 WHERE 조건을 사용할 수 있습니다.
   • 예를 들어, 이미 처리된 데이터나 특정 조건을 만족하지 않는 데이터를 제외할 수 있습니다.

예제

기존 데이터

id	data
1	{"intervals": ["2024/01/01", "2024/12/31"], "other_key": "value"}

실행 결과

id	data	new_column
1	{"intervals": ["2024/01/01", "2024/12/31"], "other_key": "value"}	01

주의사항

1. JSON 배열 인덱스
   • PostgreSQL에서 JSON 배열은 0부터 시작합니다. 따라서 intervals 키의 첫 번째 배열 요소를 가져오려면 ->>0을 사용해야 합니다.
2. 데이터 검증
   • 데이터가 JSON 형식이며, 배열이 존재하는지 검증하려면 아래와 같은 조건을 추가할 수 있습니다.

     WHERE jsonb_typeof(data::json->'intervals') = 'array'

3. 데이터가 없을 경우 처리
   • JSON 배열이 비어 있거나 / 문자가 없는 경우, split_part 함수는 빈 문자열을 반환합니다. 이러한 경우를 처리하려면 CASE 문을 활용해 기본값을 설정할 수 있습니다.

     CASE 
         WHEN split_part((data::json->'intervals'->>0)::text, '/', 2) = '' THEN 'default_value'
         ELSE split_part((data::json->'intervals'->>0)::text, '/', 2)
     END

4. 테이블 설계 확인
   • new_column은 이미 테이블에 존재해야 하며, 적절한 데이터 타입으로 설정되어 있어야 합니다. 예를 들어 TEXT 또는 VARCHAR 타입이어야 합니다.

마무리

PostgreSQL의 JSON 데이터 타입은 복잡한 데이터를 효율적으로 처리할 수 있는 강력한 도구입니다. INSERT INTO ... SELECT 구문을 활용하면 기존 데이터를 기반으로 새로운 데이터를 생성하는 작업도 간단히 수행할 수 있습니다.

이 포스팅이 도움이 되셨길 바라며, 추가적인 질문이나 다뤘으면 하는 내용이 있다면 댓글로 남겨주세요!