Data Type

AgensGraph는 다양한 데이터 타입을 제공합니다. CREATE TYPE 명령을 사용하여 새로운 타입을 추가할 수도 있습니다. CREATE TYPE에 대한 자세한 내용은 [User-defined Type] 절을 참조하십시오.

다음 표는 기본적으로 제공되는 일반 데이터 타입을 나열하며, 내부적으로 사용되거나 사용되지 않는 일부 타입은 포함되지 않을 수 있습니다. (“Alias”는 내부적으로 사용되는 이름입니다.)

Name	Alias	Description
bigint	int8	부호 있는 8바이트 정수
bigserial		자동 증가 8바이트 정수
bit [ (n) ]	16-bit integer	고정 길이 비트 문자열
bit varying [ (n) ]	varbit	가변 길이 비트 문자열
boolean	bool	논리 Boolean (true / false)
box		평면상의 사각형 박스
bytea		바이너리 데이터(“byte array”)
character [ (n) ]	char [ (n) ]	고정 길이 문자열
character varying [ (n) ]	varchar [ (n) ]	가변 길이 문자열
cidr		IPv4 또는 IPv6 네트워크 주소
circle		평면상의 원
date		달력 날짜(연, 월, 일)
double precision	float8	배정밀도 부동소수점 숫자(8바이트)
inet		IPv4 또는 IPv6 호스트 주소
integer	int, int4	부호 있는 4바이트 정수
interval [ fields ] [ (p) ]		시간 범위
json		텍스트 JSON 데이터
jsonb		바이너리 JSON 데이터, 분해됨
line		평면상의 무한 직선
lseg		평면상의 선분
macaddr		Media Access Control(MAC) 주소
money		통화 금액
numeric [ (p, s) ]	decimal [ (p, s) ]	선택 가능한 정확한 자릿수의 숫자
path		평면상의 기하학적 경로
pg_lsn		AgensGraph 로그 시퀀스 번호
point		평면상의 기하학적 점
polygon		평면상의 기하학적으로 닫힌 경로
real	float4	단정밀도 부동소수점 숫자(4바이트)
smallint	int2	부호 있는 2바이트 정수
smallserial	serial2	자동 증가 2바이트 정수
serial	serial4	자동 증가 4바이트 정수
text		가변 길이 문자열
time [ (p) ] [ without time zone ]		시간(타임존 없음)
time [ (p) ] with time zone	timetz	시간 및 타임존 포함
timestamp [ (p) ] [ without time zone ]		날짜와 시간(타임존 없음)
timestamp [ (p) ] with time zone	timestamptz	타임존을 포함한 날짜와 시간
tsquery		텍스트 검색 쿼리
tsvector		텍스트 검색 도큐먼트
txid_snapshot		사용자 수준 트랜잭션 ID 스냅샷
uuid		범용 고유 식별자
xml		XML 데이터

Numeric Types

numeric 타입은 2/4/8바이트 정수, 4/8바이트 부동소수점 숫자, 그리고 선택 가능한 총 자릿수로 구성됩니다.

다음 numeric 타입을 사용할 수 있습니다:

Name	Storage Size	Description	Range
smallint	2 bytes	작은 범위의 정수	-32768 to +32767
integer	4 bytes	일반 정수	-2147483648 to +2147483647
bigint	8 bytes	큰 범위의 정수	-9223372036854775808 to +9223372036854775807
decimal	variable	사용자 지정 정밀도, 정확함	소수점 앞 최대 131072자리,\
			소수점 뒤 최대 16383자리
numeric	variable	사용자 지정 정밀도, 정확함	소수점 앞 최대 131072자리,\
			소수점 뒤 최대 16383자리
real	4 bytes	가변 정밀도, 부정확함	6자리 정밀도
double precision	8 bytes	가변 정밀도, 부정확함	15자리 정밀도
smallserial	2 bytes	자동 증가 상수(작음)	1 to 32767
serial	4 bytes	자동 증가 상수	1 to 2147483647
bigserial	8 bytes	자동 증가 정수(큼)	1 to 9223372036854775807

Integer Types

smallint, integer, bigint 타입은 소수 부분이 없는 넓은 범위의 정수를 저장합니다. 허용 범위를 벗어난 값을 저장하려고 하면 오류가 발생합니다.

integer 타입: 범위, 저장 크기, 성능 사이에서 최적의 균형점을 제공하므로 일반적으로 선택됩니다.
smallint 타입: 일반적으로 디스크 공간이 부족할 때만 사용됩니다.
bigint 타입: integer 타입의 범위가 부족할 때 사용됩니다.

SQL은 integer(또는 int), smallint, bigint만 지정합니다. (int2, int4, int8로도 사용할 수 있습니다.)

Arbitrary Precision Numbers

numeric 타입은 매우 많은 자릿수를 저장하고 계산을 정확하게 수행할 수 있습니다. 정확해야 하는 금액과 수량을 저장할 때 특히 권장됩니다. 그러나 numeric 값의 산술 연산은 다음 절에서 설명하는 정수 타입이나 부동소수점 타입보다 훨씬 느립니다.

numeric 타입에서 scale은 소수점 오른쪽의 자릿수를 의미합니다. precision은 전체 숫자의 유효 자릿수의 총합을 의미합니다. 즉, 소수점 양쪽 자릿수의 총합입니다. 따라서 숫자 23.5141의 precision과 scale은 각각 6과 4입니다. 정수의 scale은 0(Scale 0)으로 간주할 수 있습니다.

numeric 열의 최대 precision과 최대 scale을 모두 구성할 수 있습니다.

numeric 타입의 열을 선언하려면 다음 구문을 사용하십시오:

NUMERIC(precision, scale)

precision는 양수여야 하고 scale은 0 또는 양수여야 합니다.

NUMERIC(precision)

다음과 같이 precision나 scale 없이 numeric을 지정하면 Scale 0이 선택됩니다.

NUMERIC

precision와 scale 값을 지정하지 않고 저장할 수 있는 numeric 열을 생성하면 precision 값을 저장할 수 있습니다. 이러한 종류의 열은 특정 scale을 지정하지 않으면 제한 없이 사용할 수 있습니다. 그러나 scale 값이 지정된 numeric 열은 해당 scale 값에 의해 제한됩니다. (데이터를 전송할 때는 항상 precision와 scale을 지정해야 합니다.)

note::

참고: 타입 선언에서 명시적으로 지정하는 경우 허용되는 최대 precision는 1000입니다. precision를 지정하지 않은 NUMERIC은 표에 설명된 범위로 제한됩니다.

저장할 값의 scale이 열에 선언된 scale보다 큰 경우, 시스템은 값을 지정된 scale로 반올림합니다. 반올림 후 소수점 오른쪽 자릿수가 “선언된 precision에서 선언된 scale을 뺀 값”을 초과하면 오류가 발생합니다. numeric 값은 추가적인 0 값 없이 물리적으로 저장됩니다. 따라서 선언된 열의 precision와 scale은 최댓값입니다(고정 할당이 아님). 이런 의미에서 numeric 타입은 char (n)보다 varchar (n)에 더 가깝습니다. 실제 저장 요구량은 4자리 그룹마다 2바이트에 8바이트 오버헤드를 위한 3바이트를 더한 값입니다.

decimal 타입과 numeric 타입은 동일하며 둘 다 SQL 표준입니다.

Floating-Point Types

real과 double precision 데이터 타입은 부정확한 가변 정밀도 numeric 타입입니다. 부정확하다는 것은 일부 값이 내부 형식으로 정확하게 변환될 수 없고 근삿값으로 저장되어, 값의 저장과 검색이 다소 일관되지 않게 됨을 의미합니다.

정확한 저장과 계산이 필요한 경우(예: 금액), 대신 numeric 타입을 사용하십시오.
불가피한 이유로 이 타입을 사용하여 복잡한 계산을 수행해야 하는 경우(예: 경계 사례(무한대, 언더플로)에서 특정 동작이 필요한 경우), 구현에 주의해야 합니다.
두 부동소수점 값이 같은지 비교하는 것은 항상 예상대로 동작하지 않을 수 있습니다.

대부분의 플랫폼에서 real 타입은 최소 1E-37에서 1E+37 범위를 가지며, 정밀도는 최소 6자리입니다. double precision 타입은 일반적으로 최소 15자리의 정밀도를 가지며, 범위는 1E-307에서 1E+308입니다. 너무 크거나 너무 작은 값은 오류를 발생시킵니다. 입력된 숫자의 정밀도가 너무 크면 반올림될 수 있습니다. 숫자가 0에 너무 가까워서 0이 아닌 값으로 표시할 수 없는 경우 언더플로 오류가 발생합니다.

Serial Types

smallserial, serial, bigserial 데이터 타입은 실제 타입이 아니라 고유 식별자 열을 생성하기 위한 국제적 표기법입니다(일부 다른 데이터베이스에서 지원하는 AUTO_INCREMENT 속성과 유사함).

다음 두 구문은 동일한 방식으로 동작합니다:

-- SERIAL
CREATE TABLE tablename (
colname SERIAL
);

-- SEQUENCE
CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval ('tablename_colname_seq')
);

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

integer 열을 생성하고 시퀀스에서 기본적으로 할당된 값으로 채웁니다. NULL 값이 삽입되는 것을 방지하기 위해 NOT NULL 제약 조건이 적용됩니다. (대부분의 경우 UNIQUE 또는 PRIMARY KEY 제약 조건으로 중복 값이 실수로 입력되는 것을 방지할 수 있지만, 이러한 제약 조건은 자동으로 생성되지 않습니다.)

마지막으로, 시퀀스는 열에 의해 “소유됨(owned by)”으로 표시되므로 해당 열이나 테이블이 삭제되지 않는 한 삭제되지 않습니다.

serial 열의 기본값은 시퀀스의 다음 값을 serial 열에 삽입하기 위해 지정됩니다. 이는 INSERT 구문의 열 목록에서 열을 제외하거나 DEFAULT 키워드를 사용하여 수행할 수 있습니다.

serial 타입은 serial4와 동일하며, 둘 다 integer 열을 생성합니다.
bigserial과 serial8 타입은 bigint 열을 생성한다는 점을 제외하면 동일하게 동작합니다. 테이블의 수명 동안 2^31개 이상의 식별자를 사용할 것으로 예상되는 경우 bigserial을 사용해야 합니다.
smallserial과 serial2 타입은 smallint 열을 생성한다는 점을 제외하면 동일하게 동작합니다.

serial 열을 위해 생성된 시퀀스는 소유 열이 삭제될 때 자동으로 삭제됩니다. 열을 삭제하지 않고 시퀀스를 삭제할 수 있지만, 그 경우 열의 기본 표현식이 강제로 삭제됩니다.

Character Types

다음 character 타입을 사용할 수 있습니다:

Name	Description
character varying(n), varchar(n)	제한이 있는 가변 길이
character(n), char(n)	고정 길이, 빈칸으로 채움
text	제한 없는 가변 길이

두 가지 기본 character 타입은 character varying(n)과 character(n)을 정의합니다. n의 값은 양의 정수이며, 둘 다 최대 n 길이의 문자(바이트가 아님)를 저장할 수 있습니다.

n보다 긴 문자열을 저장하는 경우, 초과 문자열이 빈칸이 아니면 오류가 발생하고, 빈칸이면 지정된 n 값의 길이로 잘립니다. n자보다 짧은 문자열을 저장하는 경우, character 타입에서는 빈칸으로 채워집니다. character varying의 경우 빈칸을 제외한 문자열이 저장됩니다.

character 타입의 후행 빈칸은 구문적으로 유의미하지 않은 것으로 취급되며, character 타입의 두 값을 비교할 때 무시됩니다. character varying, text, LIKE와 같은 패턴 매칭 정규 표현식을 사용할 때는 후행 빈칸이 구문적으로 유의미합니다.

char(n)과 varchar(n)은 character varying(n)과 character(n)의 별칭입니다. 지정자가 없는 character는 character(1)과 동일하며, 지정자가 없는 character varying은 크기에 관계없이 문자열을 저장합니다. 또한 길이에 관계없이 문자열을 저장하기 위한 text 타입도 제공합니다.

