PostgreSQL에서 배열형 컬럼 다루기

From : https://wwwi.tistory.com/350

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}');

데이터를 하나만 넣을 때는 위와 같이 쓴다.

INSERT INTO member (name, age, hobby)

VALUES('park', 11, ARRAY['movie','sing','craft']);

ARRAY를 사용하여 추가할 수도 있다.

ARRAY를 사용하면 각 항목별에 따옴표를 달아야 된다.

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;

---

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"

배열형 컬럼의 두번째와 세번째 데이터를 읽어 오기

해당 순번에 데이터가 없으면 데이터가 안나온다.

SELECT name, hobby[2:5] FROM member;

---

"kim";"{music}"

"lee";"{}"

"park";"{sing,craft}"

"hong";"{soccer,swim,tennis,book}"

"ha";"{cartoon,drink,sing,dance}"

항목의 2번째에서 5번째 값을 읽어올 때는 위와 같이 쓴다.

SELECT * FROM member

WHERE hobby[1] = 'game';

---

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

첫번째 항목인 'game'인 것을 찾을 때

SELECT * FROM member

WHERE 'book' = ANY(hobby);

---

1;"kim";10;"{book,music}"

4;"hong";9;"{baseball,soccer,swim,tennis,book}"

항목 중에 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;

배열 컬럼의 전체 항목을 바꿀 때

UPDATE member SET hobby[3] = 'sing'

WHERE id = 1;

배열 컬럼의 3번 째 항목을 바꿀 때

UPDATE member SET hobby[5] = 'fishing'

WHERE id = 1;

해당 필드의 항목이 3개있는 경우에 5번째 데이터를 변경하면 4번째는 NULL값이 들어간다.

UPDATE member SET hobby[2:4] = '{golf, sing, movie}'

WHERE id = 2;

구간을 지정해서 항목을 변경할 수 있다.

UPDATE member SET hobby = array_append(hobby, 'game')

WHERE id = 3;

배열 컬럼에 새로운 항목을 추가하고 싶을 때

---