PostgreSQL에서 배열 사용: 가이드

테이블의 SQL 이미지를 생각할 때 SELECT 쿼리와 중첩된 구조가 없는 데이터 포인트당 하나의 행 개념이 떠오를 것입니다.

하지만 숫자, 제품 등의 목록을 한 행에 저장하고 싶다면 어떻게 해야 할까요?

목록을 여러 행으로 확장할 수 있지만 이로 인해 추적하기 어려울 수 있는 행의 정글이 발생할 수 있습니다. 다행히 PostgreSQL은 목록을 저장하고 조작하는 데 사용할 수 있는 배열 데이터 유형을 제공합니다.

ARRAY를 사용하면 PostgreSQL에서는 테이블의 열을 가변 길이 다차원 배열로 정의할 수 있습니다. 내장형 또는 사용자 정의 기본 유형, 열거형, 복합 유형, 범위 유형 또는 도메인의 배열을 생성할 수 있습니다.

배열은 다양한 형식을 취할 수 있으며 배열을 선언하는 방법도 다양합니다.

아래와 같이 열에 대한 데이터 유형 선언 뒤에 키워드 ARRAY를 사용하여 선언된 이전 데이터 유형의 배열을 생성하려는 것을 나타낼 수 있습니다.

text[]와 같이 대괄호를 사용하여 데이터 유형 선언을 따를 수도 있지만 이는 덜 명확하다고 생각됩니다. 그리고 키워드 버전이 따르는 SQL 표준을 따르지 않습니다.

다음 예에서는 쇼핑 카드를 모방하고 여러 제품의 일부 순열로 채우는 작은 테이블을 만듭니다. 전체 쿼리를 실행하면 배열이 출력으로 표시되는 방식도 확인할 수 있습니다.

테이블에 배열을 추가하는 또 다른 방법은 대괄호가 포함된 ARRAY 키워드 대신 {} 구문을 사용하는 것입니다. 두 가지 방법 모두 작동합니다. 중괄호 안의 인용문에 세심한 주의를 기울이시면 됩니다. 다시 말하지만, 키워드 구문이 더 명확하고 대괄호가 다른 프로그래밍 언어의 배열 표현에 더 가깝지만 둘 다 잘 작동합니다.

데이터 과학에 대한 추가 정보 데이터 과학 인터뷰를 위해 알아야 할 10가지 고급 SQL 개념

일부 제품 정보 테이블을 조인하거나 목록 항목 기반으로 다른 작업을 수행하기 위해 실제로 특정 행에 대한 배열을 분해하거나 중첩 해제해야 한다면 어떻게 될까요?

괜찮아요. PostgreSQL의 배열은 UNNEST 키워드를 사용하여 쉽게 중첩 해제할 수 있습니다.

예상대로 UNNEST는 원래 행의 각 배열에 항목당 하나의 행을 생성합니다.

추가 작업을 위해 단일 항목에 액세스하려고 할 때마다 배열의 중첩을 해제해야 하는지 궁금할 수 있습니다. 그렇다면 배열 구조는 결국 좋은 생각이 아닐 수도 있습니다. 다행히 PostgreSQL의 배열은 아래와 같이 대괄호와 슬라이스를 사용한 인덱싱을 통해 액세스를 허용합니다. 이를 통해 모든 목록의 첫 번째 항목이나 목록의 특정 항목 범위 등을 쉽게 선택할 수 있습니다.

PostgreSQL 배열의 인덱싱은 0이 아닌 1부터 시작합니다. 이는 다른 프로그래밍 언어에 익숙한 것과 다를 수 있습니다.

슬라이스는 [start:end] 구문으로 작동합니다. 세 개 이상의 배열 요소가 있는 모든 행에 대한 분할을 통해 배열 항목에 액세스합니다.

배열에 항목이 몇 개 있는지 확실하지 않거나 위에서 설명한 것처럼 배열 크기로 필터링하려는 경우 CARDINALITY 키워드를 사용할 수 있습니다. 그러면 배열의 항목 수가 정수로 반환됩니다.

배열에 특정 제품이 포함된 모든 장바구니를 선택하려면 어떻게 해야 합니까? 아니면 배열 항목을 기반으로 하는 다른 필터 작업이 있습니까? 문제 없습니다. PostgreSQL에서는 ANY 키워드와 함께 WHERE 절의 항목 값을 사용할 수 있습니다.

아래에서는 제품 배열 열에 'product_c'가 포함된 행을 필터링합니다. 이 필터링 방법은 단일 필터 값에 가장 유용합니다.

위에서 필터 논리를 확장하고 특정 하위 배열이 포함된 쇼핑 카드를 요청할 수도 있습니다. 여기서는 '포함'을 의미하는 '@>' 연산자를 사용하고 있습니다. 다음과 같이 읽을 수 있습니다.

"제품 배열에는 ['product_a', 'product_b'] 배열이 포함되어 있습니다."

배열과 배열 값은 아래와 같이 UPDATE … SET … 절을 사용하여 다른 데이터 유형과 유사하게 업데이트할 수 있습니다. 인덱싱을 통해 단일 배열 항목을 업데이트하거나 전체 배열을 업데이트할 수 있습니다.