character 타입을 사용하는 예제는 다음과 같습니다:

create table test1 (a character (4));
CREATE TABLE
insert into test1 values ('ok');
INSERT 0 1
select a, char_length(a) from test1;
  a   | char_length 
------+-------------
 ok   |           2
 
create table test2 (b varchar(5));
CREATE TABLE
insert into test2 values ('ok');
INSERT 0 1
insert into test2 values ('good ');
INSERT 0 1
insert into test2 values ('too long');
Error: character varying(5) tries to store data that is too long for the data type.      
insert into test2 values ('too long'::varchar(5));
INSERT 0 1
select b, char_length(b) from test2;
   b   | char_length 
-------+-------------
 ok    |           2
 good  |           5
 too l |           5

Date/Time Types

다음 date/time 타입을 사용할 수 있습니다:

Name	Storage Size	Description	Low Value	High Value	Resolution
timestamp [ (p) ] [ without time zone ]	8 bytes	날짜와 시간 모두(타임존 없음)	4713 BC	294276 AD	1 microsecond / 14 digits
timestamp [ (p) ] with time zone	8 bytes	날짜와 시간 모두, 타임존 포함	4713 BC	294276 AD	1 microsecond / 14 digits
date	4 bytes	날짜(시간 없음)	4713 BC	5874897 AD	1 day
time [ (p) ] [ without time zone ]	8 bytes	시간(날짜 없음)	00:00:00	24:00:00	1 microsecond / 14 digits
time [ (p) ] with time zone	12 bytes	하루 중 시간, 타임존 포함	00:00:00	24:00:00-1459	1 microsecond / 14 digits
			+1459
interval [ fields ] [ (p) ]	16 bytes	시간 간격	-178000000 years	178000000 years	1 microsecond / 14 digits

참고: SQL 표준에서는 timestamp와 timestamp without time zone을 동일하게 사용하며, timestamptz는 timestamp with time zone의 약어입니다.

time, timestamp, interval은 초 필드의 scale(소수 부분의 자릿수)을 p 값으로 설정할 수 있으며, 기본적으로 precision(전체 자릿수)에 대한 명시적 제한은 없습니다. p의 허용 범위는 timestamp와 interval 타입의 경우 0에서 6까지입니다.

time 타입의 경우 p의 허용 범위는 8바이트 정수 저장소를 사용할 때 0에서 6까지이고, 부동소수점 저장소를 사용할 때 0에서 10까지입니다.

interval 타입에는 다음 구문 중 하나를 작성하여 저장되는 필드 집합을 제한하는 추가 옵션이 있습니다:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

Date/Time Input

날짜와 시간 입력은 다음과 같이 일, 월, 연 순서로 지정할 수 있습니다:

set datestyle to sql, mdy;
set datestyle to sql, dmy;
set datestyle to sql, ymd;

DateStyle 매개변수를 MDY로 설정하면 월-일-연 해석을, DMY로 설정하면 일-월-연 해석을, YMD로 설정하면 연-월-일 해석을 선택합니다.

날짜 또는 시간 입력은 텍스트 문자열처럼 앞뒤에 작은따옴표가 있어야 합니다.

날짜 사용 가능한 날짜 타입:

Example	Description
1999-01-08	ISO 8601. 모든 모드에서 1월 8일(권장 형식)
January 8, 1999	datestyle 입력 모드에서 모호함
1/8/1999	MDY 모드에서 1월 8일. DMY 모드에서 8월 1일
1/18/1999	MDY 모드에서 1월 18일. 다른 모드에서는 거부됨
01/02/03	MDY 모드에서 2003년 1월 2일 \
	DMY 모드에서 2003년 2월 1일 \
	YMD 모드에서 2001년 2월 3일
1999-Jan-08	모든 모드에서 1월 8일
Jan-08-1999	모든 모드에서 1월 8일
08-Jan-1999	모든 모드에서 1월 8일
99-Jan-08	YMD 모드에서 1월 8일. 다른 모드에서는 오류
08-Jan-99	YMD 모드에서의 오류를 제외하고 1월 8일
Jan-08-99	YMD 모드에서의 오류를 제외하고 1월 8일
19990108	ISO 8601. 모든 모드에서 1999년 1월 8일
990108	ISO 8601. 모든 모드에서 1999년 1월 8일
1999.008	연도 및 해당 연도의 날짜
J2451187	율리우스 일자
January 8, 99 BC	기원전 99년

시간 시각 타입은 time [(p)] without time zone과 time [(p)] with time zone입니다. time 단독은 time without time zone과 동일합니다. 이 타입의 유효한 입력은 시간과 그 뒤에 오는 선택적 타임존으로 구성됩니다. (아래 표를 참조하십시오.) time without time zone에 대한 입력으로 타임존이 지정되면 무시됩니다. 날짜를 지정할 수 있지만, America/New_York과 같이 일광 절약 시간과 연관된 타임존 이름을 사용하는 경우를 제외하고는 무시됩니다. 이 경우 표준시 기간이 적용되는지 일광 절약 시간 기간이 적용되는지를 판단하기 위해 날짜 지정이 필요합니다. 적절한 타임존 오프셋이 time with time zone 값에 기록됩니다.

표 - [시간 입력]

Example	Description
04:05:06.789	ISO 8601
04:05:06	ISO 8601
04:05	ISO 8601
040506	ISO 8601
04:05 AM	04:05과 동일. AM은 값에 영향을 주지 않음.
04:05 PM	16:05과 동일. 입력은 12 이하여야 함.
04:05:06.789-8	ISO 8601
04:05:06-08:00	ISO 8601
04:05-08:00	ISO 8601
040506-08	ISO 8601
04:05:06 PST	약어로 지정된 타임존
2003-04-12 04:05:06 America/New_York	전체 이름으로 지정된 타임존

표 - [타임존 입력]

Example	Description
PST	약어(태평양 시간용)
America/New_York	모든 타임존 이름
PST8PDT	POSIX 스타일 타임존 지정
-8:00	PST에 대한 ISO-8601 오프셋
-800	PST에 대한 ISO-8601 오프셋
-8	PST에 대한 ISO-8601 오프셋
zulu	UTC를 나타내는 군용 약어

타임존을 지정하는 방법은 아래의 [Time Zones] 절을 참조하십시오.

타임스탬프 timestamp 타입의 유효한 입력은 날짜와 시간의 연결, 그 뒤에 오는 선택적 타임존과 선택적 AD 또는 BC로 구성됩니다. (AD/BC가 타임존 앞에 나타날 수 있으나, 이는 권장되는 순서가 아닙니다.)
```
1999-01-08 04:05:06
1999-01-08 04:05:06 -8:00
```
이 값들은 ISO 8601 표준에 따른 유효한 값입니다. 다음 명령 형식도 지원됩니다:
```
January 8 04:05:06 1999 PST
```
SQL 표준은 “+” 또는 “-” 기호의 존재 여부와 그 시간 뒤의 타임존 오프셋으로 timestamp without time zone과 timestamp with time zone 리터럴을 구별합니다.
```
 -- timestamp without time zone
 TIMESTAMP '2004-10-19 10:23:54'
 
 -- timestamp with time zone
 TIMESTAMP '2004-10-19 10:23:54+02'
```
timestamp with time zone를 지정하지 않으면 timestamp without time zone으로 취급됩니다. 따라서 timestamp with time zone을 사용하려면 명시적 타입을 지정해야 합니다.
```
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
```

특수 값 표에 나와 있는 것처럼 편의를 위해 일부 특수 date/time 입력 값이 지원됩니다. infinity와 -infinity 값은 시스템 내에서 변환 없이 특별하게 표현되는 단순한 약어이지만, 그 외의 값은 읽을 때 기본 date/time 값으로 변환됩니다. (now 및 관련 문자열은 읽히는 시점에 특정 시간 값으로 변환된다는 점에 유의하십시오.) SQL 명령에서 이러한 값을 상수로 사용하는 경우 상수 앞뒤에 작은따옴표를 사용해야 합니다.

Input String	Valid Type	Description
epoch	date, timestamp	1970-01-01 00: 00: 00 + 00 (Unix 시스템 시간 0)
infinity	date, timestamp	다른 모든 타임스탬프 이후
-infinity	date, timestamp	다른 모든 타임스탬프 이전
now	date, time, timestamp	현재 트랜잭션의 시작 시간
today	date, timestamp	오늘 자정
tomorrow	date, timestamp	내일 자정
yesterday	date, timestamp	어제 자정
allballs	time	00:00:00.00 UTC

또한 다음과 같은 SQL 호환 함수를 사용하여 CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP 등 데이터 타입의 현재 시간 값을 얻을 수 있습니다.

Date/Time Output

date/time 타입의 출력 형식은 ISO 8601, SQL(Ingres), 일반적인 POSTGRES(Unix 날짜 형식) 또는 German의 네 가지 스타일 중 하나로 설정할 수 있습니다. 기본값은 ISO 형식입니다.

다음 표는 각 출력 스타일의 예를 보여줍니다. date/time 타입의 출력은 주어진 예에 따라 보통 날짜 또는 시간만 가집니다.

Style Specification	Description	Example
ISO	ISO 8601, SQL 표준	1997-12-17 07:37:16-08
SQL	전통적 스타일	12/17/1997 07:37:16.00 PST
Postgres	원래 스타일	Wed Dec 17 07:37:16 1997 PST
German	지역 스타일	17.12.1997 07:37:16.00 PST

DMY 필드 순서가 지정되면 월, 일 순서로 출력됩니다. 다른 경우에는 일, 월 순서로 출력됩니다. 다음 표는 그 예입니다:

datestyle Setting	Input Ordering	Example Output
SQL, DMY	day/month/year	17/12/1997 15:37:16.00 CET
SQL, MDY	month/day/year	12/17/1997 07:37:16.00 PST
Postgres, DMY	day/month/year	Wed 17 Dec 07:37:16 1997 PST

date/time 스타일은 SET datestyle 명령, postgresql.conf 설정 파일의 DateStyle 매개변수, 또는 서버나 클라이언트의 PGDATESTYLE 환경 변수를 사용하여 사용자가 선택할 수 있습니다. 포매팅 함수 to_char를 사용하면 date/time 출력 형식을 보다 유연하게 지정할 수 있습니다.

Time Zones

타임존과 표기법은 지형적 특성 외에도 정치적 결정의 영향을 받습니다. 전 세계 타임존은 1900년대에 표준화되었지만 일광 절약 시간 규정으로 인해 끊임없이 변화하고 있습니다. AgensGraph는 IANA(Olson) 타임존 데이터베이스를 사용합니다. 미래 시간의 경우, 지정된 타임존에 대해 가장 최근에 알려진 규칙이 먼 미래까지 무기한 준수될 것으로 간주합니다. 그러나 날짜, 시간 타입, 기능이 독특하게 혼합되어 있습니다.

명백한 두 가지 문제는 다음과 같습니다:

date 타입에는 연관된 타임존이 없습니다(time 타입만 가짐). 실제 환경에서는 일광 절약 시간 경계를 포함하는 연중 오프셋이 달라질 수 있으므로, 날짜 및 시간과 연관되지 않는 한 타임존은 의미가 없습니다.
기본 타임존은 UTC로부터의 상수 숫자 오프셋으로 지정됩니다. 따라서 DST 경계를 가로지르는 date/time 계산을 수행할 때 일광 절약 시간에 적응하지 못할 수 있습니다.

이 문제를 해결하기 위해 타임존을 사용할 때는 날짜와 시간을 모두 포함하는 date/time 타입을 사용하는 것을 권장합니다(time with time zone 타입은 호환성을 위해 지원되지만 사용을 권장하지 않습니다). 날짜나 시간만 포함하는 타입에는 로컬 타임존을 가정합니다. 타임존이 있는 날짜와 시간은 내부적으로 UTC로 저장됩니다. 그런 다음 클라이언트에 표시되기 전에 TimeZone 설정 매개변수로 지정된 타임존의 로컬 시간으로 변환됩니다. 타임존은 세 가지 형식으로 지정할 수 있습니다.

