[PostgreSQL] ARRAY 데이터 (배열 필드 생성 및 입/출력) :: any() , all()

지난번 포스팅에서 언급되었던 array에 대해 자세히 알아보기로 하자.

array 필드는 입력량이 많은 화면에서, 복수선택이 가능한 checkbox의 값, 또는 여러개의 데이터를 선택하여 저장하는 형식의 데이터를 취급하기에 매우 유리하다.

예를들어 좋아하는 과일과 채소를 선택하는 프로그램을 제작한다고 가정하자.

 

좋아하는과일 : □ 사과  □ 딸기  □ 수박

좋아하는채소 : □ 배추  □ 파  □ 양파

 

위와같이 UI를 제작할 것이다.

사과, 딸기, 수박 필드를 각각 만들어서 데이터를 저장하는 방법을 사용한다면 새로운 과일을 프로그램에 추가할 때 필드의 추가가 반드시 필요해진다. 

또는 □사과 컨트롤의 bool 값 만을 저장한다면, '사과'를 '귤' 로 변경해야하는 상황에 처리가 곤란하다.

이런 상황에서 array 타입은, array [ '사과','수박' ] 값을 저장하여 유연함을 제공한다.

또한 array의 각각의 값을 별도의 컬럼처럼 조회할 수 있다.

 

먼저 array 타입 컬럼을 가진 테이블을 생성해 보자

create table array_test (
	col1 varchar(10),
	col2 varchar(10)[]
);

최대길이 10자리 문자열의 배열 컬럼이 생성되었다. (col2)

col3 varchar(10)[][] 의 방법을 다차원 배열도 생성이 가능하다.

(하지만 위와 같이 다차원 배열은 실제로 사용할일이 많지 않으며, 혹시 필요한 상황이 오더라도 json컬럼을 사용하는것이 낫다.)

array는 [5] 처럼 최대값을 지정하더라도 무시되며, 가질수 있는 값은 제약이 없다. (단, 매우많은 데이터를 저장시 느리다.)

생성한 테이블에 배열값을 넣어 보도록 하자.

 

Insert

insert into array_test(col1, col2) values ('과일', array['사과','딸기','수박']);

insert into array_test(col1, col2) values ('채소', '{배추,파,양파}');

select * from array_test;

col1|col2      |
----|----------|
과일  |{사과,딸기,수박}|
채소  |{배추,파,양파} |

array 함수 또는 {값1,값2} 의 형식으로 데이터를 입력한다.

개발시에는 배열을 Parameter로 전달하여 값을 입력할 수 있다.

 

update / 부분 update

--전체 업데이트
update array_test 
   set col2 = '{사과,딸기,수박,참외}'
 where col1 = '과일';

--부분 업데이트
update array_test
   set col2[2] = '상추'
 where col1 = '채소';
 
 
select * from array_test;

col1|col2         |
----|-------------|
과일  |{사과,딸기,수박,참외}|
채소  |{배추,상추,양파}   |

 

배열의 일부 값을 조회하거나 변경할 때는 컬럼명[인덱스] 의 문법을 사용한다.

기억해야 할 점은 시작값이 1이라는 것이다.

[1],[2] 2개의 값을 가진 배열에 [4] 배열값을 넣으면 [3]은 null이 된다.

 

 

array_append 및 범위 update

update array_test 
   set col2 = array_append(col2,'복숭아')
 where col1 = '과일';

update array_test
   set col2[1:2] = '{배,귤}'
 where col1 = '과일';

array의 항목을 추가하고 싶을 때는 array_append 함수를 사용한다.

array의 범위 자료를 조회하거나 수정할 때는 [index1, index2] 의 방법을 사용한다.

 

 

array 값을 조회하는 방법 :: any(), all()

--배열의 첫번째 값이 '배'인 row 조회
select * from array_test where col2[1] = '배'; 
--배열에 '배'가 포함된 row 조회
select * from array_test where '배' = any(col2);
--배열 내 모든 데이터가 '배'인 row 조회
select * from array_test where '배' = all(col2);

col2[1] 의 형식으로 table index를 설정하는 것도 가능하다

 

array 값을 컬럼처럼 사용

select col1    as 분류
     , col2[1] as 과일1
     , col2[2] as 과일2
     , col2[3] as 과일3
     , col2[100] as 과일100
  from array_test where col1 = '과일';

 
분류|과일1|과일2|과일3|과일100|
--|---|---|---|-----|
과일|배  |귤  |수박 |     |

 

존재하지 않는 index를 넣어도 오류는 발생하지 않고 null값으로 조회된다.

고정값 배열을 사용 시 조회방법으로 용이하다.