[PostgreSQL] 배열형 컬럼 다루기

PostgreSQL 배열형 컬럼 선언

 - 데이터 타입 선언 시 ' [] '만 붙이면 배열형 컬럼이 됨

 

● 배열형 컬럼 생성

CREATE TABLE member(
    id      serial  PRIMARY KEY,
    name     varchar(20),     
    age      integer,         
    hobby    varchar(100)[]  -- 배열형 컬럼
);

 

 

● 배열형 컬럼에 데이터 추가하기

-- 배열형 데이터를 {} 안에 ","로 구분해서 입력한다.
INSERT INTO member (name, age, hobby) VALUES('kim', 10, '{book, music}');

 

-- 데이터를 하나만 넣을 때는 위와 같이 쓴다.
INSERT INTO member (name, age, hobby) VALUES('lee', 12, '{game}');

 

 

-- ARRAY를 사용하여 추가할 수도 있다. ARRAY를 사용하면 각 항목별에 따옴표를 달아야 된다.
INSERT INTO member (name, age, hobby) VALUES('park', 11, ARRAY['movie','sing','craft']);

 

 

-- 테스트용 데이터 입력
INSERT INTO member (name, age, hobby) VALUES('hong', 9, '{baseball,soccer,swim, tennis,book}');
INSERT INTO member (name, age, hobby) VALUES('ha', 15, '{golf, cartoon, drink, sing, dance}');

 

 

● 배열형 컬럼을 가진 데이터 읽어오기

--배열형 컬럼은 대괄호에 각 항목들이 쉼표로 구분되어진다.
SELECT * FROM member;

-- result
1;"kim";10;"{book,music}"
2;"lee";12;"{game}"
3;"park";11;"{movie,sing,craft}"
4;"hong";9;"{baseball,soccer,swim,tennis,book}"
5;"ha";15;"{golf,cartoon,drink,sing,dance}"

 

 

--배열형 컬럼의 첫번째 데이터만을 읽어 오기
SELECT name, hobby[1] FROM member;

--------------------------------------------------

"kim";"book"
"lee";"game"
"park";"movie"
"hong";"baseball"
"ha";"golf"

 

 

-- 배열형 컬럼의 두번째와 세번째 데이터를 읽어 오기
-- 해당 순번에 데이터가 없으면 데이터가 안나온다.
SELECT name, hobby[2], hobby[3] FROM member;

--------------------------------------------------

"kim";"music";""
"lee";"";""
"park";"sing";"craft"
"hong";"soccer";"swim"
"ha";"cartoon";"drink"

 

-- 항목의 2번째에서 5번째 값을 읽기
SELECT name, hobby[2:5] FROM member;

--------------------------------------------------

"kim";"{music}"
"lee";"{}"
"park";"{sing,craft}"
"hong";"{soccer,swim,tennis,book}"
"ha";"{cartoon,drink,sing,dance}"

 

 

-- 첫번째 항목인 'game'인 것을 찾을 때
SELECT * FROM member WHERE hobby[1] = 'game';

--------------------------------------------------

2;"lee";12;"{game}"

 

-- 항목 중에 book이 있는 것을 찾을 때
SELECT * FROM member WHERE 'book' = ANY(hobby);

--------------------------------------------------

1;"kim";10;"{book,music}"
4;"hong";9;"{baseball,soccer,swim,tennis,book}"

 

-- 배열 컬럼에 항목이 몇개 들어있는지를 알고 싶을 때
SELECT id, (
		SELECT COUNT(arrayColumn) 
		FROM UNNEST(hobby) arrayColumn) as array_count 
FROM member;

 

 

● 배열형 컬럼을 가진 데이터 갱신하기

 

-- 배열 컬럼의 전체 항목을 바꿀 때
UPDATE member SET hobby = '{swim, movie, racing}' WHERE id = 1;

 

-- 배열 컬럼의 3번 째 항목을 바꿀 때
UPDATE member SET hobby[3] = 'sing' WHERE id = 1;

 

-- 해당 필드의 항목이 3개있는 경우에 5번째 데이터를 변경하면 4번째는 NULL값이 들어간다.
UPDATE member SET hobby[5] = 'fishing' WHERE id = 1;

 

-- 구간을 지정해서 항목 변경
UPDATE member SET hobby[2:4] = '{golf, sing, movie}' WHERE id = 2;

 

-- 배열 컬럼에 새로운 항목 추가
UPDATE member SET hobby = array_append(hobby, 'game') WHERE id = 3;

 

출처: https://kwomy.tistory.com/69?category=851266 [Kwomy's DB World]