타임존의 전체 이름(예: America/New_York). 인식되는 타임존 이름은 pg_timezone_names 뷰에 나열되어 있습니다. (동일한 타임존 이름은 다른 많은 소프트웨어 애플리케이션에서도 인식됩니다.)
타임존 약어(예: PST). 일광 절약 시간 변환 날짜 규칙 집합을 암시할 수 있는 타임존의 전체 이름과 달리, 해당 지정은 단순히 UTC로부터의 특정 오프셋을 정의합니다. 인식되는 약어는 pg_timezone_abbrevs 뷰에 나열되어 있습니다. 설정 매개변수 TimeZone이나 log_timezone을 타임존 약어로 설정할 수는 없지만, date/time 입력 값으로 약어와 AT TIME ZONE 연산자를 사용할 수 있습니다.
타임존 이름과 약어 외에도, STDoffset 또는 STDoffsetDST 형식의 POSIX 스타일 타임존 지정을 허용합니다. STD는 지역 약어, offset은 서쪽 시간에 대한 UTC 숫자 오프셋, DST는 지정된 오프셋보다 한 시간 빠를 것으로 가정되는 선택적 서머타임 지역 약어입니다. 예를 들어 EST5EDT가 아직 인식되는 지역 이름이 아니라면, 이는 허용되며 미국 동부 해안 시간과 기능적으로 동일해집니다. 이 구문에서 지역 약어는 임의의 문자열이거나 꺾쇠괄호(<>)를 사용하는 임의의 문자열일 수 있습니다. 일광 절약 지역 약어가 있는 경우, IANA 타임존 데이터베이스의 posixrules 항목에서 사용되는 것과 동일한 일광 절약 시간 변환 규칙에 따라 사용되는 것으로 가정합니다. 표준 AgensGraph 설치에서 posixrules는 US/Eastern과 동일하므로, POSIX 스타일 타임존 지정은 미국 일광 절약 시간 규칙을 준수합니다. 필요한 경우 posixrules 파일을 교체하여 이 동작을 조정할 수 있습니다.

간단히 말해, 이것이 약어와 전체 이름의 차이입니다. 타임존 약어는 UTC로부터의 특정 오프셋을 나타내는 반면, 많은 타임존 전체 이름은 지역 일광 절약 시간 규칙을 암시하며 가능한 두 개의 UTC 오프셋을 가집니다. 예를 들어, 뉴욕 지역의 정오를 나타내는 “2014-06-04 12:00 America/New_York”은 그날 동부 일광 절약 시간(UTC-4)이었습니다. 따라서 “2014-06-04 12:00 EDT”는 동일한 시간 순간을 지정합니다. 그러나 “2014-06-04 12:00 EST”는 그날 일광 절약 시간이 명목상 적용되는지 여부에 관계없이 동부 표준시(UTC-5)의 정오를 지정합니다.

문제를 더 복잡하게 만드는 것은, 일부 지역이 동일한 타임존 약어를 시점에 따라 서로 다른 UTC 오프셋을 의미하는 데 사용한다는 점입니다. 예를 들어 모스크바의 MSK는 몇 년 동안 UTC+3을 의미했고, 다른 경우에는 UTC+4를 의미했습니다. 위의 EST 예처럼 이러한 약어가 주어진 날짜에 대한 의미(또는 가장 최근의 의미)에 따라 해석되더라도, 이것이 반드시 해당 날짜의 로컬 시간과 일치하지는 않습니다. POSIX 스타일 타임존 기능은 지역 약어의 정확성을 확인하지 않는다는 점에 유의하십시오. 즉, 잘못된 입력을 조용히 받아들일 수 있습니다. 예를 들어 SET TIMEZONE TO FOOBAR0은 시스템이 UTC에 대해 특정 약어를 사용하도록 함으로써 동작합니다. 또 한 가지 유념할 문제는, POSIX 타임존 지역 이름에서는 그리니치 서쪽 위치에 양의 오프셋이 사용된다는 점입니다. 다른 곳에서 AgensGraph는 양의 타임존 오프셋이 그리니치 동쪽인 ISO-8601 표기법을 따릅니다. 타임존 이름과 약어는 대소문자를 구분하며 서버에 내장되어 있지 않습니다. 이들은 설치 디렉터리에 저장된 설정 파일(…/share/timezone/ 및 …/share/timezonesets/)에서 검색됩니다. TimeZone 설정 매개변수는 postgresql.conf와 다른 다음과 같은 표준 방법으로도 설정할 수 있습니다:

SQL 명령 SET TIME ZONE은 세션에 대한 타임존을 설정합니다. SQL 사양과 더 호환되는 SET TIMEZONE TO를 사용할 수 있습니다.
PGTZ 환경 변수는 libpq 클라이언트가 연결 시 서버에 SET TIME ZONE 명령을 보내는 데 사용됩니다.

Interval Input

interval 값은 다음과 같은 상세 구문을 사용하여 작성할 수 있습니다:

[@] quantity unit [quantity unit...] [direction]

여기서 quantity는 숫자이고 unit은 microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium 또는 그 약어(단수 또는 복수)일 수 있습니다. direction은 ago이거나 비어 있을 수 있습니다. at(@) 기호는 선택적 노이즈입니다. 서로 다른 단위의 수량은 적절한 부호 계산을 사용하여 암묵적으로 더해집니다. 이 구문은 IntervalStyle이 postgres_verbose로 설정된 경우 interval 출력에도 사용됩니다.

일, 시, 분, 초는 단위를 명시적으로 표시하지 않고 지정할 수 있습니다. 예를 들어 “1 12:59:10”은 “1 day 12 hours 59 min 10 sec”와 동일하게 읽힙니다.

연과 월의 조합도 대시를 사용하여 지정할 수 있습니다. 예를 들어 “200-10”은 “200 years 10 months”와 동일하게 읽힙니다. (이 짧은 형식은 실제로 SQL 표준에서 허용하는 유일한 형식이며, IntervalStyle이 sql_standard로 설정된 경우 출력에 사용됩니다.)

지정자를 사용하는 형식:

P quantity unit [ quantity unit ...] [ T [ quantity unit  ...]]

문자열에는 P가 포함되어야 하며, 시간 단위를 포함하기 위해 T를 포함할 수 있습니다. 사용 가능한 단위 약어는 아래 표에 나열되어 있습니다. 단위는 생략할 수 있고 임의의 순서로 지정할 수 있지만, 하루 미만의 단위는 T 뒤에 나타나야 합니다. 특히 M의 의미는 T 앞에 있는지 뒤에 있는지에 따라 달라집니다.

표 [ISO 8601 Interval 단위 약어]

Abbreviation	Meaning
Y	연
M	월(date 부분)
W	주
D	일
H	시
M	분(time 부분)
S	초

대체 형식:

P [ years-months-days ] [ T hours:minutes:seconds ]

문자열은 P로 시작해야 하며, T는 interval의 날짜와 시간을 구분합니다. 값은 ISO 8601 날짜와 유사한 숫자로 지정됩니다. fields 지정을 사용하여 interval 상수를 생성하거나, fields 지정으로 정의된 interval 열에 문자열을 할당하는 경우, 표시되지 않은 수량의 해석은 fields에 따라 달라집니다. 예를 들어 INTERVAL ‘1’ YEAR는 1년으로 해석되는 반면, INTERVAL ‘1’은 1초를 의미합니다. fields 지정이 허용하는 가장 낮은 오른쪽 필드 값은 무시됩니다.

예를 들어 INTERVAL ‘1 day 2:03:04’ HOUR TO MINUTE는 결국 “초” 필드를 삭제합니다(날짜 필드가 아님). SQL 표준에 따라 interval 값의 모든 필드는 동일한 부호를 가져야 하므로, 선행 음수 부호는 모든 필드에 적용됩니다. 예를 들어 interval 리터럴 ‘-1 2:03:04’의 음수 부호는 날짜와 시/분/초 부분 모두에 적용됩니다. 텍스트 표현에서는 필드가 서로 다른 부호를 가질 수 있고 각 필드의 부호가 독립적으로 처리되므로, 이 예에서 시/분/초 부분은 양수 값으로 간주됩니다. IntervalStyle이 sql_standard로 설정된 경우, 선행 부호는 (추가 부호가 없으면) 모든 필드에 적용되는 것으로 가정됩니다.

필드가 음수인 경우, 모호함을 피하기 위해 (음수) 부호를 명시적으로 추가하는 것이 좋습니다. 내부적으로 interval 값은 월, 일, 초로 저장됩니다. 이는 월의 일수가 다르고, 일광 절약 시간 구현에 따라 하루가 23시간 또는 25시간이 될 수 있기 때문입니다. 월과 일 필드는 정수이며, 초 필드는 소수로 저장될 수 있습니다. interval은 보통 상수 문자열이나 timestamp 뺄셈으로 생성되므로, 이러한 저장 방식은 대부분의 경우 문제가 되지 않습니다. justify_days와 justify_hours 함수는 정상 범위에서 넘치는 일과 시간을 제어하려는 경우에 유용합니다. 상세 입력 형식과 더 단순한 입력 필드의 일부 필드 값은 소수 부분을 가질 수 있습니다(예: “1.5 week” 또는 “01:02:03.45”). 이러한 입력은 저장을 위해 적절한 수의 월, 일, 초로 변환됩니다. 그 결과 월 또는 일의 소수가 1개월=30일, 1일=24시간이라는 변환 계수를 사용하여 하위 필드에 더해집니다. 예를 들어 “1.5 month”는 1개월 15일입니다. 초만 소수 부분과 함께 표시됩니다. 아래 표는 유효한 interval 입력의 몇 가지 예를 보여줍니다.

표 [[Interval 입력]]

Example	Description
1-2	SQL 표준 형식: 1년 2개월
3 4:05:06	SQL 표준 형식: 3일 4시간 5분 6초
1 year 2 months 3 days 4 hours 5 minutes 6 seconds	일반적인 Postgres 형식: 1년 2개월 3일 4시간 5분 6초
P1Y2M3DT4H5M6S	ISO 8601 “지정자가 있는 형식”: 위와 동일
P0001-02-03T04:05:06	ISO 8601 “대체 형식”: 위와 동일

Interval Output

interval 타입의 출력 형식은 SET intervalstyle 명령을 사용하여 sql_standard, postgres, postgres_verbose, iso_8601의 네 가지 스타일 중 하나로 설정할 수 있습니다. 기본값은 postgres입니다. 아래 표는 각 출력 스타일의 예를 보여줍니다. sql_standard 스타일은 interval 값이 표준 제한(양수와 음수 값이 혼합되지 않은 경우에만 year-month 또는 day-minute)을 만족하는 경우 interval 리터럴 문자열에 대한 SQL 표준 사양을 준수하는 출력을 생성합니다. 그렇지 않으면 부호 혼합 interval의 혼동을 없애기 위해 부호가 명시적으로 추가되며, 출력은 표준 year-month 리터럴 문자열 뒤에 day-time 리터럴 문자열이 오는 것처럼 나타납니다.

표 [interval 출력 스타일 예]

Style Specification	Year-Month Interval	Day-Time Interval	Mixed Interval
sql_standard	1-2	3 4:05:06	-1-2 +3 -4:05:06
postgres	1 year 2 mons	3 days 04:05:06	-1 year -2 mons +3 days -04:05:06
postgres_verbose	@ 1 year 2 mons	@ 3 days 4 hours 5 mins	@ 1 year 2 mons -3 days 4 hours 5
		6 secs	mins 6 secs ago
iso_8601	P1Y2M	P3DT4H5M6S	P-1Y-2M3DT-4H-5M-6S

Boolean Type

표준 SQL 타입 boolean을 제공합니다. “true”와 “false” 상태 외에도, SQL null로 표현되는 세 번째 상태인 “unknown”을 가집니다.

Name	Storage Size	Description
boolean	1 byte	True 또는 False 상태

true 상태에 유효한 리터럴 값은 다음과 같습니다:

TRUE
't'
'true'
'y'
'yes'
'on'
'1'

false 상태에서는 다음 값을 사용할 수 있습니다:

FALSE
'f'
'false'
'n'
'no'
'off'
'0'

