character varying (varchar), character (char), text
Binary Data
bytea
Date/Time
timestamp [ (p) ] [ without time zone ], timestamp [ (p) ] with time zone, interval [ (p) ], date, time [ (p) ] [ without time zone ], time [ (p) ] with time zone (pgsql의 시간 저장 정밀도는 꽤 높은 편이다.)
Boolean
boolean (bool)
Geometric
point, line, lseg, box, path, polygon, circle
Network Address
cidr (IPV6 지원), inet (IPV6 지원), macaddr
Bit String
BIT (zerofill), BIT VARYING
Pseudo
any, anyarray, anyelement, cstring, void 등 (직접 정의 할 수는 없다.)
이 외에도 사용자가 직접 데이터 타입을 만드는 것이 가능하다!! (SQL: CREATE TYPE)
배열컬럼으로 전환
원 데이터 타입
배열 데이터 타입
설명
text
text[]
text 1차원 배열
text
text[][]
text 2차원 배열
text
text[][2]
2개의 요소를 가지는 text배열의 배열
integer
integer[]
integer 1차원 배열
integer
integer[][]
integer 2차원 배열
........
위의 표를 보시느라 수고하셨다. 그냥 뒤에 [] 를 붙여주는 깊이 만큼의 배열이 생성된다. 그것도 모든 데이터 타입에 대해서!
배열 컬럼이 왜 필요한가?
테이블 설계 측면
아무리 설계가 중요하고 데이터 설계가 중요하다지만 누구나 실수는 있는법. 데이터 설계하고 테이블을 다 만들어 놨는데, 요구사항이 바뀌거나 빼먹은 컬럼이 있다고 가정을 하자.
보통 이런 경우는 PK나 FK 또는 인덱스가 잡혀야 할 만큼 크리티컬한 경우는 아닐것이다. (이런 경우라면 큰! 실수이며 테이블을 새로 만들어야 하겠다.)
그렇다면 빼먹은 데이터가 단순히 저장하고 보여줄 수만 있다면 된다고 치자. 또는 시스템이나 기능이 업그레이드 되면서 크리티컬하지 않은 저장해야 할 데이터가 새로 생겼다고 치자! 일반적인 경우라면 alter table 등의 SQL을 이용하여 컬럼을 추가한다...... (어떤 DBA는 임시 컬럼을 몇개 미리 추가해서 설계하는 경우도 있다. 그다음에 view만 잘 만들면 된다나...)
이런 경우 배열컬럼이 위력을 발휘한다. 몇천만의 row가 들어있는 테이블에 alter table 을 사용한다면 오랜 시간의 테이블 락이 걸릴것이고 서비스는 중지된다. 새로 테이블을 만들고 dump데이터나 insert into ... select 로 한다고 해도 꽤 많은 시간이 걸린다. 배열컬럼은 배열 요소 하나를 추가 저장하는 것만으로 모든 작업이 끝난다.
테이블 관계/의존성 단순화
1:n의 관계에 있는 테이블이 2개 있다. 자식 테이블은 어미테이블의 pk를 기준으로 n개의 레코드가 쌓인다. 두 테이블에서 데이터를 가져올 때 select ... from .. join 등을 써서 가져오거나 쿼리를 2번 해야 한다.
배열 컬럼은 이런 조인작업을 없애준다. 한개의 테이블에서 한개의 배열컬럼으로 해결 되는 것이다. 1:n:n 테이블의 경우라면 [][] 으로 2차원 배열을 만들면 되는 것이다.
물론 배열 요소의 검색이 없다면 말이다. (integer형이라면 검색할 수도 있다. gist 인덱스를 써서..)
Contents
배열컬럼
PostgreSQL의 Data Types
배열컬럼으로 전환
배열 컬럼이 왜 필요한가?
테이블 설계 측면
테이블 관계/의존성 단순화
연산자
연산자 목록
함수
함수 목록
비교 함수
사용예
기본적인 사용법
데이터 입력
데이터 조회
generic 테이블 설계
설계측면의 generic 한 테이블 설계 (예제: 투표)
일반적인 정규화에 따른 설계
PostgreSQL의 배열컬럼을 사용한 설계
필자의 테이블 사용예
Recent Posts
Archive Posts
Tags