선행 또는 후행 빈칸은 무시되며 대소문자를 구분하지 않습니다. TRUE와 FALSE 키워드를 사용하는 것이 권장됩니다. 다음 예는 boolean 값이 문자 t와 f를 사용하여 표시(출력)됨을 보여줍니다.

boolean 타입을 사용하는 예제:

CREATE TABLE test1 (a boolean, b text); 

INSERT INTO test1 VALUES (TRUE, 'sic est'); 
INSERT INTO test1 VALUES (FALSE, 'non est'); 

SELECT * FROM test1;
a |	b
---+---------
t | sic est 
f | non est

SELECT * FROM test1 WHERE a;
a |	b
---+---------
t | sic est

Geometric Types

Geometric 타입은 2차원 공간 객체를 표현하며, 사용 가능한 geometric 타입은 다음과 같습니다:

Name	Storage Size	Description	Representation
point	16bytes	평면상의 점	(x,y)
line	32bytes	무한 직선	{A,B,C}
lseg	32bytes	무한 선분	((x1,y1),(x2,y2))
box	32bytes	사각형 박스	((x1,y1),(x2,y2))
path	16+16n bytes	닫힌 경로(polygon과 유사)	((x1,y1),…)
path	16+16n bytes	열린 경로	[(x1,y1),…]
polygon	40+16n bytes	다각형(닫힌 경로와 유사)	((x1,y1),…)
circle	24bytes	원	<(x,y),r> (중심점과 반지름)

Points

Point는 geometric 타입의 기본 2차원 구성 요소입니다. point 타입의 값은 다음 구문 중 하나를 사용하여 지정됩니다:

( x , y )
  x , y

여기서 x와 y는 각각 부동소수점 좌표입니다. Point는 첫 번째 구문을 사용하여 출력됩니다.

Lines

Line은 선형 방정식 Ax+By+C=0으로 표현되며, 여기서 A와 B는 모두 0이 아닙니다. line 타입의 값은 다음 형식으로 입력 및 출력됩니다.

{ A, B, C }

또는 입력에 다음 형식 중 하나를 사용할 수 있습니다:

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

여기서 (x1, y1)과 (x2, y2)는 직선상의 서로 다른 두 점입니다.

Line Segments

Line segment는 선분의 두 끝점을 이루는 한 쌍의 점으로 표현됩니다. lseg 타입의 값은 다음 구문 중 하나를 사용하여 지정됩니다.

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

여기서 (x1, y1)과 (x2, y2)는 선분의 두 끝점입니다. Line segment는 첫 번째 구문을 사용하여 출력됩니다.

Boxes

Box는 박스의 대각 모서리를 이루는 한 쌍의 점으로 표현됩니다. box 타입의 값은 다음 구문 중 하나를 사용하여 지정됩니다:

( ( x1 , y1 ) , ( x2 , y2 ) )
  ( x1 , y1 ) , ( x2 , y2 )
    x1 , y1   ,   x2 , y2

여기서 (x1, y1)과 (x2, y2)는 박스의 두 대각 모서리입니다. Box는 두 번째 구문을 사용하여 출력됩니다. 두 대각 모서리가 입력으로 제공되지만, 값은 우측 상단 모서리와 좌측 하단 모서리로 저장되도록 필요에 따라 재정렬됩니다.

Paths

Path는 연결된 점들의 목록으로 표현됩니다. 목록의 첫 번째와 마지막 점이 연결되지 않은 것으로 간주되면 열린 경로입니다. 첫 번째와 마지막 점이 연결된 것으로 간주되면 닫힌 경로입니다. path 타입의 값은 다음 구문 중 하나를 사용하여 지정됩니다:

[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( ( x1 , y1 ) , ... , ( xn , yn ) )
  ( x1 , y1 ) , ... , ( xn , yn )
  ( x1 , y1   , ... ,   xn , yn )
    x1 , y1   , ... ,   xn , yn

여기서 점들은 경로를 구성하는 선분의 두 끝점입니다. 대괄호([])는 열린 경로를 나타내고 괄호(())는 닫힌 경로를 나타냅니다. 세 번째부터 다섯 번째 구문처럼 가장 바깥쪽 괄호가 생략되면 닫힌 경로로 간주됩니다. Path는 적절히 두 번째 구문을 사용하여 출력됩니다.

Polygons

Polygon은 점들(다각형의 정점)의 목록으로 표현됩니다. Polygon은 닫힌 경로와 매우 유사하지만, 다르게 저장되며 자체적인 루틴 집합이 지원됩니다. polygon 타입의 값은 다음 구문 중 하나를 사용하여 지정됩니다:

( ( x1 , y1 ) , ... , ( xn , yn ) )
  ( x1 , y1 ) , ... , ( xn , yn )
  ( x1 , y1   , ... ,   xn , yn )
    x1 , y1   , ... ,   xn , yn

여기서 점들은 다각형의 경계를 구성하는 선분의 두 끝점입니다. Polygon은 첫 번째 구문을 사용하여 출력됩니다.

Circles

Circle은 중심점과 반지름으로 표현됩니다. circle 타입의 값은 다음 구문 중 하나를 사용하여 지정됩니다:

< ( x , y ) , r >
( ( x , y ) , r )
  ( x , y ) , r
    x , y   , r

여기서 (x, y)는 원의 중심이고 r은 반지름입니다. Circle은 첫 번째 구문을 사용하여 출력됩니다.

XML Type

XML 타입은 XML 데이터를 저장하는 데 사용됩니다. XML 데이터를 텍스트 필드에 저장하는 대신 입력 값을 well-formed 형식으로 검사할 수 있게 하며, 안전한 연산을 지원하는 함수를 가지고 있습니다. 이 데이터 타입은 configure –with-libxml로 빌드된 설치 환경을 필요로 합니다.

Creating XML Values

문자 데이터로부터 xml 타입 값을 생성하려면 아래 함수를 사용하십시오:

XMLPARSE ( { DOCUMENT | CONTENT } value)

예제:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

XML 타입은 입력 값이 DTD로 지정되더라도 입력 값에 대해 문서 타입 정의(DTD)를 검사하지 않습니다. 다른 XML 스키마 언어(예: XML schema)의 유효성 검사를 지원하지 않습니다.

xml에서 문자열 값을 생성하는 역연산은 다음 함수를 사용합니다:

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

type은 character, character varying, text(또는 그 별칭)일 수 있습니다. SQL 표준에 따르면 이것이 XML과 character 타입을 변환하는 유일한 방법이지만, 값을 단순히 캐스팅할 수도 있습니다.

DOCUMENT와 CONTENT의 선택은 “XML Option” 세션 설정 매개변수에 의해 결정되며, 이는 문자열 값을 xml 타입으로 캐스팅하거나 XMLPARSE 또는 XMLSERIALIZE를 거치지 않고 XMLSERIALIZE를 통과할 때 표준 명령을 사용하여 설정할 수 있습니다.

SET xmloption TO { DOCUMENT | CONTENT };

기본값이 CONTENT이므로 모든 형식의 XML 데이터가 허용됩니다.

참고: 기본 XML 옵션 설정을 사용하는 경우 XML 콘텐츠 조각의 정의가 XML을 허용하지 않으므로, DTD를 포함하는 문자열을 XML 형식으로 직접 변환할 수 없습니다. XMLPARSE를 사용하거나 XML 옵션을 변경해야 합니다.

Encoding Handling

클라이언트에서 여러 문자 인코딩을 처리할 때와 XML 데이터가 이를 통해 전달될 때 주의해야 합니다. 텍스트 모드를 사용하여 서버에 쿼리를 전달하고 쿼리 결과를 클라이언트에 전달하는 경우(일반 모드), 클라이언트와 서버 간에 전달되는 모든 문자 데이터를 변환하고 각 끝에서 문자 인코딩을 역으로 변환합니다. 여기에는 위 예에서 보인 것처럼 XML 값의 텍스트 표현이 포함됩니다. 일반적으로 이는, 클라이언트와 서버 간 전송 중에 문자 데이터가 다른 인코딩으로 변환되더라도 내장된 인코딩 선언은 변경되지 않으므로, XML 데이터에 포함된 인코딩 선언이 무효화될 수 있음을 의미합니다. 이 동작에 대처하기 위해, XML 타입 입력에 존재하는 문자열에 포함된 인코딩 선언은 무시되고 CONTENT는 현재 서버 인코딩으로 간주됩니다. 결과적으로 XML 데이터 문자열은 적절한 처리를 위해 현재 클라이언트 인코딩을 통해 클라이언트로부터 전송되어야 합니다. 문서를 현재 클라이언트 인코딩으로 변환하거나 서버로 보내기 전에 클라이언트 인코딩을 적절히 조정하는 것은 클라이언트의 책임입니다. 출력의 XML 타입 값에는 인코딩 선언이 없으며, 클라이언트는 모든 데이터를 현재 클라이언트 인코딩으로 간주합니다.

바이너리 모드를 사용하여 쿼리 매개변수를 서버에 전달하고 쿼리 결과를 클라이언트로 다시 전달하는 경우, 문자 집합 변환이 수행되지 않아 작업이 더 어려워집니다. 이 경우 XML 데이터의 인코딩 선언이 준수됩니다. 없는 경우 데이터는 UTF-8로 가정됩니다(이는 XML 표준에서 요구하는 것이며 UTF-16은 지원하지 않는다는 점에 유의하십시오). 출력 시, 클라이언트 인코딩이 UTF-8이 아니면 데이터는 클라이언트 인코딩을 지정하는 인코딩 선언을 가지며, UTF-8이면 인코딩 선언이 생략됩니다.

XML 데이터 처리는 XML 데이터 인코딩, 클라이언트 인코딩, 서버 인코딩이 모두 동일할 때 오류가 발생할 가능성이 적고 더 효율적입니다. XML 데이터는 내부적으로 UTF-8로 처리되므로, 서버도 UTF-8일 때 계산이 가장 효율적입니다.

참고: 서버 인코딩이 UTF-8이 아닌 경우 일부 XML 관련 함수는 비ASCII 데이터에서 동작하지 않을 수 있습니다. 이는 특히 xpath ()에서 문제로 알려져 있습니다.

Accessing XML Values

XML 데이터 타입은 비교 연산자를 제공하지 않는다는 점에서 독특합니다. 이는 XML 데이터에 대해 잘 정의되고 보편적으로 유용한 비교 알고리즘이 없기 때문입니다. 결과적으로 xml 열을 검색 값과 비교하여 행을 검색할 수 없습니다. XML은 별도의 키 필드(예: ID)와 함께 사용해야 합니다. XML 값을 비교하는 대안적 해결책은 먼저 문자열로 변환하는 것입니다. 그러나 문자열 비교는 유용한 XML 비교 방법과는 관련이 없다는 점에 유의하십시오.

XML 데이터 타입에는 비교 연산자가 없으므로, 이 타입의 열에 직접 인덱스를 생성할 수 없습니다. XML 데이터를 빠르게 검색해야 하는 경우 가능한 해결책으로는 문자열 타입으로 캐스팅하여 표현식을 인덱싱하거나 XPath 표현식을 인덱싱하는 방법이 있습니다. 물론 실제 쿼리에서는 인덱싱된 표현식에 맞게 검색을 조정해야 합니다.

또한 전문 검색 기능을 사용하여 XML 데이터의 전체 문서 검색 속도를 높일 수 있습니다. 그러나 필요한 전처리 지원은 아직 제공되지 않습니다.

JSON Types

JSON 데이터 타입은 RFC 7159에 명시된 대로 JavaScript Object Notation(JSON) 데이터를 저장하기 위한 것입니다. 해당 데이터는 text로도 저장할 수 있지만, JSON 데이터 타입은 저장된 각 값이 JSON 규칙에 따라 유효하도록 강제하는 이점이 있습니다. 또한 이러한 타입의 저장된 데이터에 사용할 수 있는 다수의 JSON 전용 함수와 연산자가 있습니다.

JSON 데이터 타입에는 JSON과 JSONB의 두 가지가 있습니다. 이 두 타입은 거의 동일한 값 집합을 입력으로 받습니다. 둘 사이의 실질적인 차이는 효율성입니다. JSON 데이터 타입은 입력 텍스트의 정확한 복사본을 저장하며, 그 처리 함수는 실행할 때마다 다시 파싱해야 합니다. 반면 JSONB 데이터는 분해된 바이너리 형식으로 저장되어, 추가되는 변환 오버헤드로 인해 입력 시에는 약간 느리지만, 다시 파싱할 필요가 없으므로 처리 중에는 훨씬 빠릅니다. JSONB는 또한 인덱싱을 지원하는데, 이는 상당한 이점이 될 수 있습니다.

JSON 타입은 입력 텍스트의 정확한 복사본을 저장하며, 토큰 사이의 구문적으로 유의미하지 않은 공백과 JSON 객체의 키 순서를 보존합니다. 또한 값 내부의 JSON 객체가 동일한 키를 두 번 이상 포함하는 경우, 모든 키/값 쌍이 유지됩니다. (처리 함수는 마지막 값이 유효한 것으로 가정합니다.) 반대로 JSONB는 공백, 객체 키의 순서, 중복 객체 키를 유지하지 않습니다. 입력에 중복 키가 지정되면 마지막 값만 유지됩니다. 일반적으로 객체 키 순서에 대한 기존 가정과 같은 특정한 이유가 없는 한, 대부분의 애플리케이션은 JSON 데이터를 JSONB로 저장해야 합니다. 데이터베이스당 하나의 문자 집합 인코딩만 허용됩니다. 따라서 데이터베이스 인코딩이 UTF8이 아닌 경우 JSON 타입이 JSON 사양을 엄격하게 준수하는 것은 불가능합니다. 데이터베이스 인코딩으로 표현할 수 없는 문자를 직접 포함하려는 시도는 실패합니다. 반면 UTF8로는 표현할 수 없지만 데이터베이스 인코딩으로는 표현할 수 있는 문자는 허용됩니다. RFC 7159는 JSON 문자열이 \uXXXX로 표시되는 유니코드 이스케이프 시퀀스를 포함하는 것을 허용합니다. JSON 타입 입력 함수에서 유니코드 이스케이프는 데이터베이스 인코딩에 관계없이 허용되며, 구문적 정확성(즉, \u 뒤에 16진수 네 자리)을 검사받습니다. 그러나 JSONB의 입력 함수는 더 엄격합니다. 데이터베이스 인코딩이 UTF8이 아닌 경우, 비ASCII 문자(U+007F 이상)에 대한 유니코드 이스케이프를 허용하지 않습니다. 또한 JSONB 타입에서는 \u0000을 거부하고(text 타입으로 표현할 수 없으므로) 유니코드 기본 다국어 평면(BMP)을 벗어나는 문자를 지정하는 유니코드 서로게이트 쌍의 사용이 올바라야 합니다. 이는 유효한 유니코드 이스케이프에 해당하는 ASCII 또는 UTF8 문자로 변환되어 저장됩니다(겹치는 서로게이트 쌍은 단일 문자로 포함됨).

참고: 많은 JSON 처리 함수가 유니코드 이스케이프를 일반 문자로 변환하므로, 입력이 jsonb가 아닌 json 타입이더라도 오류를 반환합니다. 일반적으로 가능한 한 JSON에서 유니코드 이스케이프를 UTF8 데이터베이스 인코딩과 혼합하지 않는 것이 가장 좋습니다.

텍스트 JSON 입력을 JSONB로 변환하면, RFC 7159에 설명된 기본 타입이 Table 8-23에 나와 있는 것처럼 AgensGraph의 기본(native) 타입에 효과적으로 매핑됩니다. 따라서 JSON 타입과 JSON에는 (추상적으로도) 적용되지 않으면서 유효한 JSONB 데이터를 구성하는 몇 가지 지역적 제약이 추가됩니다. 이는 기본 데이터 타입으로 표현될 수 있는지 여부에 대한 제한에 해당합니다. 특히 JSONB는 AgensGraph numeric 데이터 타입의 범위를 벗어나면서 JSON을 벗어나지 않는 숫자를 거부합니다. 해당 구현이 정의되어 있는 제약은 RFC 7159에서 허용됩니다. 그러나 실제로는 JSON의 number 기본 타입이 IEEE 754 배정밀도 부동소수점을 표현하는 데 일반적으로 사용되므로(RFC 7159에서 명시적으로 예상되고 허용됨), 이 문제는 다른 구현에서 훨씬 더 흔합니다. 시스템과의 교환 형식으로 JSON을 사용하는 경우, AgensGraph에 저장된 원본 데이터와 비교할 때 숫자 정밀도 손실의 위험을 고려해야 합니다. 반대로, 표에 나와 있는 것처럼 해당 AgensGraph 타입에는 적용되지 않는 JSON 기본 타입의 입력 타입에 대한 몇 가지 지역적 제약이 있습니다.

[JSON 기본 타입과 해당 AgensGraph 타입]

JSON primitive type	Type	Notes
string	text	데이터베이스 인코딩이 UTF8이 아니면, \u0000은 비ASCII 유니코드 이스케이프이므로 허용되지 않음
number	numeric	NaN과 infinity 값은 허용되지 않음
boolean	boolean	소문자 표기 true와 false만 허용됨
null	(없음)	SQL NULL과는 개념적으로 다름

JSON Input and Output Syntax

JSON 데이터 타입의 입력/출력 구문은 RFC 7159에 따라 지정할 수 있습니다. 다음 예는 유효한 모든 JSON(또는 JSONB) 표현식을 보여줍니다.

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

앞서 언급한 것처럼, JSON 값이 입력되고 추가 처리 없이 출력되면 JSON은 입력과 동일한 텍스트를 출력하고, JSONB는 구문적으로 유의미하지 않은 내용(예: 공백)을 유지하지 않습니다. 아래에 제시된 차이점을 참조하십시오.

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;

결과 :

                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;

결과 :

                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

한 가지 유의할 점은, 구문적으로 유의미하지는 않지만 JSONB에서는 숫자가 기본 numeric 타입에 따라 출력된다는 것입니다. 실제로 이는 “e”로 표시된 숫자가 출력될 때 생략됨을 의미합니다. 다음은 그 예입니다:

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;

결과 :

         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

그러나 이 예에서 보듯이, JSONB는 구문적으로 유의미하지 않더라도 확인을 위해 후행 0을 보존합니다.

Designing JSON documents effectively

JSON으로 데이터를 표현하는 것은, 요구 사항이 가변적인 환경에서 강제되는 전통적인 관계형 데이터 모델보다 훨씬 더 유연할 수 있습니다. 두 방법은 동일한 애플리케이션에서 공존하며 서로를 보완할 수 있습니다. 그러나 최대한의 유연성이 필요한 애플리케이션에서는 JSON 문서가 다소 고정된 구조를 갖는 것이 권장됩니다. 구조가 일반적으로 적용되는 것은 아니지만(일부 비즈니스 규칙을 선언적으로 적용할 수도 있습니다), 예측 가능한 구조를 사용하면 테이블의 “문서”(데이텀) 집합을 유용하게 요약하는 쿼리를 작성하기가 더 쉬워집니다. JSON 데이터는 테이블에 저장될 때 다른 데이터 타입과 동일한 동시성 제어 고려 사항의 영향을 받습니다. 큰 문서를 저장하는 것이 가능하더라도, 업데이트는 전체 행에 대한 행 수준 잠금을 획득한다는 점에 유의해야 합니다. 업데이트 트랜잭션 간의 잠금 경합을 줄이기 위해 JSON 문서를 관리 가능한 크기로 제한하는 것을 고려해야 합니다. 원칙적으로 JSON 문서는, 비즈니스 규칙이 가리키는 원자적 데이터가 각각 독립적으로 수정될 수 있는 더 작은 데이텀으로 분할될 수 없음을 나타냅니다.

JSONB Containment and Existence

포함 관계 테스트는 JSONB의 중요한 기능입니다. JSON 타입에는 이와 유사한 기능 집합이 없습니다. 포함 관계 테스트는 하나의 JSONB 문서가 다른 문서에 포함되어 있는지를 검사합니다. 이 예는 별도로 표시된 경우를 제외하고 true를 반환합니다.

-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb 
       @> '{"version": 9.4}'::jsonb;

-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false

-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields false

-- A top-level key and an empty object is contained:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

일반적인 원칙은 포함되는 객체의 구조와 데이터 내용이 포함하는 객체와 일치한다는 것입니다. 이는 포함하는 객체에서 일치하지 않는(불일치하는) 배열 요소나 객체 키/값 쌍을 제거한 후에 가능할 수 있습니다. 그러나 포함 관계를 비교할 때 배열 요소의 순서는 중요하지 않으며, 중복된 배열 요소는 실제로 한 번만 고려됩니다.

구조가 일치해야 한다는 일반 원칙의 특별한 예외로, 배열은 기본 값을 포함할 수 있습니다.

-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

JSONB에는 포함 관계 주제의 변형인 존재(existence) 연산자가 있으며, 이는 (text 값으로 지정된) 문자열이 JSONB 값의 최상위 수준에 객체 키 또는 배열 요소로 나타나는지를 검사합니다. 이 예는 별도로 표시된 경우를 제외하고 true를 반환합니다.

-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false

-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

-- A string is considered to exist if it matches a primitive JSON string:
SELECT '"foo"'::jsonb ? 'foo';

배열과 달리 JSON 객체는 검색을 위해 내부적으로 최적화되어 있으며 선형 검색을 수행하지 않습니다. 이는 관련 키와 요소가 많을 때, 포함 관계나 존재를 테스트하는 데 있어 배열보다 더 적합함을 의미합니다.

JSONB Indexing

GIN 인덱스를 사용하면 다수의 JSONB 문서(데이텀)에서 키 또는 키/값 쌍을 효율적으로 검색할 수 있습니다. 성능과 유연성의 트레이드오프가 다른 두 가지 GIN “연산자 클래스”가 제공됩니다. JSONB의 기본 GIN 연산자 클래스는 @>, ?, ?&, ?|와 같은 연산자를 사용하는 쿼리를 지원합니다. 다음은 이 연산자 클래스 내에서 인덱스를 생성하는 예입니다:

CREATE INDEX idxgin ON api USING GIN (jdoc);

기본 GIN 연산자 클래스가 아닌 jsonb_path_ops는 @> 연산자의 인덱싱만 지원합니다. 다음은 이 연산자 클래스 내에서 인덱스를 생성하는 예입니다:

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

문서화된 스키마 정의를 사용하여 서드파티 웹 서비스에서 검색한 JSON 문서를 저장하는 테이블 예를 살펴보겠습니다. 일반적인 문서는 다음과 같습니다:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

이 문서를 api라는 테이블의 jdoc이라는 JSONB 열에 저장합니다. 이 열에 GIN 인덱스가 생성되면 다음 쿼리가 인덱스를 사용합니다.

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

그러나 연산자 “?”는 인덱싱될 수 있지만, 인덱싱된 열 jdoc에 직접 적용되지는 않습니다. 따라서 다음 쿼리에서는 인덱스를 사용할 수 없습니다.

-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

그래도 표현식 인덱스를 적절히 사용하면 위 쿼리에서 인덱스를 사용할 수 있습니다. “tags” 키 내의 특정 항목에 대한 쿼리가 흔한 경우, 아래와 같은 인덱스 정의가 유용합니다.

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

WHERE 절 jdoc->’tags’ ? ‘qui’는 인덱싱 가능한 연산자 “?”의 적용이며, 인덱싱된 표현식 jdoc -> ‘tags’가 인식됩니다.

또 다른 쿼리 방법은 포함 관계를 최대한 활용하는 것입니다. 예를 들어 jdoc 열에 대한 간단한 GIN 인덱스가 이 쿼리를 지원할 수 있습니다.

-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

그러나 이러한 인덱스는 jdoc 열의 모든 키와 값의 복사본을 저장하는 반면, 이전 예의 표현식 인덱스는 tags 키 아래에서 발견된 데이터만 저장합니다. 단순 인덱스 방식이 훨씬 더 유연한 것은 사실이지만(임의의 키에 대한 쿼리를 지원하므로), 대상이 지정된 표현식 인덱스는 단순 인덱스보다 훨씬 작고 검색 속도가 빠릅니다.

jsonb_path_ops 연산자 클래스는 @> 연산자를 사용하는 쿼리만 지원하지만, 기본 연산자 클래스인 jsonb_ops보다 우수한 성능상의 이점을 가집니다. 동일한 데이터에서 jsonb_path_ops 인덱스의 크기와 검색 특이성은 일반적으로 jsonb_ops 인덱스보다 각각 훨씬 작고 우수합니다. 이는 데이터에 자주 나타나는 키를 포함하는 쿼리에서 특히 그렇습니다. 따라서 이 클래스를 사용한 검색은 일반적으로 기본 연산자 클래스를 사용하는 것보다 훨씬 우수합니다.

jsonb_ops와 jsonb_path_ops GIN 인덱스의 기술적 차이는, 전자가 데이터의 각 키와 값에 대해 독립적인 인덱스 항목을 생성하는 반면, 후자는 데이터 값에 대해서만 인덱스 항목을 생성한다는 점입니다. 기본적으로 각 jsonb_path_ops 인덱스 항목은 값과 그에 이르는 키들의 해시입니다. 예로 인덱스 {“foo”: {“bar”: “baz”}를 살펴보겠습니다. foo, bar, baz 세 가지를 모두 하나의 해시 값으로 결합하여 단일 인덱스 항목이 생성됩니다. 따라서 이러한 구조를 찾는 제약 쿼리는 매우 구체적인 인덱스 검색이 됩니다. 그러나 foo가 키로 나타날지 여부는 전혀 알 수 없습니다. 반대로 jsonb_ops 인덱스는 foo, bar, baz를 각각 나타내는 세 개의 인덱스 항목을 생성합니다. 그런 다음 제약 쿼리를 실행하기 위해 이 세 항목을 모두 포함하는 행을 조회합니다. GIN 인덱스는 AND 검색을 매우 효율적으로 수행할 수 있지만, 특히 세 인덱스 항목 중 하나를 포함하는 행이 많은 경우, 동등한 jsonb_path_ops 검색보다 덜 구체적이고 느립니다. jsonb_path_ops 방식의 단점은 {“a”: {}}와 같이 값을 포함하지 않는 JSON 구조에 대해서는 인덱스 항목을 생성하지 않는다는 것입니다. 해당 구조를 포함하는 문서 검색이 요청되면 전체 인덱스 스캔이 필요하며, 이는 매우 느립니다. 따라서 jsonb_path_ops는 빈번한 검색을 수행하는 애플리케이션에는 적합하지 않습니다. JSONB는 또한 btree 및 hash 인덱스를 지원하는데, 이는 전체 JSON 문서의 동등성을 확인할 때만 유용합니다. JSONB 데이터의 B-tree 정렬 순서는 그다지 중요하지 않지만, 완전성을 위해 필요합니다.

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

쌍의 개수가 동일한 객체는 다음 순서로 비교됩니다:

key-1, value-1, key-2 ...

객체 키는 저장 순서로 비교됩니다. 특히 짧은 키를 긴 키 앞에 저장하면 직관적이지 않은 결과가 발생합니다:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

마찬가지로, 요소 개수가 동일한 배열은 다음 순서로 비교됩니다:

element-1, element-2 ...

기본(native) JSON 값은 기본 데이터 타입에도 적용되는 비교 규칙을 사용하여 비교됩니다. 문자열은 기본 데이터베이스 collation을 사용하여 비교됩니다.

Arrays

Array를 사용하면 테이블의 열 타입을 가변 길이 다차원 배열로 정의할 수 있습니다. 내장 또는 사용자 정의 기본 타입, enum 타입, 또는 복합 타입의 배열을 생성할 수 있습니다. 도메인의 배열은 아직 지원되지 않습니다.

Declaration of Array Types

배열 타입의 사용을 설명하기 위해 다음과 같이 테이블을 생성하겠습니다:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

표시된 것처럼, 배열 데이터 타입은 배열 요소의 데이터 타입 이름에 대괄호([])를 추가하여 명명됩니다. 위 명령은 text 타입의 열(name), 직원의 분기별 급여를 나타내는 integer 타입의 1차원 배열(pay_by_quarter), 직원의 주간 일정을 나타내는 text의 2차원 배열(schedule)을 가진 sal_emp라는 테이블을 생성합니다. CREATE TABLE에서는 배열의 정확한 크기를 지정할 수 있습니다. 예를 들어:

CREATE TABLE tictactoe ( 
squares	integer[3][3]
);

그러나 현재 구현은 제공된 배열 크기 제한을 무시합니다. 즉, 길이가 지정되지 않은 배열과 동일하게 동작합니다. 현재 구현은 선언된 차원 수를 강제하지 않습니다. 특정 요소 타입의 배열은 크기나 차원 수에 관계없이 동일한 타입으로 간주됩니다. 따라서 CREATE TABLE에서 배열 크기나 차원 수를 선언하는 것은 단순히 문서화일 뿐이며 런타임 동작에 영향을 주지 않습니다.

ARRAY 키워드를 활용하여 1차원 배열에 대해 SQL 표준을 준수하는 대체 구문을 사용할 수 있습니다. pay_by_quarter는 다음과 같이 정의될 수도 있었습니다:

pay_by_quarter	integer ARRAY[4],

배열 크기를 지정하지 않으면:

pay_by_quarter  integer ARRAY,

그러나 어떤 경우에도 크기 제약은 강제되지 않습니다.

Array Value Input

배열 값을 리터럴 상수로 생성하려면, 값을 중괄호 안에 넣고 쉼표로 구분합니다. 값 앞뒤에 큰따옴표를 사용할 수 있으며, 값에 쉼표나 중괄호가 포함된 경우에는 큰따옴표를 사용해야 합니다. 배열 상수의 일반적인 형태:

'{ val1 delim val2 delim ... }'

위 예에서 delim은 pg_type 항목에 기록된 타입의 구분자입니다. AgensGraph 배포판에서 제공하는 모든 표준 데이터 타입은, 세미콜론(;)을 사용하는 box 타입을 제외하고 쉼표를 사용합니다. 각 val은 배열 요소 타입의 상수 또는 하위 배열입니다. 예를 들어:

'{{1,2,3},{4,5,6},{7,8,9}}'

이 상수는 세 개의 integer 하위 배열로 구성된 2차원 3x3 배열입니다. 배열 상수의 요소를 NULL로 설정하려면, 요소 값으로 NULL을 작성합니다. (NULL의 대소문자는 변경할 수 있습니다.) 실제 문자열 값 “NULL”을 원하는 경우, NULL 앞뒤에 큰따옴표를 사용합니다. (상수는 처음에 문자열로 처리되어 배열 입력 변환 루틴에 전달되며, 이때 명시적인 타입 지정이 필요할 수 있습니다.)

다음은 INSERT 구문과 그 실행 결과의 예입니다:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

SELECT * FROM sal_emp;

결과 :

 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

다차원 배열은 각 차원에 대해 일치하는 범위를 가져야 하며, 불일치가 있으면 다음 오류가 발생합니다:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

ARRAY의 생성자 구문도 사용할 수 있습니다.

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

배열 요소는 일반적인 SQL 상수 또는 표현식입니다. 문자열 리터럴은 배열 리터럴에서와 같이 큰따옴표 대신 작은따옴표로 묶입니다.

Accessing Arrays

위에서 생성한 테이블에서 몇 가지 쿼리를 실행할 수 있습니다. 배열의 단일 요소에 접근합니다. 다음 쿼리는 2분기에 급여가 변경된 직원의 이름을 검색합니다.

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

결과 :

 name
-------
 Carol
(1 row)

배열 번호는 대괄호로 묶이며, 1부터 시작하는 배열 표기법을 사용합니다. 즉, n개 요소의 배열은 array[1]에서 시작하여 array[n]에서 끝납니다.

이 쿼리는 3분기에 대한 모든 직원의 급여를 검색합니다.

SELECT pay_by_quarter[3] FROM sal_emp;

결과 :

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

또한 배열이나 하위 배열의 임의의 직사각형 슬라이스에 접근할 수 있습니다. 배열 슬라이스는 하나 이상의 배열 차원에 대해 lower-bound:upper-bound를 사용하여 표현됩니다.

예를 들어, 다음 쿼리는 주의 처음 이틀 동안 Bill의 일정의 첫 번째 항목을 검색합니다.

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

결과 :

        schedule
------------------------
 {{meeting},{training}}
(1 row)

한 차원을 슬라이스로 생성하면(예: 콜론을 포함하면), 모든 차원이 슬라이스로 처리됩니다. 콜론 없이 단일 숫자만 있는 차원은 1부터 지정된 숫자까지로 처리됩니다. 예를 들어 [2]는 다음과 같이 [1: 2]로 처리됩니다:

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

결과 :

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

슬라이스가 아닌 경우의 혼동을 피하기 위해, [2] [1:1]보다는 [1:2] [1:1]과 같이 모든 차원에 슬라이스 구문을 사용하는 것이 가장 좋습니다.

슬라이스 지정자의 lower-bound 또는 upper-bound를 생략할 수 있습니다. 누락된 경계는 다음 예와 같이 배열의 하한 또는 상한으로 대체됩니다:

SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';

결과 :

        schedule
------------------------
 {{lunch},{presentation}}
(1 row)

SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';

결과 :

        schedule
------------------------
 {{meeting},{training}}
(1 row)

배열 첨자 표현식은 배열 자체나 첨자 표현식이 null이면 null을 반환합니다. 또한 첨자가 배열의 범위를 벗어나면 null이 반환됩니다(이 경우 오류가 발생하지 않습니다). 예를 들어 schedule이 현재 차원 [1:3] [1:2]일 때 참조되는 schedule [3][3]은 NULL을 출력합니다. 마찬가지로, 잘못된 개수의 첨자를 참조하는 배열은 오류가 아니라 null을 출력합니다.

배열 슬라이스 표현식도 마찬가지로 배열 자체나 첨자 표현식이 null이면 null을 반환합니다. 그러나 다른 경우(예: 현재 배열의 범위를 완전히 벗어난 배열 슬라이스를 선택하는 경우), 슬라이스 표현식은 null 대신 비어 있는(0차원) 배열을 출력합니다. 요청된 슬라이스가 배열 경계와 부분적으로 겹치면, null을 반환하는 대신 겹치는 영역으로 축소됩니다.

배열 값의 현재 차원은 array_dims 함수를 사용하여 검색할 수 있습니다.

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

결과 :

 array_dims
------------
 [1:2][1:2]
(1 row)

array_dims는 text 결과를 생성하는데, 이는 사람이 읽기에는 더 좋지만 프로그램에는 불편합니다. 차원은 지정된 배열 차원의 상한과 하한을 각각 반환하는 array_upper와 array_lower를 사용하여 검색할 수도 있습니다.

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

array_length는 지정된 배열 차원의 길이를 반환합니다.

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

결과 :

 array_length
--------------
            2
(1 row)

cardinality는 모든 차원의 배열에 있는 요소의 총 개수를 반환합니다. 이는 실제로 unnest 호출에 의해 생성되는 행의 수입니다.

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

결과 :

 cardinality
-------------
           4
(1 row)

Modifying Arrays

배열 값을 전체로 업데이트할 수 있습니다;

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

또는 ARRAY 표현식 구문을 사용합니다.

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

배열은 단일 요소를 업데이트할 수 있습니다;

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

또는 부분적으로만 업데이트합니다.

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

lower-bound 또는 upper-bound를 생략한 슬라이스 구문은 NULL이거나 비어 있지 않은 배열 값을 업데이트할 때만 사용할 수 있습니다.

저장된 배열 값은 아직 존재하지 않는 요소에 할당하여 확장할 수 있습니다. 기존 요소와 새로 할당된 요소 사이의 위치는 null로 채워집니다. 예를 들어 배열 myarray에 현재 4개의 요소가 있는 경우, myarray [6]에 할당하는 업데이트 후에는 6개의 요소를 갖게 됩니다. myarray [5]에는 null이 포함됩니다. 현재 이러한 방식의 확장은 (다차원 배열이 아닌) 1차원 배열에만 허용됩니다. 첨자 할당을 사용하면 첨자가 1에서 시작하지 않는 배열을 생성할 수 있습니다. 예를 들어 첨자 값이 -2부터 7까지인 배열을 생성하려면 myarray [-2:7]에 할당할 수 있습니다.

새 배열 값은 연결 연산자 ||를 사용하여 생성할 수도 있습니다.

SELECT ARRAY[1,2] || ARRAY[3,4] as array;

결과 :

   array
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]] as array;

결과 :

        array
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

연결 연산자를 사용하면 단일 요소를 1차원 배열의 시작 또는 끝에 추가할 수 있습니다. 이는 두 개의 N차원 배열(또는 N차원 배열과 N+1차원 배열)을 수용합니다.

다음 예와 같이 단일 요소를 1차원 배열의 시작 또는 끝에 추가하면, 결과는 배열 피연산자와 동일한 하한 첨자로 채워진 배열입니다.

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);

결과 :

 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);

결과 :

 array_dims
------------
 [1:3]
(1 row)

차원 수가 동일한 두 배열을 연결하면, 결과는 왼쪽 피연산자의 외부 차원의 하한을 유지합니다. 그 결과 다음 예에서 보듯이 왼쪽 피연산자의 모든 요소로 이루어진 배열 뒤에 오른쪽 피연산자의 모든 요소가 따라옵니다:

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);

결과 :

 array_dims
------------
 [1:5]
(1 row)

결과 :

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);

결과 :

 array_dims
------------
 [1:5][1:2]
(1 row)

N차원 배열을 N+1차원 배열의 시작에서 끝으로 추가하면, 결과는 위의 요소 배열 예와 유사합니다. 다음 예에서 보듯이 각 N차원 하위 배열은 본질적으로 N+1차원 배열의 외부 차원의 요소입니다:

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);

결과 :

 array_dims
------------
 [1:3][1:2]
(1 row)

또한 array_prepend, array_append, array_cat 함수를 사용하여 함수를 생성할 수 있습니다. 처음 두 함수는 1차원 배열만 지원하는 반면, array_cat은 다차원 배열을 지원합니다. 위에서 논의한 연결 연산자는 이러한 함수를 직접 사용하는 것보다 선호됩니다. 사실 이러한 함수는 주로 연결 연산자를 구현할 때 사용하기 위해 존재합니다. 그러나 사용자 정의 집계를 생성할 때도 유용할 수 있습니다. 예를 들어:

SELECT array_prepend(1, ARRAY[2,3]);

결과 :

 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);

결과 :

 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);

결과 :

 array_cat
-----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);

결과 :

      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);

결과 :

      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

간단한 경우에는 이러한 함수를 직접 사용하는 것보다 위에서 설명한 연결 연산자가 선호됩니다. 그러나 연결 연산자는 세 가지 경우를 모두 처리하도록 오버로드되어 있으므로, 이러한 함수 중 하나를 사용하면 모호함을 피하는 데 도움이 될 수 있습니다.

SELECT ARRAY[1, 2] || '{3, 4}' as array;  -- the untyped literal is taken as an array

결과 :

   array
-----------
 {1,2,3,4}

SELECT ARRAY[1, 2] || '7';                 -- so is this one
ERROR:  malformed array literal: "7"

SELECT ARRAY[1, 2] || NULL as array;        -- so is an undecorated NULL

결과 :

 array
-------
 {1,2}
(1 row)

SELECT array_append(ARRAY[1, 2], NULL);    -- this might have been meant

결과 :

 array_append
--------------
 {1,2,NULL}

위 예에서 파서는 연결 연산자의 한쪽에서 integer 배열을, 다른 쪽에서 불확정 타입의 상수를 봅니다. 상수 타입을 분석하는 데 사용되는 경험적 방법은 그것이 연산자의 다른 입력과 동일한 타입(이 경우 integer 배열)이라고 가정하는 것입니다. 따라서 연결 연산자는 array_append가 아니라 array_cat으로 간주됩니다. 그것이 잘못된 선택이라면, 상수를 배열의 요소 타입으로 캐스팅하여 수정할 수 있습니다. array_append를 사용하는 것이 바람직한 해결책일 수 있습니다.

Searching in Arrays

배열의 값을 검색하려면 각 값을 확인해야 합니다. 배열의 크기를 안다면 이렇게 할 수 있습니다. 예를 들어:

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

그러나 큰 배열에서는 이 방법이 곧 번거로워지고 배열 크기를 알 수 없을 때는 도움이 되지 않습니다. 위 쿼리는 다음으로 대체할 수 있습니다:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

또한 배열 값이 10000과 같은 모든 행을 찾을 수 있습니다:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

또는 generate_subscripts 함수를 사용할 수 있습니다.

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

또한 왼쪽 피연산자가 오른쪽 피연산자와 겹치는지 확인하는 && 연산자를 사용하여 배열을 검색할 수도 있습니다.

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

또한 array_position과 array_positions 함수를 사용하여 배열의 특정 값을 검색할 수 있습니다. 전자는 배열에서 첫 번째 값의 첨자를 반환하고, 후자는 배열에 있는 해당 값의 모든 첨자로 이루어진 배열을 반환합니다.

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');

결과 :

 array_positions
-----------------
 2

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);

결과 :

 array_positions
-----------------
 {1,4,8}

Array Input and Output Syntax

배열 값의 외부 텍스트 표현은 배열 요소 타입에 대한 I/O 변환 규칙과 배열 구조를 나타내는 장식에 따라 해석되는 항목으로 구성됩니다. 장식은 배열 값의 양쪽 끝에 있는 중괄호와 인접한 항목 사이의 구분자로 구성됩니다. 구분자는 보통 쉼표(,)이지만 다른 것일 수도 있습니다. 이는 배열의 요소 타입에 대한 typedelim 설정에 의해 결정됩니다. 모든 표준 데이터 타입은, 세미콜론(;)을 사용하는 box 타입을 제외하고 쉼표를 사용합니다. 다차원 배열에서는 각 차원(행, 평면, 큐브 등)이 자체적인 중괄호 수준을 가지며, 인접한 중괄호 엔티티 수준 사이에 구분자를 사용해야 합니다.

배열 출력 루틴은 요소 값이 빈 문자열이거나, 중괄호, 구분자, 큰따옴표, 백슬래시, 공백을 포함하거나, NULL이라는 단어와 일치하는 경우 요소 값 앞뒤에 큰따옴표를 사용합니다. 요소 값에 포함된 큰따옴표와 백슬래시는 백슬래시로 이스케이프됩니다. numeric 데이터 타입의 경우 큰따옴표가 나타나지 않는다고 가정해도 안전합니다. 그러나 text 데이터 타입의 경우 따옴표의 유무에 대처해야 합니다. 기본적으로 배열의 차원에서 하한 인덱스 값은 1로 설정됩니다. 다른 하한을 사용하여 배열을 지정하는 경우, 배열 내용을 생성하기 전에 배열 첨자의 범위를 명시적으로 지정할 수 있습니다. 이러한 종류의 장식은 각 배열 차원의 하한/상한 앞뒤의 대괄호([])와 구분자로서의 콜론(:)으로 구성됩니다. 배열 차원 장식 뒤에는 등호(=)가 옵니다.

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

결과 :

 e1 | e2
----+----
  1 |  6
(1 row)

배열 출력 루틴은 하한이 둘 이상인 경우에만 결과에 명시적 차원을 포함합니다.

요소를 위해 생성된 값이 NULL인 경우(변형의 경우), 해당 요소는 NULL로 간주됩니다. 따옴표나 백슬래시가 있으면 이것이 비활성화되어 리터럴 문자열 값 “NULL”의 입력이 허용됩니다. 또한 array_nulls 설정 매개변수를 off로 설정하여 NULL이 NULL로 인식되는 것을 방지할 수 있습니다. 앞서 언급한 것처럼, 배열 값을 생성할 때 개별 배열 요소 앞뒤에 큰따옴표를 사용할 수 있습니다. 이는 요소 값이 배열 값 파서에 의해 혼동될 수 있는 경우에 해야 합니다. 예를 들어, 중괄호, 쉼표(또는 데이터 타입 구분자), 큰따옴표, 백슬래시, 선행 또는 후행 공백을 포함하는 요소는 큰따옴표를 사용해야 합니다. 빈 문자열과 NULL과 일치하는 문자열도 따옴표로 묶어야 합니다. 따옴표로 묶인 배열 요소 값에 큰따옴표나 백슬래시를 추가하려면, 이스케이프 문자열 구문을 사용하고 앞에 백슬래시를 붙입니다. 또한 백슬래시 이스케이프를 사용하여 따옴표를 피하고 배열 구문으로 잘못 처리될 수 있는 모든 데이터 문자를 보호할 수 있습니다.

왼쪽 중괄호 앞이나 오른쪽 중괄호 뒤에 공백을 추가할 수 있습니다. 또한 개별 항목 문자열 앞이나 뒤에 공백을 추가할 수 있습니다. 이러한 모든 경우에 공백은 무시됩니다. 큰따옴표 요소 내의 공백과 요소 내 비공백 문자 양쪽 끝의 공백은 무시됩니다.

Range Types

range 타입은 어떤 요소 타입의 값 범위를 나타내는 데이터 타입입니다. 예를 들어 timestamp의 범위는 회의실의 예약 시간 범위를 나타내는 데 사용할 수 있습니다. 이 경우 데이터 타입은 tsrange(“timestamp range”의 약어)이고 timestamp가 하위 타입입니다. 요소 값이 값 범위 내에 있는지, 그 이전인지, 그 이후인지를 잘 정의할 수 있도록 하위 타입은 전순서(total order)를 가져야 합니다.

range 타입은 여러 요소 값을 단일 range 값으로 표현할 수 있고 겹침 범위와 같은 개념을 명확하게 표현할 수 있다는 점에서 유용합니다. 가장 명백한 예 중 하나는 스케줄링에 시간 및 날짜 범위를 사용하는 것입니다. 이러한 타입은 가격 범위, 측정 기기의 측정 범위 등에도 유용할 수 있습니다.

Built-in Range Types

다음 내장 range 타입이 제공됩니다:

int4range - integer의 범위

int8range - bigint의 범위

numrange - numeric의 범위

tsrange - timestamp without time zone의 범위

tstzrange - timestamp with time zone의 범위

daterange - date의 범위

사용자 정의 range 타입을 정의할 수도 있습니다. 자세한 내용은 User-defined Type을 참조하십시오.

Examples

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- Containment
SELECT int4range(10, 20) @> 3;

-- Overlaps
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- Extract the upper bound
SELECT upper(int8range(15, 25));

-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);

-- Is the range empty?
SELECT isempty(numrange(1, 5));

Inclusive and Exclusive Bounds

비어 있지 않은 모든 range는 하한과 상한이라는 두 개의 경계를 가집니다. 이 값들 사이의 모든 점이 range에 포함됩니다. 포함 경계(inclusive bound)는 경계점 자체가 range에 포함됨을 의미하고, 제외 경계(exclusive bound)는 경계점이 range에 포함되지 않음을 의미합니다. range의 텍스트 형식에서 포함 하한은 “[“로 표현되고 제외 하한은 “(“로 표현됩니다. 마찬가지로 포함 상한은 “]”로 표현되고 제외 상한은 “)”로 표현됩니다.

lower_inc와 upper_inc 함수는 range 값의 하한과 상한을 각각 테스트합니다.

Infinite (Unbounded) Ranges

range의 하한을 생략할 수 있으며, 이는 상한 아래의 모든 점이 range에 포함됨을 의미합니다. 마찬가지로 range의 상한이 생략되면, 하한 위의 모든 점이 range에 포함됩니다. 하한과 상한이 모두 생략되면, 요소 타입의 모든 값이 range에 포함되는 것으로 간주됩니다.

이는 하한이 “음의 무한대”이거나 상한이 “양의 무한대”라고 간주하는 것에 해당합니다. 그러나 이 무한 값은 결코 range의 요소 타입의 값이 아니며, range의 일부가 될 수 없다는 점에 유의하십시오. (따라서 포함 무한 경계라는 것은 존재하지 않습니다. 이를 생성하려고 하면 자동으로 제외 경계로 변환됩니다.)

일부 요소 타입에 “infinite” 표기법이 있는 것은 사실이지만, 이는 range 타입 메커니즘과 관련하여 또 다른 별개의 값입니다. 예를 들어 timestamp range에서 [today,]는 [today,)와 동일합니다. 그러나 [today, infinity]는 때때로 [today, infinity)와 다를 수 있습니다. 후자는 특수 timestamp 값 infinity를 제외합니다.

lower_inf와 upper_inf 함수는 각 range의 무한 하한/상한을 각각 테스트합니다.

Range Input/Output

range 값의 입력은 다음 패턴 중 하나를 따라야 합니다:

(lower-bound,upper-bound)
(lower-bound,upper-bound]
[lower-bound,upper-bound)
[lower-bound,upper-bound]
empty

괄호 또는 대괄호는 위에서 설명한 것처럼 하한과 상한이 제외되는지 포함되는지를 나타냅니다. 마지막 패턴은 empty로, 빈 range(점이 없는 range)를 나타냅니다. lower-bound는 하위 타입에 대한 유효한 입력인 문자열일 수 있으며, 하한이 없으면 비워 둘 수 있습니다. 마찬가지로 upper-bound는 하위 타입에 대한 유효한 입력인 문자열일 수 있으며, 상한이 없으면 비워 둘 수 있습니다. 각 경계 값은 “(큰따옴표) 문자를 사용하여 따옴표로 묶을 수 있습니다. 경계 값에 괄호, 대괄호, 쉼표, 큰따옴표, 백슬래시가 포함된 경우 이러한 문자가 range 구문의 일부로 오인될 수 있으므로 이는 필수입니다. 따옴표로 묶인 경계 값에 큰따옴표나 백슬래시를 삽입하려면 앞에 백슬래시를 붙여야 합니다. (큰따옴표로 묶인 경계 값 내의 큰따옴표 쌍도, SQL 리터럴 문자열에서 작은따옴표에 대한 규칙과 유사하게 큰따옴표로 처리됩니다.) range 구문으로 잘못 처리될 수 있는 모든 데이터 문자를 보호하려면, 따옴표를 사용하지 않고 백슬래시 이스케이프를 사용할 수 있습니다. 또한 빈 문자열인 경계 값을 생성할 때는 “”를 작성해야 합니다. 아무것도 입력하지 않으면 무한 경계를 의미하게 됩니다. range 값 앞뒤에 공백이 허용되지만, 괄호나 대괄호 사이의 공백은 하한 또는 상한 값의 일부로 간주됩니다. (요소 타입에 따라 중요할 수도, 중요하지 않을 수도 있습니다.)

예제:

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;

Constructing Ranges

각 range 타입에는 range 타입과 동일한 이름의 생성자 함수가 있습니다. 추가 경계 값을 따옴표로 묶을 필요가 없으므로, 생성자 함수를 사용하는 것이 range 리터럴 상수를 작성하는 것보다 더 편리합니다. 생성자 함수는 두 개 또는 세 개의 인수를 받습니다. 세 인수 형태는 세 번째 인수로 지정된 형태의 경계로 range를 생성하는 반면, 두 인수 형태는 표준 형태(하한 포함, 상한 제외)의 range를 생성합니다. 세 번째 인수는 다음 문자열 중 하나여야 합니다: “()”, “(]”, “[]” 또는 “[]”

예제:

-- The full form is: lower bound, upper bound, and text argument indicating
-- inclusivity/exclusivity of bounds.
SELECT numrange(1.0, 14.0, '(]');

-- If the third argument is omitted, '[)' is assumed.
SELECT numrange(1.0, 14.0);

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
SELECT int8range(1, 14, '(]');

-- Using NULL for either bound causes the range to be unbounded on that side.
SELECT numrange(NULL, 2.2);

Discrete Range Types

이산(discrete) range는 요소 타입이 integer 또는 date인, 잘 정의된 “단계별(step-by-step)” 타입입니다. 이 타입에서는 두 요소 사이에 유효한 값이 없으면 두 요소가 인접하다고 말할 수 있습니다. 연속 range와 달리, 이는 주어진 두 값 사이에서 서로 다른 요소 값을 항상(또는 거의 항상) 인식할 수 있습니다. 예를 들어 numeric 타입을 넘어서는 range는 timestamp를 넘어서는 range처럼 연속적입니다. (timestamp는 정밀도 제한 때문에 이론적으로는 이산적으로 처리될 수 있지만, 단계의 크기가 중요하지 않을 때는 연속으로 간주하는 것이 좋습니다.) 이산 range 타입을 생각하는 또 다른 방법은 각 요소 값에 대해 “다음” 또는 “이전” 값에 대한 명확한 개념을 갖는 것입니다. 주어진 원래 값 대신 다음 또는 이전 요소 값을 선택함으로써, range의 경계를 포함하는 표현과 range의 경계를 제외하는 표현 사이를 변환하는 것이 가능하다는 점에 유의해야 합니다. 예를 들어 integer range 타입 [4,8]과 (3,9)는 동일한 값 집합을 나타냅니다. 그러나 numeric range의 경우는 그렇지 않습니다. 이산 range 타입은 요소 타입에 대한 원하는 단계 크기를 인식하는 정규화(canonicalization) 함수를 가져야 합니다. 정규화 함수는 특히, 일관된 포함 또는 제외 range의 동일 표현을 갖는 range 타입으로 값을 동등하게 변환하는 역할을 합니다. 정규화 함수가 지정되지 않으면, 서로 다른 타입의 range는 실제로 동일한 값 집합을 나타내더라도 항상 비동등으로 처리됩니다. 내장 range 타입인 int4range, int8range, daterange는 하한을 포함하고 상한을 제외하는(즉, “[)”) 정규 형식을 사용합니다. 사용자 정의 range 타입은 다른 표기법을 사용할 수 있습니다.

Defining New Range Types

사용자 정의 range 타입을 정의할 수 있습니다. 이렇게 하는 가장 흔한 이유는 내장 range 타입으로 제공되지 않는 하위 타입 range를 사용하기 위함입니다. 다음은 하위 타입 float8에 대한 새 range 타입을 정의하는 예입니다.

CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

SELECT '[1.234, 5.678]'::floatrange;

float8는 의미 있는 “단계”가 아니므로, 이 예에서는 정규화 함수를 정의하지 않습니다. 하위 타입이 연속 값이 아니라 이산 값을 갖는 경우, CREATE TYPE 명령은 정규(canonical) 함수를 지정해야 합니다. 정규화 함수는 입력 range 값을 받아 경계 타입이 다를 수 있는 동등한 range 값을 반환해야 합니다. 동일한 값 집합을 나타내는 두 range(예: integer range [1, 7]과 [1, 8))의 정규 출력은 같아야 합니다. 동일한 타입의 두 동등한 값이 항상 동일한 타입의 동일한 값으로 매핑되기만 하면, 어떤 표현을 정규형으로 선택하든 상관없습니다. 정규화 함수는 포함/제외 경계 형식을 제어하는 것 외에도, 원하는 단계 크기가 하위 타입의 저장 가능한 크기보다 큰 경우 경계 값을 잘 처리할 수 있습니다. 예를 들어 timestamp를 넘어서는 range 타입의 단계 크기를 시간으로 정의할 수 있습니다. 이 경우 정규화 함수는 시간의 배수가 아니면 반올림이 필요하거나, 대신 오류가 발생할 수 있습니다. 사용자 정의 range 타입을 정의하는 경우, 주어진 range에 어떤 값이 속하는지를 결정하는 정렬 순서를 변경하기 위해 사용할 다른 하위 타입 B-tree 연산자 클래스나 collation을 지정할 수 있습니다. 또한 GiST 또는 SP-GiST 인덱스에 사용될 range 타입은 하위 타입 차이, 즉 subtype_diff 함수를 정의해야 합니다(인덱스는 subtype_diff 없이도 동작하지만, 차이 함수가 제공될 때보다 훨씬 덜 효율적입니다). 하위 타입 차이 함수는 하위 타입의 두 입력 값을 받아 float8 값으로 표현된 차이를 반환합니다(예: X 빼기 Y). 위 예에서는 일반 float8 뺄셈 연산자의 기반이 되는 함수를 사용할 수 있지만, 다른 하위 타입의 경우 타입 변환이 필요할 수 있습니다. 숫자로 차이를 표현하는 방법에 대한 몇 가지 창의적인 아이디어가 필요할 수 있습니다. 가능한 한 최대한, subtype_diff 함수는 선택된 연산자 클래스와 collation이 암시하는 정렬 순서와 일치해야 합니다. 즉, 정렬 순서에 따라 첫 번째 인수가 두 번째 인수보다 클 때 이 함수의 결과는 항상 양수 값이어야 합니다.

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

SELECT '[11:10, 23:00]'::timerange;

range 타입 생성에 대한 자세한 내용은 User-defined Type을 참조하십시오.

Indexing

GiST 및 SP-GiST 인덱스는 range 타입의 테이블 열을 생성할 수 있습니다. 다음은 GiST 인덱스를 생성하는 예입니다.

CREATE INDEX reservation_idx ON reservation USING GIST (during);

GiST 또는 SP-GiST 인덱스는 =, &&, <@, @>, <<, >>, -|-, &<, &>와 같은 range 연산자가 포함된 쿼리의 속도를 높일 수 있습니다.

B-tree 및 hash 인덱스도 range 타입의 테이블 열을 생성할 수 있습니다. 이러한 인덱스 타입의 경우 유용한 range 연산은 기본적으로 “=”뿐입니다. range 값에 대해 “<”와 “>” 연산자를 사용하고 정의된 B-tree 정렬 순서가 있더라도, 그 순서 자체는 임의적이며 실제로는 그다지 유용하지 않습니다. range 타입에 대한 B-tree 및 hash 지원은 주로 실제 인덱스를 생성하기 위함이 아니라 쿼리 내부에서 정렬과 해싱을 허용하기 위한 것입니다.

Constraints on Ranges

UNIQUE는 스칼라 값에 대한 자연스러운 제약 조건입니다. 그러나 이는 range 타입에는 적합하지 않고 주로 배제 제약 조건(exclusion constraint)에 적합합니다. 배제 제약 조건을 사용하면 range 타입에서 “겹침 없음(nonoverlap)”과 같은 제약 조건을 지정할 수 있습니다.

예제:

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING GIST (during WITH &&)
);

이 제약 조건은 겹치는 값이 테이블에 동시에 존재하는 것을 방지합니다.

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

btree_gist 익스텐션을 사용하여 일반 스칼라 데이터 타입에 배제 제약 조건을 정의할 수 있으며, 이를 통해 최대한의 유연성으로 range를 배제/결합할 수 있습니다. 예를 들어 btree_gist가 설치된 후, 다음 제약 조건은 회의실 번호가 같을 때만 겹치는 range를 거부합니다.

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

INSERT INTO room_reservation VALUES
    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1

User-defined Type

CREATE TYPE 명령을 사용하여 새 타입을 추가할 수 있습니다. CREATE TYPE에는 Composite Type, Enum Type, Range Type, Base Type, Shell Type의 다섯 가지 유형이 있습니다.

구문 :

CREATE TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )

CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] )

CREATE TYPE name AS RANGE (
    SUBTYPE = subtype
    [ , SUBTYPE_OPCLASS = subtype_operator_class ]
    [ , COLLATION = collation ]
    [ , CANONICAL = canonical_function ]
    [ , SUBTYPE_DIFF = subtype_diff_function ]
)

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , TYPMOD_IN = type_modifier_input_function ]
    [ , TYPMOD_OUT = type_modifier_output_function ]
    [ , ANALYZE = analyze_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
)

CREATE TYPE name

예제 :

다음은 복합 타입을 생성하고 이를 함수 정의에 사용하는 예입니다.

CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;

다음은 enum 타입을 생성하고 이를 테이블 정의에 사용하는 예입니다.

CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);

다음은 range 타입을 생성하는 예입니다.

CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);

다음은 기본(base) 타입을 생성한 다음 이를 테이블 정의에 사용하는 예입니다.

CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

CREATE TABLE myboxes (
    id integer,
    description box
);