Vertices

Vertices는 그래프 데이터 모델에서 가장 기본이 되는 요소로서 현실 세계의 개체(entity)를 나타내며, 속성 값인 Properties를 가진다.
 
그래프에서는 vertices와 edges를 기본 단위로 가진다. AgensGraph에서는 vertices와 edges 모두 Properties를 포함할 수 있다. 보통 vertices로 개체를 나타내지만 edges로 개체를 나타내는 경우도 있다. Edges, Properties와는 달리 vertices는 label 값을 가지지 않거나, 여러 label 값을 가질 수 있다.
 
가장 단순한 형태의 그래프는 단일 vertex로 구성된다. Vertex는 0개 이상의 property를 가질 수 있다.

다음 단계로 여러 vertices를 갖는 그래프를 구성해본다. 이전 단계의 그래프에 2개 이상의 vertices를 추가하고 기존 vertex에 하나 이상의 property를 추가한다.

Edges

Edges는 vertices 연결하는 역할을 수행한다. 또한, 두 개의 vertex를 연결하는데 각 vertex는 edge의 방향성에 따라 start vertex와 end vertex로 나뉜다. Vertices와 마찬가지로 properties를 가진다.
 
Vertices 사이의 edges는 그래프 데이터베이스에서 중요한 역할을 하는데, 특히 연결된 데이터를 찾기 위해서 반드시 필요하다.
 
Edges는 두 개의 vertex를 연결하는데 각 vertex는 edge의 방향성에 따라 start vertex와 end vertex로 나뉜다.
 
Edges를 활용한다면 vertices를 list, tree, map, 복합 엔티티와 같은 다양한 자료구조 형태로 만들어줄 수 있다. 우리가 만들어가고 있는 예제에 edges를 추가한다면 더욱 의미있는 데이터를 나타낼 수 있다.
예제에서는 ACTED_IN과 DIRECTED를 edge 타입으로 사용했다. ACTED_IN의 property인 roles는 배열 타입의 값을 저장한다.
 
ACTED_IN edge는 Tom Hanks vertex를 start vertex로, Forrest Gump vertex를 end vertex로 가진다. 이를 다르게 설명하면 Tom Hanks vertex는 outgoing edge를 가지고 있고, Forrest Gump vertex는 incomming edge를 가지고 있다고 말할 수도 있다.

한 방향으로 edge가 형성되어 있다면 굳이 반대방향으로 중복해서 edge를 추가할 필요가 없으며, 이는 Graph 순회나 성능과도 관련이 있다.

Edges는 항상 방향성을 가지고 있지만, 사용하는 애플리케이션에서 불필요하다면 방향성을 무시할 수도 있다. 아래는 vertex가 자기 스스로에게 edges를 가지고 있는 모습을 나타내고 있다.
Graph 순회를 보다 효율적으로 수행하기 위해서는 모든 edges가 edge type을 가지는 것이 좋다.

Properties

Vertices와 edges 모두 properties를 가질 수 있다. Properties는 속성 값으로 각 속성명은 string 타입으로만 정의해야 한다.
 
Property 값으로 사용가능한 데이터 타입은 아래와 같다.

• Numeric 타입
  
• String 타입
  
• Boolean 타입
  
• List 타입 (다양한 데이터 타입들의 집합)

NULL값은 property값으로 사용할 수 없다. NULL이 입력된다면 해당 property 자체는 없는 것으로 간주한다. 단, List에서는 NULL 값을 사용할 수 있다.

Labels

Label을 이용해서 vertex 나 edge의 역할이나 타입을 정의할 수 있다. 특징이 유사한 vertex 들이나 edge 들을 그룹화하고 해당 그룹의 이름을 정의할 수 있는데 이를 label이라고 한다. 즉 유사한 label을 가진 모든 vertex 들이나 edge 들은 동일한 그룹에 속함을 나타낸다.
 
데이터베이스 질의문들은 label을 사용함으로서 전체 그래프가 아닌 해당 그룹에 관해서만 수행할 수 있고, 이는 질의들을 보다 효율적으로 수행할 수 있도록 도움을 준다.
 
Vertex들에 label을 사용하는 것은 선택사항이며, label을 가지지 않거나 오직 하나의 label 만을 갖는다.
또한 label는 properties에 constraints를 정의하거나 index를 추가할 경우에도 사용된다.  
Edge에도 vertex와 유사한 label을 지정할 수 있는데, vertex와는 달리 label이 없는 edge는 존재하지 않는다. 모든 edge 들은 항상 하나의 label을 갖는다.  
기존 예제 그래프에는 Person과 Movie labels를 추가해보도록 하겠다.

Traversal

요청받은 질의에 응답하기 위해 graph를 탐색하며 paths를 찾아가는 것을 traversal이라고 한다. Traversal은 시작 verticies에서 관련 vertices를 탐색하며 요청받은 질의에 대한 답변을 찾아가는 과정이다. 즉, 그래프를 순회한다는 해당 vertices와 파생되는 edges를 특정 규칙에 따라 찾아가는 것을 의미한다.  
지금까지의 예제에서 Tom Hanks가 출현한 영화를 찾고자 한다. Tom Hanks vertex를 시작으로 그와 연결된 ACTED_IN edge를 따라서 Forrest Gump라는 vertex에서 종료되는 모든 과정을 traversal로 볼 수 있다.

그래프 데이터베이스에서 cypher 질의문의 traversal과 추가적인 기술들을 활용한다면 보다 뛰어난 결과 데이터를 도출할 수 있다. 자세한 사항은 Cypher Query Language를 참고하면 된다.

Paths

Path는 질의문이나 traversal의 결과 데이터로서, 한 개 이상의 vertices와 이에 연결된 edges를 나타낸다.
 
이전 예제에서 traversal의 결과 데이터인 path는 아래와 같다.

위 path의 길이는 1이다. Path중 가장 짧은 길이는 0인데, 단일 vertex가 edges를 가지고 있지 않은 경우가 이에 해당한다.

Vertex 자신을 가리키는 edge를 가진 경우 path의 길이는 1이다.

Get the pre-compiled binary

AgensGraph는 Linux/Windows에서 동작한다. AgensGraph는 두 가지 방법으로 설치가 가능하다. 하나는 바이너리 패키지를 다운로드 하는 방법이고, 또 다른 하나는 소스코드를 다운받아 패키지를 컴파일 하는 방법이다. 바이너리 패키지는 비트나인 홈페이지에서 다운이 가능하며, 소스코드는 github에서 다운이 가능하다. 자신의 시스템 환경을 알고 싶다면 다음 명령을 명령어 창에 입력한다.

uname -sm

Extract the package

다운받은 파일을 원하는 위치에서 압축을 해제한다. (e.g.: /usr/local/AgensGraph/)

tar xvf /path/to/your/use

환경변수 설정 (선택사항)

쉘 시작파일에 다음 3줄을 추가한다. (e.g. .bash_profile)

export LD_LIBRARY_PATH=/usr/local/AgensGraph/lib:$LD_LIBRARY_PATH
export PATH=/usr/local/AgensGraph/bin:$PATH
export AGDATA=/path/to/make/db_cluster

데이터베이스 클러스터 생성

다음 명령을 통해 데이터베이스 클러스터를 생성한다. -D 옵션을 지정하지 않을 경우 .bash_profile에 지정한 AGDATA를 사용한다.

initdb [-D /path/to/make/db_cluster]

서버시작

아래의 명령어를 이용하여 AgensGraph를 시작할 수 있다.

ag_ctl start [-D /path/created/by/initdb]

데이터베이스 생성

createdb [dbname]

데이터베이스 이름과 사용자 이름을 명시하지 않으면 기본값으로 설정된다. 기본값은 현재 사용자의 이름이다.

터미널 실행

agens [dbname]

위와 같이 터미널 실행시 다음과 같은 화면을 볼 수 있다.

username=#

슈퍼유저인 경우 프롬프트에 “=#”으로 표시되고 그외의 유저는 “=>”으로 표시된다.

username=# CREATE
username-# ( 
username(# ( 
username(# )
username(# )
username-#

쿼리 입력 중간인 경우는 ‘-’으로 표시하고 괄호 내부에 입력하는 중이면’(’로 표시한다. 다중 괄호인 경우도 그에 맞춰 표현할 수 있다. 입력 프롬프트를 커스터마이징 할 수 있으며 상세옵션은 다음 링크에서 확인 할 수 있다.

그래프 생성

AgensGraph는 단일 데이터베이스에 다중 그래프 저장이 가능하다. Cypher는 다중 그래프를 파악할 수 없다. AgensGraph는 DDL과 Cypher를 사용하여 그래프를 생성, 관리를 위한 변수들을 지원한다. 다음 구문은 network라 불리는 그래프를 생성하고 현재 그래프를 설정하는 구문이다.

CREATE GRAPH network;
SET graph_path = network;

이 예제에서 graph_path 변수는 network로 설정한다. 그러나 그래프를 만들기 전에 graph_path가 설정되어 있지 않으면 그래프를 만든 후 자동으로 설정한다.

사용자 생성

CREATE ROLE user1 LOGIN IN ROLE graph_owner;
DROP ROLE user1;

새로운 사용자를 생성하여 데이터베이스 오브젝트에 대한 소유권과 기타 권한을 관리할 수 있다. 새 사용자가 그래프에서 label을 생성하고 싶다면 반드시 graph를 생성한 사용자와 같은 그룹에 포함되어 있어야한다. 사용자 생성에 관한 자세한 옵션은 링크를 참조하기 바란다.

레이블 생성

그래프 데이터를 생성하기 전에 레이블을 생성하는 것이 원칙이지만 cypher의 CREATE 문 수행시 label을 지정하면 자동으로 생성해주는 편의 기능을 지원하고 있다. AgensGraph에서는 vertex와 edge에는 하나의 레이블을 가져야 한다. 다음 구문은 person라는 vertex label과 knows라는 edge label을 생성하는 예제이다.

CREATE VLABEL person;
CREATE ELABEL knows;
CREATE (n:movie {title:'Matrix'});

Vertex와 Edge 생성

이번절에서는 cypher의 CREATE절을 사용하여 person vertex와 knows edge를 생성한다. CREATE절은 vertex와 edge로 구성된 패턴을 생성한다. (variable:label {property: value, ...})는 vertex의 형태이고, -[variable:label {property: value, ...}]-는 edge의 형태이다. 그리고 edge의 방향성은 <와 >으로 edge의 방향을 나타낼 수 있다. vertex와 edge의 형태에서 variable의 경우는 있어도 되고, 없어도 된다.

Note : AgensGraph는 edge 패턴에서 --을 지원하지 않는다. --는 문장 끝의 주석을 의미한다.

다음 구문은 “Tom knows Summer”, “Pat knows Nikki” 그리고 “Olive knows Todd” 패턴을 생성하는 예제이다.

CREATE (:person {name: 'Tom'})-[:knows]->(:person {name: 'Summer'});
CREATE (:person {name: 'Pat'})-[:knows]->(:person {name: 'Nikki'});
CREATE (:person {name: 'Olive'})-[:knows]->(:person {name: 'Todd'});

AgensGraph에서 vertex와 edge의 속성의 경우 jsonb 타입을 사용한다. 속성 값들의 경우 JSON 객체로 속성들을 표현한다.

Creating Graph

Agens Graph는 단일 데이터베이스 내에 여러 그래프들을 저장할 수 있다. 따라서 그래프를 생성하고, 사용할 그래프를 선택해야 cypher를 이용한 그래프 질의가 가능하다.

• Create Graph

  CREATE GRAPH graphName;
  SET graph_path = graphName;

  CREATE GRAPH는 그래프를 생성하는 명령이다. 해당 명령과 함께 생성할 그래프의 이름을 함께 명시하여 사용한다(v1.3. 버전 이후부터 graphName에 공백 사용이 가능하다). graph_path는 현재 다룰 그래프를 의미하는 변수이다. 다루고자 하는 그래프의 이름을 SET을 사용하여 설정한다.

• Drop Graph

  DROP GRAPH graphname CASCADE;       

  DROP GRAPH는 그래프를 삭제하는 명령이다. 그래프 생성시 vertex와 edge에 대한 초기 label이 생성되므로 DROP GRAPH시 CASCADE를 반드시 사용해야 한다.

• Create Labels

  CREATE VLABEL vlabelName;
  CREATE ELABEL elabelName;
  
  CREATE VLABEL childVlabelName inherits (parentVlabelName);
  CREATE ELABEL childElabelName inherits (parentElabelName1, parentElabelName2);

  CREATE VLABEL은 vlabel을, CREATE ELABEL은 elabel을 생성하는 명령이다. 각 명령은 생성할 vlabel 혹은 elabel의 이름을 함께 명시하여 사용한다.
  inherits()는 다른 label을 상속하는 명령이다. Label을 생성할 때 자식 label 이름 뒤에 해당 해당 키워드와 함께 부모label의 이름을 명시하면 다른 label을 상속할 수 있다. 상속의 대상으로 부모 label은 여러 개가 될 수 있다.

• Drop Labels

  DROP VLABEL vlabelName;
  DROP ELABEL elabelName;
  
  DROP ELABEL elabelName CASCADE;

  DROP VLABEL은 vlabel을, DROP ELABEL은 elabel을 삭제하는 명령이다. 각 명령은 삭제할 vlabel 혹은 elabel의 이름을 함께 명시하여 사용한다. 상속관계에 있는 vlabel은 직접적으로 삭제가 되지않으므로 CASCADE를 사용하여 의존관계의 모든 데이터를 삭제한다.

• Create vertices and edges

  CREATE (:person {name: 'Jack'});
  CREATE (:person {name: 'Emily'})-[:knows]->(:person {name: 'Tom'});

  CREATE절은 vertex와 edge를 생성하는 명령이다. 해당 명령은 생성할 vertex 혹은 edge를 pattern으로 올바르게 작성하여 사용한다. (CREATE절을 더불어 다양한 절과 vertex/edge를 표현할 pattern에 대해서는 이후에 자세히 설명한다.)

Querying Graph

그래프를 대상으로 질의를 한다는 것은 찾고자 하는 그래프를 pattern으로 표기를 하여 찾아낸 뒤 해당 그래프 내에서 원하는 정보를 추출해내는 것이다.

MATCH (:person {name: 'Jack'})-[:likes]->(v:person)
RETURN v.name;

name
-------
Emily
(1 row)

MATCH 절은 해당 절에 표기된 pattern에 부합하는 그래프 데이터를 찾아낸다. 찾은 그래프에서 반환하고자 하는 요소만을 RETURN 절에 명시한다.

Manipulating Graph

기존에 있던 그래프 데이터를 수정하기 위해서도 MATCH 절의 pattern을 표기하여 해당 그래프를 찾아낸 뒤에 수정 작업을 해야 한다.

MATCH (v:person {name: 'Jack'})
SET v.age = '24';

SET 절을 이용하여 vertex나 edge의 property 값을 설정할 수 있다.

Vertex

Vertex는 괄호를 이용하여 표현하며 vlabel, property, variable과 함께 표기하면 찾고자 하는 vertex를 더욱 구체화 할 수 있다.

( )

Vertex는 ( )로 표현한다. 위 예제와 같이 괄호 안에 vlabel이나 property 등을 표기하지 않은 pattern은 모든 vertex를 의미한다.

(:person)

Vertex에 vlabel을 표현하고자 한다면 (:vlabelName) 으로 표기한다. Vertex를 나타내는 괄호 안에 콜론과 함께 vlabel의 이름을 표기하여 나타낸다. 위 예제는 person이라는 vlabel을 가지고 있는 vertex를 의미한다.

(v)
(var)
(var_1)
(v:person)

Vertex에 variable을 부여하고자 한다면 (variableName)로 표기한다. Variable은 alphanumeric(a~z, 0~9), underbar의 조합으로 명명할 수 있다(단 숫자로 시작할 수 없다). Vertex에 variable과 vlabel을 동시에 나타내고자 할 때는 (variableName:vlabelName) 으로 표기한다.

({name: 'Jack'})
(v:person {name: 'Jack'})
(v:person {name: 'Jack', age: 24})

Vertex에 property를 표기하여 더욱 구체화 할 수 있다. Property를표현하고자 한다면
({propertyName:propertyValue})로 표기한다. 값이 string인 경우에는 ‘ ’ 로 값을 감싸야 한다. Property를 여러 개 표현하고자 한다면 ,를 이용한다.

Edge

Edge는 2개의 대시를 이용하여 표현하며 Edge는 elabel, property, variable과 함께 표기할 수 있다.

-[]-
-[]->
<-[]-

Edge는 -[]-로 표현한다. < >를 이용하면 방향성을 표현할 수 있다. 위 예에서 대시만으로 표현한 -[ ]-는 아무런 제약조건을 표시하지 않았기에 모든 edge를 의미한다. 화살괄호를 추가로 표현한 -[ ]-> <-[ ]-는 한쪽 방향성을 가진 모든 edge를 의미한다.

-[:knows]->

Edge에 다른 요소들을 표현하고자 한다면 [ ] 내부에 해당 요소들을 명시한다. Edge에 elabel을 표현하고자 한다면 -[:elabelName]-> 으로 표기한다. 위 예제는 knows라는 elabel을 가지고 있는 edge를 의미한다.

-[e]->
-[e:likes]->

Edge에 variable을 부여하고자 한다면 -[variableName]->로 표기한다. Edge에 variable과 elabel을 동시에 나타내고자 할 때는 -[variableName:elabelName]-> 으로 표기한다.

-[{why: 'She is lovely'}]->
-[:likes {why: 'She is lovely'}]->
-[e:likes {why: 'She is lovely'}]->

Edge에 property를 표현하고자 한다면 -[{propertyName:propertyValue}]->로 표기한다. 값이 string인 경우에는 ‘ ’ 를 이용한다. Property를 여러 개 표현하고자 한다면 ,를 이용한다.

Vertices and Edges

Pattern은 vertex와 edge를 각각 표현 할 수도 있지만 함께 어우러져 표현할 수 있다.

()-[]->()
(jack:person {name: 'Jack'})-[k:knows]->(emily:person {name: 'Emily'})
p = (:person)-[:knows]->(:person)

Vertex와 edge의 조합으로 된 pattern의 기본틀은 ( )-[]->( )이다. Vertex와 edge에 property, label을 명시하여 pattern에 의미를 부여하는 것이 좋다. Variable은 vertex와 edge 개별로 부여할 수도 있지만, pattern 전체를 대상으로 부여할 수도 있다. Pattern 전체에 variable을 부여하고자 한다면 =를 이용한다. 위 예제에서 p는 variable이고, =를 이용함으로써 pattern 전체를 대상으로 적용하였다.

Path

Pattern 내에서 vertex와 edge의 개수는 지속적으로 증가할 수 있다. Pattern 내 일련 경로의 단위를 path라고 한다.

(a)-[]->( )-[]->(c)
(a)-[*2]->(c)

(a)-[]->( )-[]->( )-[]->(d)
(a)-[*3]->(d)

(a)-[]->( )-[]->( )-[]->( )-[]->(e)
(a)-[*4]->(e)

길이가 긴 path의 경우, 가독성을 높이고 작성의 편의성을 위해서 축약된 형태로 바꿔 작성할 수 있다. Vertex n개가 edge를 n-1번 거쳐서 간다면 대괄호 내에 별표(*)와 함께 n-1을 표기하면 된다.(위의 예시를 참고한다.)

Flexible Length

상황에 따라 한 질의 내에서 거쳐야 하는 edge의 개수에 동적인 변화를 줘야 하는 경우가 생길 수 있다.

(a)-[*2..]->(b)
(a)-[*..7]->(b)
(a)-[*3..5]->(b)
(a)-[*]->(b)

만약 path의 길이에 동적인 변화를 주고자 한다면 ..를 표기하면 된다. 위 예제는 차례대로 edge의 개수를 2개 이상 지닌 path, 7개 이하 지닌 path, 3개 이상 5개 이하 지닌 path, 무한 길이의 path를 뜻한다.

MATCH

MATCH절은 database에서 찾고자 하는 그래프 pattern을 기술하는 절이다. 데이터를 가져오는 가장 기본적인 방법이다. Pattern 명시에 대한 자세한 내용은 앞서 설명한 Pattern을 참고한다.

• Mathing

  MATCH (j {name: 'Jack'})
  RETURN j;
  
  MATCH (j {name: 'Jack'})-[l:knows]->(e)
  RETURN l;
  
  MATCH (j {name: 'Jack'})
  RETURN j.age;
  
  MATCH p=(j {name: 'Jack'})-[l:knows]->(e)
  RETURN p;

  찾고자 하는 그래프 pattern을 MATCH절에 기술한 뒤 RETURN절을 이용하여 반환한다.

• Various Notation

  MATCH (j:person {name: 'Jack'})-[:knows]->(v:person)
  MATCH (e:person {name: 'Emily'})-[:knows]->(v)
  RETURN v.name;
  
  MATCH (j:person {name: 'Jack'})-[:knows]->(v:person), 
        (e:person {name: 'Emily'})-[:knows]->(v)
  RETURN v.name;
  
  MATCH (j:person {name: 'Jack'})-[:knows]->(v:person)<-[:knows]-(e:person {name: 'Emily'})
  RETURN v.name;

  위 3개의 질의는 Jack의 지인이면서 Emily의 지인이기도 한 사람들의 이름을 출력한다. 모두 동일한 결과를 출력하지만 MATCH절의 개수나 MATCH절내 pattern 표기 방법이 다르다. 첫 번째 질의는 MATCH절을 두 번 사용하였고, 두 번째 질의는 하나의 MATCH절에 콤마를 이용하여 두 개의 pattern을 나타내었으며, 세 번째 질의는 하나의 MATCH절에 하나의 pattern을 이용하여 나타내었다. MATCH절을 다양하게 사용하여 같은 결과를 도출해낼 수 있다.

  그래프 데이터를 생성하는 경우, 조회하는 경우, 삭제하는 경우, 추가하는 경우, 수정하는 경우 등 pattern과 부합하는 그래프를 우선적으로 찾아야 하는 경우가 대다수이다. 따라서 MATCH절은 매우 기본적이면서 중요한 절이다.

  [참조]

  Agens에서 대량의 데이터가 여러 페이지에 걸쳐서 출력될 경우 화면이동에 대한 도움말을 ? 키워드를 이용하여 볼 수 있다.

  Most commands optionally preceded by integer argument k.  Defaults in brackets.
  Star (*) indicates argument becomes new default.
  -------------------------------------------------------------------------------
  <space>                 Display next k lines of text [current screen size]
  z                       Display next k lines of text [current screen size]*
  <return>                Display next k lines of text [1]*
  d or ctrl-D             Scroll k lines [current scroll size, initially 11]*
  q or Q or <interrupt>   Exit from more
  s                       Skip forward k lines of text [1]
  f                       Skip forward k screenfuls of text [1]
  b or ctrl-B             Skip backwards k screenfuls of text [1]
  '                       Go to place where previous search started
  =                       Display current line number
  /<regular expression>   Search for 'k'th occurrence of regular expression [1]
  n                       Search for 'k'th occurrence of last r.e [1]
  !<cmd> or :!<cmd>       Execute <cmd> in a subshell
  v                       Start up /usr/bin/vi at current line
  ctrl-L                  Redraw screen
  :n                      Go to kth next file [1]
  :p                      Go to kth previous file [1]
  :f                      Display current file name and line number
  .                       Repeat previous command
  ---------------------------------------------------------------------------

Shortest Path

• Single Shortest Path
  Shortestpaths 함수를 사용하여 두 개의 vertex 사이에서 하나의 Shortest Paths 를 찾는다.

  MATCH p = shortestpath( (j:person {name: 'Jack'})-[l:knows*..15]-(a:person {name: 'Alice'}) )
  RETURN p;

  두 개의 Vertex Jack과 Alice 사이에서 15개의 Releations 내(안)에서 하나의 Shortest Paths를 찾는다. 괄호 안에 start vertex, edge, end vertex로 구성된 단일 링크의 패스를 기술한다. Edge는 다른 edge와 마찬가지로 max hops 및 direction을 기술할 수 있다.

• All Shortest Paths
  두개의 vertex 사이에서 모든 Shortest Paths를 찾는다.

  MATCH p = allshortestpaths( (j:person {name: 'Jack'})-[l:knows*]-(a:person {name: 'Alice'}) )
  RETURN p;

  두 개의 Vertex Jack과 Alice 사이에서 모든 Shortest Paths를 찾는다.

OPTIONAL MATCH

OPTIONAL MATCH절은 MATCH절과 마찬가지로 찾고자 하는 그래프 pattern을 기술하는 절이다. OPTIONAL MATCH절은 반환할 결과가 없을 경우 NULL을 반환한다는 점이 MATCH절과 다르다.

• Optional Matching

  OPTIONAL MATCH (e:person {name:'Emily'})
  RETURN e.hobby;

  OPTIONAL MATCH절의 사용방법은 MATCH절과 같다. 찾고자 하는 pattern을 OPTIONAL MATCH절에 표기한 뒤 RETURN절을 통해 결과를 반환한다. 반환되는 결과에는 NULL이 포함되어 있을 수 있다.

MATCH ONLY

Only 키워드를 사용하면 MATCH쿼리를 사용하여 자식 라벨을 제외한 결과를 반환할 수 있다.

• Match only

  MATCH (n:person ONLY) RETURN n;
  MATCH ()-[r:knows ONLY]->() RETURN r;

RETURN

RETURN절은 cypher 질의의 결과를 명시하는 절이다. 찾고자 하는 그래프 pattern과 일치하는 데이터를 반환하는데 vertex, edge, property등을 반환할 수 있다.

• Vertex Return

  MATCH (j {name: 'Jack'})
  RETURN j;

  Vertex를 반환하고자 한다면 MATCH절에 찾고자 하는 vertex를 표기하고 variable을 부여한다. 이후 RETURN절에서 해당 variable을 명시하면 vertex를 반환할 수 있다.

• Edge Return

  MATCH (j {name: 'Jack'})-[k:knows]->(e)
  RETURN k;

  Edge를 반환하고자 한다면 MATCH절에 찾고자 하는 edge를 표기하고 variable을 부여한다. 이후 RETURN절에서 해당 variable을 명시하면 edge를 반환할 수 있다.

• Property Return

  MATCH (j {name: 'Jack'})
  RETURN j.age;

  Vertex나 edge의 property를 반환하고자 한다면 MATCH절에 찾고자 하는 vertex나 edge를 표기하고 variable을 부여한다. 이후 RETURN절에서 해당 variable과 property를 .과 함께 명시하면 property를 반환할 수 있다.

• All Return

  MATCH (j {name: 'Jack'})-[k:knows]->(e)
  RETURN *;

  MATCH절에 표기한 요소들을 모두 반환하고자 한다면 RETURN절에 *를 명시하면 된다. 반환되는 요소들은 variable이 부여된 요소들이다. Variable이 부여되어 있지 않은 요소들은 반환되지 않는다.

• Path Return

  MATCH p=(j {name: 'Jack'})-[k:knows]->(e)
  RETURN p;

  MATCH절에 표기된 pattern과 일치하는 path를 반환하고자 할 때는 pattern 전체에 variable을 적용해야 한다. Pattern 앞에 variable을 =와 함께 표기하면 pattern 전체에 variable을 적용할 수 있다. 그 후 RETURN절에 해당 variable을 명시하면 path를 반환할 수 있다.

• Alias Return

  MATCH (j {name: 'Jack'})
  RETURN j.age AS HisAge;
  
  MATCH (j {name: 'Jack'})
  RETURN j.age AS "HisAge";

  반환 되는 결과의 컬럼에 alias를 부여하여 출력할 수 있다. 반환 되는 요소 뒤에 AS 키워드와 함께 alias를 표기하면 된다. Alias를 큰따옴표와 함께 표기하지 않으면 소문자로 출력이 되고, 함께 표기하면 표기한 그대로 출력이 된다.

• Function Return

  MATCH (j {name: 'Jack'})-[k:knows]->(e)
  RETURN id(k), properties(k);
  
  MATCH p=(j {name: 'Jack'})-[k:knows]->(e)
  RETURN length(p), nodes(p), edges(p);
  
  MATCH (a)
  RETURN count(a);

  Vertex와 Edge에 대해서 id, properties만 얻을 수 있는 함수를 제공하며 path에 대해서 길이 및 각 요소들을 따로 구할 수 있도록 함수를 제공하고 있다. 또한 PostgreSQL이 지원하는 aggregation 함수를 사용할 수 있다.

• Constant Return

  RETURN 3 + 4, 'Hello ' + 'AgensGraph';
  RETURN 3 + 4 AS lucky, 'Hello ' + 'AgensGraph' AS greeting;

  상수값들과 그에 대한 연산자를 표현 할 수 있다. SQL SELECT 구문에 올 수 있는 expression 중에 테이블, 컬럼 표현을 제외하고 모두 RETURN 구문과 함께 사용할 수 있다. 더 자세한 내용은 PostgreSQL Expression을 참조한다.

• Unique Return

  MATCH (j { name: 'Jack' })-[k:knows]->(e)
  RETURN DISTINCT e;              

  반환 되는 결과의 중복된 레코드를 제거하고 유일한 값에 대해서만 결과를 출력한다. 반환 되는 요소 앞에 DISTINCT 키워드를 표기하면 된다.

WITH

WITH절은 여러 cypher 질의들을 연결하는 절이다. WITH절 선행 질의의 결과를 WITH절 후행 질의로 전달한다. 즉, WITH절은 후행 질의의 input으로 선행 질의의 값을 전달하는 RETURN절이라고 볼 수 있다.

• WITH

  MATCH (j:person {name:'Jack'})-[:knows]->(common:person)<-[:knows]-(other:person)
  RETURN other, count(common);
  
  WHERE count(common) > 1
  RETURN other;
  
  MATCH (j:person {name:'Jack'})-[:knows]->(common:person)<-[:knows]-(other:person)
  WITH other, count(common) AS cnt
  WHERE to_jsonb(cnt) > 1
  RETURN other;

  위 3개의 질의를 예로 WITH절에 대해서 설명한다. 첫 번째 질의는 Jack과 other이 공통으로 알고있는 지인들이 몇명인지를 그 other과 함께 반환한다. 두 번째 질의는 첫 번째 질의와 연관지어 생각하면 공통의 지인 수가 1명보다 많은 경우에 해당되는 other를 반환하는 질의임을 알 수 있다. 두 번째 질의는 단독으로 실행될 수 없으며, 첫 번째 질의와 조합을 이루어야 두 번째 질의가 의미가 생기는데 두 질의를 이어주는 역할을 WITH절이 한다. 세 번째 질의는 첫 번째, 두 번째 질의를 연결시켜 주는 WITH절을 포함한 질의이다. 첫 번째 질의의 반환되는 결과들을 variable, alias 형태로 붙잡아두고 있다가 두 번째 질의로 그 결과들을 넘겨준다. 이러한 경우처럼 여러 질의들을 연결하고 선행 질의의 output이 후행 질의의 input으로 사용되는 때에는 WITH절로 연결시키면 된다. WITH절에서는 variable이나 alias형태로 붙잡고 있어야 한다.

• Partitioning

  MATCH (j:person {name:'Jack'})-[:knows]->(common:person)<-[:knows]-(other:person)
  RETURN other, count(common)
  ORDER BY other.name
  LIMIT 10;
  
  WHERE count(common) > 1
  RETURN other;
  
  MATCH (j:person {name:'Jack'})-[:knows]->(common:person)<-[:knows]-(other:person)
  WITH other, count(common) AS cnt
  ORDER BY other.name
  LIMIT 10
  WHERE to_jsonb(cnt) > 1
  RETURN other;

  WITH절을 사용하는 데에 있어서 전체 질의를 partitioning 하는 것이 중요하다. WITH절이 RETURN절이라 생각하고 RETURN 후에 올 수 있는 ORDER BY, LIMIT까지를 하나의 partition이라고 생각하면 된다. 따라서 선행 질의에 ORDER BY, LIMIT절 등이 있으면 해당 절들을 WITH절 뒤에 작성한 뒤 후행 질의를 작성하면 된다.

WHERE

WHERE 절은 단독으로 사용할 수 없고, MATCH, OPTIONAL MATCH, START, WITH에 종속되어 사용한다. MATCH와 OPTIONAL MATCH절의 패턴에 제약조건을 추가하거나 START 및 WITH의 경우 결과를 필터링 한다.

ORDER BY

ORDER BY절은 결과 값을 정렬하는 절이다. RETURN절이나 WITH절과 함께 사용되며 컬럼을 기준으로 오름차순/내림차순으로 정렬을 할 것인지 결정한다.

• Order by Property

  MATCH (a:person)
  RETURN a.name AS NAME
  ORDER BY NAME;
  
  MATCH (a:person)
  RETURN a.name AS NAME, a.age AS AGE
  ORDER BY NAME, AGE;
  
  MATCH (a:person)
  RETURN a
  ORDER BY a.name;

  ORDER BY절은 기본적으로 vertex나 edge의 property를 기준으로 정렬한다. 정렬의 기준이 될 property의 alias를 ORDER BY절에 명시하면 된다. 즉 RETURN절에서 정렬의 기준이 될 property에게 alias를 부여한 뒤에 ORDER BY절에서 해당 alias를 명시한다. ORDER BY절에 여러 개의 기준들을 명시하면 첫 번째 기준으로 우선 정렬을 한 뒤 첫 번째 요소의 중복되는 값에 한해서만 두 번째 기준으로 정렬을 한다. 하지만 RETURN절에 property가 명시되어 있지 않다면 ORDER BY절에 alias가 아닌 property를 직접적으로 명시해도 된다.

• Descending Order

  MATCH (a:person)
  RETURN a.name AS NAME
  ORDER BY NAME DESC;
  
  MATCH (a:person)
  RETURN a.name AS NAME, a.age AS AGE
  ORDER BY NAME DESC, AGE;

  ORDER BY절은 기본적으로 오름차순으로 정렬을 한다. 내림차순으로 정렬을 하고자 한다면 DESC 키워드를 표기하면 된다. ORDER BY절에 여러 개의 기준들을 명시하였을 경우에는 내림차순으로 정렬되기 원하는 요소 뒤에 DESC를 표기하면 된다.

SKIP

SKIP절은 검색 시작 위치를 변경하는 절이다.

MATCH (a)
RETURN a.name
SKIP 3;

MATCH절에 표기된 pattern을 찾을 때 SKIP절에 명시된 숫자만큼을 건너뛰고 검색을 시작한다.

LIMIT

LIMIT절은 result set의 결과 수를 제한 시키는 절이다.

MATCH (a)
RETURN a.name
LIMIT 10;

출력하고자 하는 결과 수를 제한하고자 한다면 LIMIT절에 그 수를 명시하면 된다. Result set의 결과 수가 LIMIT절에 명시된 숫자보다 작거나 같다면 모든 결과가 출력될 것이고, 크다면 LIMIT절에 명시된 숫자만큼만 출력하게 된다.

CREATE

CREATE절은 vertex나 edge 등 그래프 요소들을 생성하는 절이다.

• Create Vertex

  CREATE ( );
  CREATE (:person);
  CREATE (:person {name: 'Edward'});
  CREATE (:person {name: 'Alice', age: 20});
  CREATE (a {name:'Alice'}), (b {name:a.name});

  Vertex를 생성하고자 할 때는 CREATE절에 vertex 관련 pattern을 표기하면 된다. Vertex 생성시 마지막 예제와 같이 앞서 명시한 vertex를 참조하여 생성할 수 있다.

• Create Edge

  MATCH (E:person {name: 'Edward'}), (A:person {name: 'Alice'})
  CREATE (E)-[:likes]->(A);
  
  MATCH (E:person {name: 'Edward'}), (A:person {name: 'Alice'})
  CREATE (E)-[:likes {why: 'She is lovely'}]->(A);
  
  MATCH (E:person {name: 'Edward'})
  CREATE (E)-[:IS_PROUD_OF]->(E);

  Edge는 두 vertex 사이를 연결하는 역할을 한다. 따라서 MATCH절을 통해 두 vertex를 찾은 뒤 edge를 생성해야 한다. 참고로 두 vertex라는 것은 서로 다른 vertex를 의미하는 것은 아니다. 위의 세 번째 질의처럼 self-edge도 가능하다.

• Create Path

  CREATE (E:person {name: 'Edward'})-[:likes]->(A:person {name: 'Alice'});
  
  MATCH (E:person {name: 'Edward'})
  CREATE (E)-[:likes]->(A:person {name: 'Alice'});
  
  MATCH (E:person {name: 'Edward'}), (A:person {name: 'Alice'})
  CREATE (E)-[:likes]->(A);

  각 요소들을 개별로 생성할 수도 있지만 CREATE절에 path를 표기하면 원하는 pattern을 한 번에 생성할 수도 있다. Path를 한번에 생성할 때는 주의해야 할 점이 있다. 첫 번째 질의처럼 MATCH절과 함께 사용하지 않은 경우에는 해당 pattern과 똑같은 데이터가 존재하더라도 새롭게 생성한다. 즉, 중복되어 만들어지는 것이다. 두 번째 질의처럼 MATCH절과 함께 사용한다면 Edward vertex를 찾은 뒤 해당 vertex에 likes edge와 Alice vertex를 새롭게 만든다. 만약 이미 존재하고 있는 Edward와 Alice vertex에 likes edge만을 새롭게 생성하고자 한다면 세 번째 질의처럼 사용하면 된다.

DELETE

DELETE절은 vertex나 edge를 제거하는 절이다.

• Delete vertex

  MATCH (m:person {name: 'Michael'})
  DELETE m;

  MATCH절을 통해 제거하고자 하는 vertex를 찾고 DELETE절에 variable을 표기하여 제거한다. 단, 제거하려는 vertex가 다른 vertex와 edge로 연결되어 있다면 해당 edge를 먼저 제거하여야 vertex가 제거된다.

• Delete edge

  MATCH (m:person {name: 'Michael'})-[l:likes]->(b:person {name: 'Bella'})
  DELETE l;

  MATCH절을 통해 제거하려는 edge를 찾고 DELETE절에 variable을 표기하여 해당 edge를 제거한다.

DETACH DELETE

• Delete a vertex with all its relationships

  MATCH (m:person {name: 'Michael'})
  DETACH DELETE m;

  DETACH 키워드를 함께 이용하면 해당 vertex와 연결되어 있는 edge도 함께 제거된다. 제거하려는 vertex가 다른 vertex와 edge로 연결된 경우 해당 edge를 먼저 제거하는 과정을 생략할 수 있다.

SET

SET절은 property를 추가, 설정, 제거를 하거나 vlabel을 추가하는 절이다.

• Add Property

  MATCH (E:person {name: 'Edward'})
  SET E.age = 20;

  MATCH절을 통해 property를 추가하려는 vertex나 edge를 찾고 SET절에 추가할 property의 이름과 값을 표기한다. Property 관련 사항을 표기할 때는 작은 따옴표, 큰 따옴표와 함께 표기한다.

• Modify Property

  MATCH (E:person {name: 'Edward'})
  SET E.age = 30;

  MATCH절을 통해 property를 변경하려는 vertex나 edge를 찾고 SET절에 변경할 property의 이름과 값을 표기한다. Property 관련 사항을 표기할 때는 작은 따옴표, 큰 따옴표와 함께 표기한다.

• Remove Property

  MATCH (E:person {name: 'Edward'})
  SET E.age = NULL;

  MATCH절을 통해 property를 제거하려는 vertex나 edge를 찾고 SET절에 제거할 property의 이름과 NULL을 표기한다.

• Copy properties between nodes and relationships

  MATCH (at {name:'Edward'}), (pn {name: 'Peter'})
  SET at = pn
  RETURN at.name, at.age, pn.name, pn.age;

  SET은 하나의 vertex/edge를 다른 vertex/edge로부터 모든 properties를 복사할 수 있다. at의 properties가 pn의 properties로 모두 복사되고 기존의 at의 properties는 모두 제거 된다.

• Replace all properties using a map and =

  MATCH (p { name: 'Edward' })
  SET p = { name: 'Edward William', position: 'Enterpreneur' }
  RETURN p.name, p.age, p.position;

  = 연산자를 사용하여 vertex/edge의 기존 properties를 map에의해 제공된 properties로 대체 할 수 있다.

• Remove all properties using an empty map and =

  MATCH (p { name: 'Edward' })
  SET p = { }
  RETURN p.name, p.age, p.position;

  = 연산자를 사용하여 vertex/edge의 기존 properties를 모두 제거할 수 있다.

• Mutate specific properties using a map and +=

  MATCH (p { name: 'Edward' })
  SET p += { age: 38, hungry: TRUE, position: 'Enterpreneur' }
  RETURN p.name, p.age, p.hungry, p.position;;

  += 연산자를 사용하여 vertex/edge의 기존 properties를 변경하거나, 새로운 properties를 추가할 수 있다. 아래와 같이 빈 map은 vertex 및 edge의 properties 가 제거 되지 않는다.

  MATCH (p { name: 'Edward' })
  SET p += { }
  RETURN p.name, p.age, p.hungry, p.position;

• Set multiple properties using one SET clause

  MATCH (n { name: 'Peter' })
  SET n.position = 'Developer', n.surname = 'Taylor';

  여러 properties을 쉼표(,)로 구분하여 한번에 설정한다.

REMOVE

REMOVE절은 property를 제거하는 절이다.

• Remove property

  MATCH (E:person {name: 'Edward'})
  SET E.habit = NULL;
  
  MATCH (E:person {name: 'Edward'})
  REMOVE E.habit;

  SET절을 이용하여 property를 제거하는 방법은 이미 SET 에서 설명한 바 있다. REMOVE절을 이용해서도 property를 제거할 수 있다.
  MATCH절을 통해 property를 제거하려는 요소를 찾고 REMOVE절에 제거할 property의 이름을 표기한다.

MERGE

MERGE절은 명시된 pattern이 graph 내에 존재하지 않으면 CREATE절처럼 해당 pattern을 추가하고, graph 내에 이미 존재한다면 단순히 MATCH절처럼 해당 pattern이 존재한다는 확인시켜주는 절이다. MERGE절이 인식하는 대상은 해당 절에 명시된 pattern 전체이다.

• Merge

  MERGE (:person {name: 'Edward'});
  
  MATCH (p:person {name: 'Edward'}) RETURN p;
  
  CREATE (:person {name: 'Edward'});

  MERGE절에 pattern을 명시하고 실행을 하면 해당 pattern이 graph 내에 존재하는지 아닌지를 알 수 있다. 이미 graph 내에 존재한다면 MATCH절처럼 기능을 수행하고, graph 내에 없다면 CREATE절처럼 해당 pattern을 새롭게 생성한다.

• Merge Path

  MERGE (E:person {name: 'Edward'})-[L:likes]->(A:person {name: 'Alice'})
  RETURN E, L, A;
  
  MERGE (E:person {name: 'Edward'})
  MERGE (A:person {name: 'Alice'})
  MERGE (E)-[L:likes]->(A)
  RETURN E, L, A;

  MERGE절이 인식하는 대상은 해당 pattern 전체이다. 명시된 pattern의 일부만 graph 내에 존재한다고 해서 존재하지 않는 부분만 새롭게 생성하는 것이 아니다. 위의 질의를 예로 들어 설명한다. 첫 번째 질의는 Edward, Alice vertex가 이미 graph 내에 존재하고 likes edge로 연결되어 있지 않다면 edge만 새롭게 생성되는 것이 아니다. 새롭게 Edward, Alice vertex가 생성되고 그 두 vertex 사이에 edge가 생성된다.
  Graph 속에 pattern의 일부 요소들이 이미 있는지 없는지 확실하게 알지 못한다면 두 번째 질의처럼 각 요소들을 나누어서 MERGE절을 사용하는 것이 좋다.

• ON CREATE SET and ON MATCH SET

  MERGE (E:person {name: 'Edward'})
  ON CREATE SET E.lastMERGEOP = 'CREATE'
  ON MATCH SET E.lastMERGEOP = 'MATCH'
  RETURN E.lastMERGEOP;

  ON CREATE SET, ON MATCH SET절을 활용하면 MERGE절의 동작 방식에 따른 property 설정이 가능하다. MERGE절이 CREATE절처럼 동작하였을 경우에는 ON CREATE SET절에 명시한 사항이 반영되고, MERGE절이 MATCH절처럼 동작하였을 경우에는 ON MATCH SET절에 명시한 사항이 반영된다.

UNION and UNION ALL

UNION절은 여러 질의의 결과들을 하나로 결합하는 역할을 수행한다.

• UNION and UNION ALL

  MATCH (a:person)
  WHERE 20 < a.age
  RETURN a.name AS name
  UNION
  MATCH (b:person)
  WHERE b.age < 50
  RETURN b.name AS name;
  
  MATCH (a:person)
  WHERE 20 < a.age
  RETURN a.name AS name
  UNION ALL
  MATCH (b:person)
  WHERE b.age < 50
  RETURN b.name AS name;

  두 질의 사이에 UNION 키워드를 표기하면 두 결과를 하나로 결합할 수 있다. UNION은 중복되는 값들을 한 번씩만 출력한다. UNION과 ALL 키워드를 함께 사용하면 두 결과를 하나로 결합할 때 중복되는 값들을 중복하는 그대로 출력한다.

LOAD FROM

LOAD FROM절을 사용하여 테이블 단위로 data를 loading 할 수 있다.

• create vertex

  LOAD FROM person AS v
  CREATE (:person {id: v.id, name: v.name});

• create edge

  LOAD FROM friend AS e
  MATCH (a:person),(b:person)
  WHERE id(a) = to_jsonb(e.start_id) 
    AND id(b) = to_jsonb(e.end_id)
  CREATE (a)-[:friend {date: '2018'}]->(b);

CREATE and DROP CONSTRAINT

property에 제약사항을 설정하여 데이터를 제어하는 기능을 제공한다.

• CREATE and DROP CONSTRAINT

  CREATE CONSTRAINT [constraint_name] ON label_name ASSERT field_expr IS UNIQUE
  CREATE CONSTRAINT [constraint_name] ON label_name ASSERT check_expr
  DROP CONSTRAINT constraint_name ON label_name
  
  CREATE CONSTRAINT ON person ASSERT id IS UNIQUE;
  CREATE CONSTRAINT ON person ASSERT name IS NOT NULL;
  CREATE CONSTRAINT ON person ASSERT age > 0 AND age < 128;

  constraint name은 생략하면 자동으로 지정되어 생성한다. UNIQUE는 해당 레이블에서 필드의 값이 유일하도록 제한한다. check_expr은 새로 입력되거나 수정으로 변경되는 property에 대해 참 또는 거짓 값을 반환한다. 결과가 거짓이면 해당 입력/변경은 실패한다. \dGv, \dG 명령어를 이용하여 vertex 및 edge의 정보 조회시 constraint를 함께 확인할 수 있다.

  CREATE VLABEL people;
  CREATE CONSTRAINT ON people ASSERT age > 0 AND age < 99;
  
  MERGE (s:people {name: 'David', age: 45});
  
  MATCH (s:people) return s;
                      s
  ------------------------------------------
   people[24.1]{"age": 45, "name": "David"}
  (1 row)
  
  
  MERGE (s:people {name: 'Daniel', age: 100}); 
  ERROR:  new row for relation "people" violates check constraint "people_properties_check"
  DETAIL:  Failing row contains (24.2, {"age": 100, "name": "Daniel"}).
  
  MERGE (s:people {name: 'Emma', age: -1}); 
  ERROR:  new row for relation "people" violates check constraint "people_properties_check"
  DETAIL:  Failing row contains (24.3, {"age": -1, "name": "Emma"}).
  
  MATCH (s:people) return s;
                      s
  ------------------------------------------
   people[24.1]{"age": 45, "name": "David"}
  (1 row)

Logarithmic

• log()
  log 함수는 숫자의 자연 로그(로그의 밑이 e임)를 반환한다.

  RETURN log(27);

  27의 자연 로그가 반환된다.

• log10()
  log10 함수는 숫자의 상용 로그(로그의 밑이 10임)를 반환한다.

  RETURN log10(27);

  27의 상용 로그가 반환된다.

• exp()
  exp 함수는 자연상수(e)를 밑으로 하고, 인자로 전달된 numeric 값을 지수로 하는 거듭제곱 값을 반환한다. 즉, exp(1)은 e¹ ≒ 2.71828182845905을, exp(2)은 e² ≒ 7.38905609893065을, exp(-1)은 e^-1 ≒ 0.367879441171442을 반환한다.

  RETURN exp(1);
  RETURN exp(2);
  RETURN exp(-1);

• sqrt()
  sqrt 함수는 인자로 전달된 numeric 값의 제곱근을 반환한다. sqrt 함수는 음수를 인자로 전달할 수 없다.

  RETURN sqrt(25);

Trigonometric

• sin()/cos()/tan()
  sin 함수는 인자로 전달된 numeric 값의 sine값을, cos 함수는 인자로 전달된 numeric 값의 cosine값을, tan 함수는 인자로 전달된 numeric 값의 tangent값을 반환한다. sin(),cos(),tan()은 radians 단위로 값을 출력하며, degrees 단위로 값을 출력하고자 할때는 sind(), cosd(), tand()를 사용한다.

  RETURN sin(0.5);
  RETURN sin(-1.5);
  
  RETURN cos(0);
  RETURN cos(-1);
  
  RETURN tan(0);
  RETURN tan(15.2);

• cot()/asin()/acos()/atan()/atan2()
  cot 함수는 인자로 전달된 numeric 값의 cotangent값(tangent의 역수)을, asin 함수는 인자로 전달된 numeric 값의 arcsine값(sine의 역)을, acos 함수는 인자로 전달된 numeric 값의 arccosine값(cosine의 역)을, atan, atan2 함수는 인자로 전달된 numeric 값의 arctangent값(tangen의 역)을 반환한다. asin, acos함수의 인자 범위는 -1 ~ 1 사이의 numeric 값이다. atan2는 인자가 두개인데 이는 atan 함수를 더욱 세밀하게 하기 위해서이다. 개념적으로 atan2(a, b)는 atan(a/b)와 같다. 하지만 atan(-5)는 atan(-15/3)과 atan(15/(-3))인지 확실히 알 수가 없다. 삼각함수에서 인자는 라디안 값이기 때문에 확실한 구분이 필요하다. 따라서 atan보다는 atan2를 사용하는 것이 더욱 값을 정확하게 해준다.

  RETURN cot(1.5);
  
  RETURN asin(1);
  
  RETURN acos(0.5);
  
  RETURN atan(-1);
  
  RETURN atan2(-1.5, 1.3);

• pi()/degrees()/radians()
  pi 함수는 pi를 숫자로 반환한다. degrees 함수는 전달된 인자를 라디안값으로 인식하여 디그리(각도)로 변환하여 반환하며, radians 함수는 전달된 인자를 디그리(각도)로 인식하여 라디안으로 변환하여 반환한다.

  RETURN pi();
  
  RETURN degrees(12.3);
  RETURN degrees(pi());
  
  RETURN radians(180);

Integer Types

smallint, integer 및 bigint type은 소수부가 없는 다양한 범위의 정수를 저장한다. 허용된 범위를 벗어난 값을 저장하려고 하면 에러가 발생한다.

• integer type : 범위, 저장소 크기 및 성능에서 최고의 균형점을 제공하므로 일반적으로 선택된다.
  
• smallint type : 일반적으로 디스크 공간이 부족한 경우에만 사용된다.
  
• bigint type : integer 타입의 범위가 불충분한 경우에 사용하기 위한 것이다.

SQL은 정수 타입 integer(또는 int), smallint 및 bigint만 지정한다. (int2, int4 및 int8로도 사용이 가능하다.)

Arbitrary Precision Numbers

numeric type은 자릿수의 매우 큰 숫자를 저장할 수 있고, 계산을 정확하게 수행한다. 특히 금액 및 정확성이 요구되는 기타 수량을 저장할 때 권장된다. 그러나, numeric 값의 산술은 다음 절에서 설명되는 정수 타입 또는 부동 소수점 타입에 비해 매우 느리다.

numeric의 scale은 소수점 오른쪽에 있는 소수부의 자릿수이다. numeric의 precision은 전체 숫자의 유효 자릿수의 총 개수이다. 즉, 소수점을 기준으로 양쪽 모두의 자릿수이다. 따라서 숫자 23.5141의 precision은 6이고, scale은 4이다. 정수는 Scale 0으로 간주할 수 있다.
 
numeric 컬럼의 최대 전체 자릿수 및 최대 소수 자릿수를 모두 구성할 수 있다.
 
타입 numeric의 컬럼을 선언하려면 다음 구문을 사용한다.

NUMERIC(precision, scale)

전체 자릿수는 양의 값이어야 하고, 소수 자릿수는 0 또는 양의 값이어야 한다.

NUMERIC(precision)

전체 자릿수 또는 소수 자릿수 없이 아래와 같이 지정하면, 이것은 소수 자릿수 0을 선택한다.

NUMERIC

Precision, scale값 명시 없이 precision, scale 값을 저장할 수 있는 numeric 컬럼을 생성하면, precision의 값까지 저장할 수 있다. 이런 종류의 컬럼은 특정 scale을 명시하지 않으면 허용 가능한 크기로 제약없이 사용가능하나 scale 값이 명시된 numeric 컬럼은 scale 값에 제약을 받게 된다. (데이터 이관시에는 전체 자리수와 소수 자리수를 항상 지정하도록 한다.)

참고: 타입 선언에서 명시적으로 지정할 경우 최대 허용 전체 자릿수는 1000이다. 전체 자릿수를 지정하지 않은 NUMERIC은 표에 설명된 범위로 제한된다.

저장할 값의 소수 자릿수가 컬럼에서 선언된 소수 자릿수보다 큰 경우 시스템은 지정된 소수 자릿수로 값을 반올림한다. 그런 다음, 소수점 이하 자릿수가, 선언된 전체 자릿수에서 선언된 소수 자릿수를 뺀 것을 초과하면 에러가 발생한다. 숫자 값은 여분의 ‘0’ 값 없이 물리적으로 저장된다. 따라서 선언된 컬럼의 전체 자릿수 및 소수 자릿수는 고정 할당이 아닌 최대이다. 이런 의미에서 numeric 타입은 char(n)보다는 varchar(n)에 가깝다. 실제 저장소 요구 사항은 4자리 그룹별로 2바이트에, 8바이트 오버헤드에 대해 3을 더한 것이다.
 
타입 decimal 및 numeric은 동일하다. 두 가지 타입 모두 SQL 표준이다.

Floating-Point Types

데이터 타입 real 및 double precision은 부정확한 가변 전체 자릿수 숫자 타입이다. 부정확이란, 일부 값을 내부 형식으로 정확하게 변환할 수 없고 근사값으로 저장되므로 값의 저장 및 검색이 약간 불일치할 수 있다는 의미이다.

• 정확한 저장소 및 계산이 필요한 경우(예: 금액), numeric 타입을 대신 사용한다.
• 어떤 중요한 이유로 이 타입을 사용하여 복잡한 계산을 해야 할 경우, 특히 boundary cases(infinity, underflow)에서 특정 동작이 필요할 경우 구현을 신중하게 해야 한다.
• 두 개의 부동 소수점 값이 동등한지에 대한 비교가 항상 예상한 대로 작동하지 않을 수 있다.

대부분의 플랫폼에서 real 타입은 최소 범위가 1E-37 ~ 1E+37이고, 전체 자릿수는 최소 6자리이다. double precision 타입은 일반적으로 최소 15자릿수의 전체 자릿수로 1E-307 ~ 1E+308 정도의 범위이다. 너무 크거나 작은 값은 에러가 발생한다. 입력 숫자의 전체 자릿수가 너무 클 경우 반올림될 수도 있다. 0은 아닌 것으로 표시하기 어려울 정도로 숫자가 0에 근접하면 underflow 에러가 발생한다.

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 컬럼을 생성하여 sequence에서 할당될 기본값으로 정렬한다. NOT NULL 제약 조건은 null 값이 삽입되지 않도록 하기 위해 적용된다. (대부분의 경우 UNIQUE 또는 PRIMARY KEY 제약 조건으로 중복 값이 우연히 입력되지 않게 할 수 있으나 자동으로 생성되지 않는다.)
마지막으로, sequence는 컬럼 “소유자(owned by)”로 마킹되므로 컬럼 또는 테이블을 삭제하지 않으면 삭제되지 않는다.
 
sequence의 다음 값을 serial 컬럼에 삽입하기 위해 serial 컬럼 기본값을 지정한다. 이것은 INSERT문의 컬럼 목록에서 컬럼을 제외하거나 DEFAULT 키워드를 사용함으로써 가능하다.

• serial type은 serial4과 동일하다. 둘 다 integer 컬럼을 생성한다.
• bigserial type 및 serial8 type은 bigint 컬럼을 생성할 때 외에는 동일하게 작동된다. bigserial은 테이블 수명 내내 식별자를 231개 이상 사용할 것으로 예상하는 경우에 사용해야 한다.
• smallserial type 및 serial2 type은 smallint 컬럼을 생성할 때 외에는 동일하게 작동된다.

serial 컬럼에 대해 생성된 시퀀스는 소유 컬럼이 삭제되면 자동으로 삭제된다. 컬럼을 삭제하지 않고 시퀀스를 삭제할 수 있지만 그럴 경우 컬럼 기본 표현식이 강제로 삭제된다.

Date/Time Input

날짜 및 시간 입력은 일, 월, 년의 순서를 아래와 같이 지정 할 수 있다.

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

월-일-년 해석을 선택하려면 DateStyle 파라미터를 MDY로 설정하고, 일-월-년 해석을 선택하려면 DMY로 설정하고, 년-월-일 해석을 선택하려면 YMD로 설정한다.

날짜 또는 시간 입력은 텍스트 문자열처럼 앞뒤에 작은따옴표를 사용해야 한다.

• 날짜
  Date types 에서 사용 가능한 타입이다.

• 시각
  시각 타입은 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 값에 기록된다.

  표 - [시각 입력]

표 - [시간대 입력]

시간대를 지정하는 방법은 아래 Time Zones 절 참조한다.

• 타임 스탬프
  타임 스탬프 타입에 대한 유효 입력은 날짜 및 시간의 연결로 구성되며, 그 뒤에 선택적 시간대, 그 뒤에 선택적 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'

• 특수 값
  표에 표시된 대로 편의를 위해 몇 가지 특수 날짜/시간 입력 값을 지원한다. 값 infinity 및 -infinity는 시스템 내부에서 특별히 표현되며, 변경 없이 표시되지만, 그 외에는 판독 시 기본 날짜/시간 값으로 변환되는 간단한 축약어이다. (특히, now 및 관련 문자열은 판독되는 순간에 특정한 시간 값으로 변환된다.) 이 값을 모두 SQL 명령에서 상수로 사용하는 경우 앞뒤로 작은따옴표를 사용해야 한다.

다음 SQL 호환 함수를 사용하면 해당 데이터 타입 CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP에 현재 시간값을 얻을 수도 있다.

Date/Time Output

날짜/시간 타입의 출력 형식은 4가지 스타일 ISO 8601, SQL(Ingres), 전형적인 POSTGRES(Unix 날짜 형식) 또는 German 중 하나로 설정할 수 있다. 기본값은 ISO 형식이다.

다음 표는 각 출력 스타일의 예제를 보여준다. date 및 time 타입의 출력은 제시된 예제에 따라 일반적으로 날짜 또는 시간 부분만 있다.

DMY 필드 순서가 지정된 경우 월,일 순으로 출력되며, 그 외에는 일,월 순으로 출력된다. 아래 표는 예제이다.

날짜/시간 스타일은 SET datestyle 명령, postgresql.conf 구성 파일의 DateStyle 파라미터 또는 서버나 클라이언트의 PGDATESTYLE 환경 변수를 사용하여 사용자가 선택할 수 있다. 형식 지정 함수 to_char는 좀 더 유연한 방식으로 날짜/시간 출력 형식을 지정할 수 있다.

Time Zones

Time Zone 및 표기법은 지형적인 것 외에도 정치적 결정의 영향을 받는다. 전세계의 시간대는 1900년대에 표준화되었지만 서머타임 규정 등에 의해 계속 바뀌고 있다. AgensGraph는 IANA(Olson) 시간대 데이터베이스를 이용한다. 미래의 시간의 경우, 지정된 시간대에 대해 알려진 최신 규칙은 먼 미래에도 무기한으로 계속 준수될 것으로 간주한다. 그러나 날짜, 시간 타입 및 기능의 특이한 혼합을 갖고 있다.
2가지 명백한 문제는 다음과 같다.

• date 타입은 연결된 시간대가 없지만 time 타입은 가능하다. 실세계에서 Time Zone은 서머타임 경계가 포함된 연도를 통해 오프셋이 달라질 수 있으므로 날짜 및 시간과 연결되지 않는 한 의미가 없다.  
  
• 기본 Time Zone는 UTC로부터 상수 숫자 오프셋으로 지정된다. 따라서, DST 경계를 가로지르는 날짜/시간 계산 수행 시 서머타임에 적응하기 불가능할 수 있다.

이러한 문제를 해결하기 위해 Time Zone을 사용할 경우 우리는 날짜 및 시간이 모두 포함된 날짜/시간 타입의 사용을 권장한다(호환을 위해 지원은 하지만 time with time zone 타입 사용을 권장하지 않는다). 날짜 또는 시간만 포함하고 있는 타입에 대해 지역 시간대를 가정한다. Time Zone의 날짜 및 시간은 내부적으로 UTC로 저장된다. 이것은 클라이언트에게 표시되기 전에 TimeZone 구성 파라미터에 의해 지정되는 Time Zone에서 지역 시간으로 변환된다. 3가지 서로 다른 형식으로 Time Zone을 지정할 수 있도록 허용한다.

• 전체 시간대 이름. 예를 들면, America/New_York. 인식된 시간대 이름은 pg_timezone_names 뷰에 나열된다. (동일 시간대 이름은 기타 다수의 소프트웨어에서도 인식된다.)
   
  
• 시간대 약어. 예를 들면, PST. 서머타임 전환 날짜 규칙 집합을 암시할 수 있는 전체 시간대 이름과 대조적으로, 해당 사양은 단순히 UTC로부터 특정 오프셋을 정의한다. 인식된 약어는 pg_timezone_abbrevs 뷰에 나열된다. 구성 파라미터 TimeZone 또는 log_timezone을 시간대 약어로 설정할 수 없지만 날짜/시간 입력 값에서 약어 및 AT TIME ZONE 연산자를 사용할 수 있다.
   
  
• 시간대 이름 및 약어 외에, STDoffset 또는 STDoffsetDST의 POSIX 스타일 시간대 사양을 수용한다. 여기서, STD는 지역 약어이고, offsetUTC로부터 서쪽의 시간에 대한 숫자 오프셋이고, DST는 지정된 오프셋에서 한 시간 앞선 것으로 간주되는 선택적 서머타임 지역 약어이다. 예를 들어, EST5EDT는 아직 인식된 지역 이름이 아닌 경우에 이것이 수용되고, 미국 동해안 시간과 기능적으로 동일해진다. 이 구문에서, 지역 약어는 글자의 문자열 또는 꺾쇠괄호(<>)를 사용한 임의의 문자열일 수 있다. 서머타임 지역 약어가 존재하는 경우 IANA 시간대 데이터베이스의 posixrules 항목에서 사용되는 것과 동일한 서머타임 전환 규칙에 따라 사용되는 것으로 간주된다. 표준 AgensGraph 설치에서, posixrules는 US/Eastern과 동일하므로 POSIX 스타일 시간대 사양은 USA 서머타임 규칙을 준수한다. 필요한 경우 posixrules 파일을 교체함으로써 이러한 행동을 조절할 수 있다.

요약하면, 이것은 약어 및 전체 이름 간의 차이이다. 약어는 UTC로부터 특정한 오프셋을 대표한다. 반면, 전체 이름 다수는 지역 서머타임 규칙을 암시하므로 2개의 가능한 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 시간대 지역 이름에서 그리니치의 위치 west에 대해 양의 오프셋이 사용된다는 것이다. 그 외 지역에서는, AgensGraph는 양의 시간대 오프셋이 그리니치의 east인 ISO-8601 표기법을 준수한다. 시간대 이름 및 약어는 대소문자를 구분하여 인식된다.

시간대 이름 또는 약어는 서버에 내장되지 않는다. 설치 디렉토리의 …/share/timezone/ 및 …/share/timezonesets/ 아래에 저장된 구성 파일로부터 구한다. TimeZone 구성 파라미터는 postgresql.conf 다른 표준 방법으로 설정 가능하다. 이것을 설정하는 특수한 방법이 몇 가지 있다.

• SQL 명령 SET TIME ZONE은 세션에 대한 시간대를 설정한다. SQL 사양에 좀 더 호환되는 구문인 SET TIMEZONE TO를 대신 사용할 수 있다.
• PGTZ 환경 변수는 연결시 SET TIME ZONE 명령을 서버에 전송하기 위해 libpq 클라이언트에서 사용된다.

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로 설정된 경우에 간격 출력에도 사용된다.

일, 시, 분 및 초의 양은 명시적 단위 마킹 없이 지정할 수 있다. 예를 들어, ’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를 포함할 수 있다. 사용 가능한 단위 약어는 아래의 표에 나와 있다. 단위는 생략할 수 있으며, 임의의 순서로 지정할 수 있지만 1일 미만의 단위는 T 뒤에 나타나야 한다. 특히, M의 의미는 이것이 T 앞에 있는지, 아니면 뒤에 있는지에 따라 달라진다.

표 [ISO 8601 간격 단위 약어]

대체 형식 :

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

문자열은 P로 시작되어야 하며, T는 간격의 날짜와 시간을 분리한다. 값은 ISO 8601 날짜와 유사한 숫자로 지정된다. fields 사양으로 간격 상수를 작성하는 경우 또는 fields 사양으로 정의된 간격 컬럼에 문자열을 할당하는 경우 마킹되지 않은 수량의 해석은 fields에 따라 달라진다. 예를 들어 INTERVAL ‘1’ YEAR는 1년으로 해석되는 반면, INTERVAL ’1’은 1초를 의미한다. 또는 fields 사양에서 허용하는 최하위 오른쪽 필드 값은 무시된다.

예를 들면, INTERVAL ‘1 day 2:03:04’ HOUR TO MINUTE는 결과적으로 초 필드를 삭제하지만 날짜 필드는 삭제하지 않는다. SQL 표준에 따라 간격 값의 모든 필드는 부호가 동일해야 하므로 선행 음의 부호는 모든 필드에 적용된다. 예를 들어, 간격 리터럴 ‘-1 2:03:04’에서 음의 부호는 날짜 및 시/분/초 부분에 모두 적용된다. 필드가 서로 다른 부호를 갖는 것을 허용하며, 전통적으로 텍스트 표현에서 각 필드의 부호를 독립적으로 처리하므로 시/분/초 부분은 이 예제에서 양의 값으로 간주된다. IntervalStyle이 sql_standard로 설정되면 선행 부호는 모든 필드에 적용되는 것으로 간주된다(단, 추가 부호가 없을 경우).
 
모호성을 피하려면 필드가 음인 경우 명시적으로 부호를 첨부하는 것이 좋다. 내부적으로 interval 값은 월, 일 및 초로 저장된다. 이것은 월의 날짜 수가 다르기 때문이고, 하루는 서머타임에 따라 23 또는 25시간일 수 있다. 월 및 일 필드는 정수이고, 초 필드는 소수로 저장할 수 있다. 간격은 보통 상수 문자열 또는 timestamp 감산에서 생성되므로 이 저장 방법은 대부분의 경우에 문제가 없다. 함수 justify_days 및 justify_hours는 일반 범위에서 오버플로되는 일 및 시를 조절할 때 유용하다. 자세한 입력 형식 및 좀 더 간략한 입력 필드의 일부 필드에서 필드 값은 소수부를 가질 수 있다. 예를 들면, ’1.5 week’ 또는 ‘01:02:03.45’. 이러한 입력은 저장을 위해 적절한 월, 일 및 초 수로 변환된다. 이것은 결과적으로 월 또는 일의 소수일 경우 소수가 변환 계수 1월 = 30일 및 1일 = 24시간을 사용하여 하위 필드에 추가된다. 예를 들면, ’1.5 month’는 1개월 15일이다. 출력의 소수로서 초만 표시된다. 아래의 표는 유효 interval 입력의 몇 가지 예시를 보여준다.

표 [간격 입력]

Interval Output

간격 타입의 출력 형식은 명령 SET intervalstyle을 사용하여 4가지 스타일 sql_standard, postgres, postgres_verbose 또는 iso_8601 중에서 하나로 설정할 수 있다. 기본값은 postgres 형식이다. 표는 각 출력 스타일의 예제를 보여준다. sql_standard 스타일은 간격 값이 표준 제한을 만족하는 경우(양의 값 및 음의 값을 혼용하지 않는 경우 년-월만 또는 일-시만 해당)에 간격 리터럴 문자열에 대한 SQL 표준 사양을 준수하는 출력을 생성한다. 그 외에는, 부호 혼합 간격의 혼란을 없애기 위해 부호가 명시적으로 추가되고 표준 년-월 리터럴 문자열 뒤에 일-시 리터럴 문자열이 오는 것처럼 출력이 보인다.

표 [간격 출력 스타일 예제]

Points(점)

Point는 Geometric Types에 대한 기본적인 2차원 빌딩 블록이다. point type의 값은 다음 구문 중 하나를 사용하여 지정된다.

( x , y )
  x , y

여기서 x 및 y는 각각 부동 소수점 좌표이다. 점은 첫 번째 구문을 사용하여 출력된다.

Lines(선)

Line은 선형 방정식 Ax + By + C = 0으로 표현된다. 여기서 A 및 B는 둘 다 0이 아니다. line type의 값은 다음 양식의 입력 및 출력이다.

{ 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은 Line Segment의 끝점을 구성하는 점의 쌍으로 표현된다. lseg type의 값은 다음 구문 중 하나를 사용하여 지정된다.

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

여기서 (x1,y1) 및 (x2,y2)는 Line Segment의 끝점이다. Line Segment은 첫 번째 구문을 사용하여 출력된다.

Boxes(상자)

Box는 Box의 맞은편 모서리를 구성하는 점의 쌍으로 표현된다. box type의 값은 다음 구문 중 하나를 사용하여 지정된다.

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

여기서 (x1,y1) 및 (x2,y2)는 Box의 맞은편 모서리 2개이다. Box는 두 번째 구문을 사용하여 출력된다. 맞은편 모서리 두 개는 입력으로 제공되지만 값은 오른쪽 위 모서리와 왼쪽 아래 모서리로 저장하기 위해 필요 시 재정렬된다.

Paths(경로)

Path는 연결된 점의 목록으로 표현된다. 목록의 첫 번째 점과 마지막 점이 연결되지 않은 것으로 생각되면 열린 Path이고, 첫 번째 점과 마지막 점이 연결된 것으로 생각되면 닫힌 Path이다. path type의 값은 다음 구문 중 하나를 사용하여 지정된다.

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

여기서, 점들은 경로를 구성하는 선분의 끝점이다. 각괄호([])는 열린 path를 나타내고 소괄호(())는 닫힌 path를 나타낸다. 세 번째부터 다섯 번째 구문에서처럼 맨 바깥쪽 소괄호가 생략된 경우 닫힌 path로 간주된다. path는 두 번째 구문을 적절하게 사용하여 출력된다.

Polygons(다각형)

Polygon은 점 목록(Polygon 꼭지점)으로 표현된다. Polygon은 닫힌 경로와 매우 유사하지만 다르게 저장되며 자체적으로 지원되는 루틴 집합이 있다. polygon type의 값은 다음 구문 중 하나를 사용하여 지정된다.

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

여기서, 점들은 Polygon의 경계를 구성하는 선분의 끝점이다. Polygon은 첫 번째 구문을 사용하여 출력된다.

Circles(원)

Circle은 중심점과 반경으로 표현된다. circle type의 값은 다음 구문 중 하나를 사용하여 지정된다.

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

여기서 (x,y)는 Circle의 중심점이고 r은 반경이다. Circle은 첫 번째 구문을 사용하여 출력된다.

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 type은 입력값이 문서 형 선언(DTD)로 지정되더라도 DTD에 대한 입력값을 확인하지 않는다. XML스키마와 같은 다른 XML스키마 언어에 대한 유효성을 확인하는 기능도 지원하지 않는다.

xml에서 문자열 값을 생성하는 역 연산은 아래의 함수를 사용한다.

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

type은 character, character varying, text(또는 그중의 alias)일 수 있다. SQL 표준에 따르면 XML과 문자 유형을 변환하는 유일한 방법이지만 값을 단순히 캐스팅 할 수도 있다.

문자열 값이 XMLPARSE 또는 XMLSERIALIZE를 통하지 않고 xml 유형으로 캐스팅 되거나 XMLSARIALIZE를 통과 할 때 DOCUMENT와 CONTENT의 선택은 표준 명령을 사용하여 설정할 수 있는 “XML 옵션” 세션 구성 매개 변수에 의해 결정된다.

SET xmloption TO { DOCUMENT | CONTENT };

기본값은 CONTENT 이므로 XML 데이터의 모든 형식이 허용된다.

참고 : 기본 XML 옵션 설정을 사용하면 XML 내용 단편의 정의가 XML을 허용하지 않기 때문에 문서 유형 선언이 포함 된 문자열을 XML 형식으로 직접 변환 할 수 없다. XMLPARSE를 사용 하거나 XML 옵션을 변경해야 한다.

Encoding Handling

클라이언트에서 복수의 문자 인코딩을 처리할 경우와 이를 통해 XML 데이터가 전달될 경우 주의해야 한다. 쿼리를 서버에 전달하고 쿼리 결과를 클라이언트에 전달하기 위해 텍스트 모드를 사용하는 경우(정상 모드), 클라이언트와 서버 사이에 전달되는 모든 문자 데이터를 변환하고, 각각의 끝에서 문자 인코딩을 역으로도 변환한다.

이것은 위의 예제에 있는 것처럼 XML 값의 문자열 표현을 포함한다. 보통 이것은, 임베드된 인코딩 선언은 바뀌지 않으므로 클라이언트와 서버 사이의 전송 중에 문자 데이터가 다른 인코딩으로 변환되면 XML 데이터에 포함된 인코딩 선언이 무효화될 수 있다는 것을 의미한다. 이러한 행동에 대처하기 위해 XML 타입 입력에 대해 존재하는 문자 문자열에 포함된 인코딩 선언은 무시되고 CONTENT는 현재 서버 인코딩인 것으로 간주된다.

결과적으로, 올바른 처리를 위해 XML 데이터의 문자열은 현재 클라이언트 인코딩으로 클라이언트로부터 전송되어야 한다. 도큐먼트를 서버에 전송하기 전에 현재 클라이언트 인코딩으로 변환할 것인지, 아니면 클라이언트 인코딩을 적절히 조절할 것인지는 클라이언트의 책임이다. 출력에서 타입 XML의 값은 인코딩 선언이 없으며, 클라이언트는 모든 데이터가 현재 클라이언트 인코딩인 것으로 간주한다.
쿼리 파라미터를 서버에 전달하고 쿼리 결과를 클라이언트에 다시 전달하기 위해 바이너리 모드를 사용하는 경우 문자 집합 변환이 수행되지 않으므로 상황이 어렵게 된다.

이 경우, XML 데이터의 인코딩 선언이 준수되고, 부재 시 데이터가 UTF-8인 것으로 간주된다(XML 표준에서 요구하는 바이며 UTF-16을 지원하지 않는다는 점에 유의). 출력에서, 데이터는 클라이언트 인코딩이 UTF-8가 아닌 한, 클라이언트 인코딩을 지정하는 인코딩 선언을 갖는다. 그럴 경우 생략된다.

XML 데이터를 처리하는 것은 에러 발생 가능성이 적고, XML 데이터 인코딩, 클라이언트 인코딩 및 서버 인코딩이 동일한 경우에 좀 더 효율적이다. XML 데이터는 내부적으로 UTF-8로 처리되므로 서버도 UTF-8인 경우에 계산이 가장 효율적이다.

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

Accessing XML Values

XML 데이터 타입은 비교 연산자를 제공하지 않는다는 점에서 특이하다. 이것은 잘 정의(well-defined)되지 않고 보편적으로 유용한 XML 데이터에 대한 비교 알고리즘이 없기 때문이다. 이것의 결론 중 하나는 xml 컬럼과 검색 값을 비교하여 행을 검색할 수 없다는 것이다.

따라서, ID 같은 별개의 키 필드로로 XML과 병용해야 한다. XML 값 비교를 위한 대체 솔루션은 먼저 문자열로 변환하는 것이지만, 문자열 비교는 유용한 XML 비교 방법과 아무 관계가 없다는 점에 유의해야 한다.
XML 데이터 타입에 대한 비교 연산자가 없으므로 이 타입의 컬럼에 인덱스를 직접 생성하는 것도 불가능하다. XML 데이터의 신속 검색이 필요한 경우에 가능한 해결책에는 표현식을 문자 스트링 타입으로 캐스트하고 인덱싱하거나 XPath 표현식을 인덱싱하는 것이 포함된다. 물론, 실제 쿼리는 인덱스된 표현식에 의해 검색이 조절되도록 해야 한다.

텍스트 검색 기능은 XML 데이터의 전체 문서 검색 속도를 올리는 데에도 사용할 수 있다. 그러나 필요한 전처리 지원은 아직 사용 가능한 것이 없다.

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

포함 조건(containment)을 테스트하는 것은 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 도큐먼트(데이텀) 내의 키 또는 키/값 쌍을 효율적으로 검색하는 데 사용할 수 있다. 성능과 유연성 트레이드 오프가 서로 다른 2개의 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의 3가지 모두 해시 값으로 통합하면서 생성된다.

따라서, 이러한 구조를 찾는 제약 조건 쿼리는 결과적으로 극단적인 특정 인덱스 검색이 된다.
그러나 foo가 키로 나타날 것인지 여부는 전혀 알아낼 방법이 없다.

반대로, jsonb_ops 인덱스는 foo, bar 및 baz를 각각 나타내는 3가지 인덱스 항목을 생성한다. 그런 다음, 제약 조건 쿼리를 실행하려면 이러한 항목을 세 가지 모두 포함하는 행을 조회한다. GIN 인덱스는 해당 AND 검색을 매우 효율적으로 수행할 수 있는 반면, 특히 3개의 인덱스 항목 중 하나를 포함하는 행이 수가 매우 많은 경우 동등한 jsonb_path_ops 검색보다는 덜 구체적이고 느리다.

jsonb_path_ops 접근법의 단점은 {“a”: {}} 같은 값을 포함하지 않는 JSON 구조에 대해 인덱스 항목을 생성하지 않는다는 것이다. 해당 구조가 포함된 도큐먼트 검색이 요청되는 경우 전체 인덱스 스캔이 필요한데, 매우 느리다.
따라서 jsonb_path_ops는 해당 검색을 빈번하게 수행하는 어플리케이션에 부적합하다.

JSONB도 btree 및 hash 인덱스를 지원한다. 이것은 전체 JSON 도큐먼트의 동등성을 검사하는 경우에만 일반적으로 유용하다. JSONB 데이터의 btree 순서 지정은 그다지 중요하지 않지만, 완성도를 위해서 필요하다.

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 ...

원시 JSON 값은 기본 데이터 타입에 대한 것과 동일한 비교 규칙을 사용하여 비교된다. 문자열은 기본 데이터베이스 콜레이션을 사용하여 비교된다.

Declaration of Array Types

배열 타입의 사용을 설명하기 위해 아래와 같은 테이블을 생성해보자.

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

표시된 대로 배열 데이터 타입은 대괄호([])가 배열 엘리먼트의 데이터 타입 이름에 추가되어 명명된다.
위의 명령은 타입 text (name)의 컬럼을 갖는 sal_emp라는 테이블을 생성하는데, 타입 integer (pay_by_quarter)의 1차원 배열은 직원의 분기별 급료를 나타내고, text (schedule)의 2차원 배열은 직원의 주간 스케줄을 나타낸다.

CREATE TABLE에 대한 구문은 지정할 배열의 정확한 크기를 허용한다.
예를 들면 아래와 같다.

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

다만, 현재의 구현은 제공된 배열 크기 제한을 무시한다. 즉, 동작은 지정되지 않은 길이의 배열과 동일하다. 현재 구현에서는 선언 된 차원 수를 강제하지 않는다. 특정 엘리먼트 유형의 배열은 크기 또는 차원 수에 관계없이 모두 동일한 유형으로 간주된다.

따라서 CREATE TABLE 에서 배열 크기 또는 차원 수를 선언하는 것은 단순히 문서화한 것이며 런타임 동작에 영향을 미치지 않는다.

키워드 ARRAY를 사용하여 SQL표준을 준수하는 대체 구문을 1차원 배열에 사용할 수 있다. 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}}'

이 상수는 3개의 정수 서브 배열로 구성된 2차원 3x3 배열이다. 배열 상수의 엘리먼트를 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를 사용해서 나타낸다.

예를 들어, 이 쿼리는 주 중 첫 2일의 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)

non-slice 경우에 혼동을 피하기 위해서는 [2][1:1]이 아닌 [1:2][1:1]과 같은 모든 차원에 대해 slice 구문을 사용하는 것이 가장 좋다.

슬라이스 지정자의 lower-bound 또는 upper-bound를 생략 할 수 있다. 다음 예와 같이 누락 된 바운드는 배열의 lower 또는 upper로 대체된다.

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 가능하다.

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 할 수 있다.

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

또는 일부만 update 가능하다.

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

생략 된 lower-bound 또는 upper-bound 슬라이스 구문 은 NULL이나 0이 아닌 배열 값을 업데이트 할 때만 사용할 수 있다.

저장된 배열 값은 아직 존재하지 않는 엘리먼트에 할당함으로써 확대 가능하다.
이전에 존재한 엘리먼트와 새로 할당된 엘리먼트 사이의 위치는 null로 채워진다.

예를 들면, 배열 myarray에 현재 4개의 엘리먼트가 있을 경우 myarray[6]에 할당하는 업데이트 후에 6개의 엘리먼트를 갖는다. myarray[5]는 null을 포함한다.
현재, 이러한 방식으로의 확대는 다차원 배열이 아닌 1차원 배열에만 허용된다.

첨자를 붙인 할당은 첨자가 1부터 시작되지 않는 배열의 생성을 허용한다. 예를 들면, 첨자 값이 -2~7인 배열을 생성하려면 myarray[-2:7]에 할당할 수 있다.

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

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)

연결(concatenation) 연산자를 사용하면 단일 엘리먼트를 1 차원 배열의 처음 또는 끝에 밀어 넣을 수 있다. 이것은 2개의 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)

차원 수가 동일한 2개의 배열을 연결(concatenation)하는 경우 결과는 왼쪽 피연산자의 외부 차원의 하한계 첨자를 보유한다.
다음 예와 같이 결과는 왼쪽 피연산자의 모든 엘리먼트를 구성하는 배열 다음에 오른쪽 피연산자의 모든 엘리먼트가 오는 것이다.

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+1 차원 배열의 처음부터 끝까지 N 차원 배열을 밀어 넣을 경우 결과는 위의 엘리먼트 배열 사례와 유사하다.
다음 예와 같이 각 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을 사용하여 함수를 생성할 수도 있다. 처음 2개는 1차원 배열만 지원하지만, array_cat는 다차원 배열을 지원한다.

위에서 논의된 연결(concatenation) 연산자는 이러한 함수의 직접 사용을 선호한다. 사실, 이 함수들은 기본적으로 연결(concatenation) 연산자를 구현할 때 사용하기 위해 존재한다.

그러나, 사용자 정의된 집계를 생성할 때에도 직접적으로 유용할 수 있다.
함수의 사용은 아래와 같다.

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}

위의 예에서 parser는 연결 연산자의 한쪽에 정수 배열을보고 다른 연산자에는 불확정 유형의 상수를 본다. 상수 유형을 분석하기 위해 사용하는 경험적 방법은 연산자의 다른 입력과 동일한 유형 (이 경우 정수 배열)이라고 가정하는 것이다.
그래서 연결 연산자는 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과 일치할 경우에 엘리먼트 값 앞뒤에 큰따옴표를 사용한다.
엘리먼트 값에 임베드된 큰따옴표 및 역슬래시는 역슬래시로 이스케이프된다. 숫자 데이터 타입의 경우 큰따옴표가 절대 나타나지 않는다고 간주하는 것이 안전하지만, 텍스트 데이터 타입의 경우 인용부호의 존재 또는 부재에 대처해야 한다.

기본적으로, 배열의 차원에서 하한계 인덱스 값은 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과 일치하는 문자열도 인용부호를 사용해야 한다. 큰따옴표 또는 역슬래시를 인용된 배열 엘리먼트 값에 넣으려면 이스케이프 문자열 구문을 사용하고 역슬래시를 앞에 넣어야 한다.

또는, 배열 구문으로 잘못 처리될 수도 있는 모든 데이터 문자를 보호하기 위해 인용부호를 피하고 역슬래시 이스케이핑을 사용할 수 있다.
왼쪽 중괄호 앞 또는 오른쪽 중괄호 뒤에 공백을 추가할 수 있다. 개별 항목 문자열 앞 또는 뒤에 공백을 추가할 수도 있다. 이 모든 사례에서 공백은 무시된다.
그러나, 큰따옴표 엘리먼트 내의 공백 또는 엘리먼트의 비 공백 문자 양쪽에 있는 공백은 무시된다.

Built-in Range Types

다음과 같은 기본 제공 범위 타입이 제공된다.

int4range - Range of integer

int8range - Range of bigint

numrange - Range of numeric

tsrange - Range of timestamp without time zone

tstzrange - Range of timestamp with time zone

daterange - Range of date

또한, 자체 범위 타입을 정의할 수 있다. 자세한 내용은 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

비어 있지 않은 모든 범위는 2개의 경계, 하한계와 상한계를 갖는다.
이 값 사이의 모든 점은 범위에 포함된다. 경계 포함은 경계 점 자체는 범위에 포함된다는 것을 의미하며, 경계 제외는 경계 점이 범위에 포함되지 않음을 의미한다.

범위의 텍스트 양식에서, 하한계 포함은 “[”로 표현되고 하한계 제외는 ”(”로 표현된다. 비슷하게, 상한계 포함은 ”]”로 표현되고, 상한계 제외는 “)”로 표현된다.

함수 lower_inc 및 upper_inc는 범위 값의 하한계 및 상한계를 각각 테스트한다.

Infinite (Unbounded) Ranges

범위의 하한계는 생략할 수 있는데, 상한계 미만의 모든 점들이 범위에 포함된다는 것을 의미한다. 비슷하게, 범위의 상한계가 생략된 경우 하한계 이상의 모든 점들이 범위에 포함된다. 하한계 및 상한계 모두 생략되면 엘리먼트 타입의 모든 값이 범위에 포함되는 것으로 간주된다.

이것은 하한계가 “음의 무한” 또는 상한계가 “양의 무한”인 것으로 간주하는 것과 동일하다. 그러나, 이러한 무한 값은 범위의 엘리먼트 타입의 값이 절대 아니며, 범위의 일부일 수 없다는 점에 유의하라. (따라서, 무한 경계 포함 같은 류가 없다. 한 가지를 작성하려고 하면 자동으로 경계 제외로 변환된다.)

또한, 일부 엘리먼트 타입은 “무한” 표시법을 갖고 있지만, 범위 타입 메커니즘에 관한 한 이것은 또 다른 값이다. 예를 들면, 타임 스탬프 범위에서 [today,]는 [today,)와 동일한 것을 의미한다. 그러나 [today,infinity]는 [today,infinity)와 의미가 다를 때도 있다. 후자는 특수 timestamp 값 infinity가 제외된다.

함수 lower_inf 및 upper_inf는 각각 범위의 무한 하한계 및 상한계를 테스트한다.

Range Input/Output

범위 값에 대한 입력은 다음 패턴 중 하나를 따라야 한다.

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

소괄호 또는 대괄호는 앞에서 설명한 대로 하한계 및 상한계가 제외 또는 포함인지를 나타낸다. 마지막 패턴은 empty이며, 이것은 비어 있는 범위를 나타낸다(아무런 점이 없는 범위).

lower-bound는 서브타입에 대한 유효 입력인 문자열이거나, 하한계가 없을 경우 비워둘 수 있다. 비슷하게, upper-bound는 서브타입에 대한 유효 입력인 문자열이거나, 상한계가 없을 경우 비워둘 수 있다.

각 경계 값은 “(큰따옴표) 문자를 사용하여 인용할 수 있다. 경계 값에 소괄호, 대괄호, 쉼표, 큰따옴표 또는 역슬래시가 포함된 경우, 이러한 문자가 범위 구문의 일부로 잘못 간주될 수도 있으므로 이것은 필수이다.
큰따옴표 또는 역슬래시를 인용된 경계 값에 넣으려면 역슬래시를 앞에 넣어야 한다. (또한, 큰따옴표로 인용된 경계 값 내의 큰따옴표 쌍은 SQL 리터럴 문자열의 작은따옴표에 대한 규칙과 비슷하게 큰따옴표 문자로 간주된다.)

또는, 범위 구문으로 잘못 처리될 수도 있는 모든 데이터 문자를 보호하기 위해 인용을 피하고 역슬래시 이스케이핑을 사용할 수 있다. 또한, 비어 있는 문자열인 경계 값을 작성할 경우, 아무것도 안 쓰면 무한 경계를 의미하게 되므로 ““를 작성한다.
공백은 범위 값 전후에 허용되지만, 소괄호 또는 대괄호 사이의 공백은 하한계 또는 상한계 값의 일부로 간주된다. (엘리먼트 타입에 따라 중요할 수도 있고 중요하지 않을 수도 있다.)

Examples:

-- 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

각 범위 타입은 범위 타입과 동일한 이름의 생성자 함수를 갖는다. 생성자 함수를 사용하는 것은 경계 값의 추가 인용을 할 필요가 없으므로 범위 리터럴 상수를 작성하는 것보다 좀 더 편리하다.
생성자 함수는 2 또는 3개의 인수를 수용한다. 3개 인수 양식은 세 번째 인수에서 지정된 양식의 경계로 범위를 생성하는 반면, 2개 인수 양식은 표준 양식의 범위를 생성한다(하한계 포함, 상한계 제외). 세 번째 인수는 문자열”()“,”(]“,”[)” 또는 “[]” 중 하나여야 한다.

Examples:

-- 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

불연속 범위는 엘리먼트 타입이 integer 또는 date 같이 잘 정의된(well-defined) “단계별” 타입이다. 이러한 타입에서, 2개의 엘리먼트 사이에 유효 값이 없을 경우 인접하다고 말할 수 있다. 연속 범위와는 대조적으로, 이것은 항상(또는 거의 대부분) 2개의 주어진 값 사이에서 다른 엘리먼트 값을 인식하는 것이 가능하다.
예를 들면, timestamp를 벗어난 범위와 마찬가지로 numeric 타입을 벗어난 범위는 연속이다. (timestamp는 정밀도에 제한이 있어서 이론적으로 불연속으로 처리될 수 있지만 단계의 크기가 관심사가 아닐 때는 연속으로 간주하는 것이 낫다.)

불연속 범위 타입에 대해 생각하는 또 다른 방법은 각 엘리먼트 값에 대해 “다음” 또는 “이전” 값에 대한 명확한 방안이 있는 것이다. 주어진 원래의 것 대신, 다음 또는 이전 엘리먼트 값을 선택함으로써 범위의 경계를 포함하는 표현과 제외하는 표현 사이의 변환이 가능하다는 점을 알고 있어야 한다.
예를 들면, 정수 범위 타입 [4,8] 및 (3,9)는 동일한 값 집합을 나타낸다. 그러나 수치상 범위일 경우에는 그렇지 않다.

불연속 범위 타입은 엘리먼트 타입에 대해 원하는 단계 크기를 인식하는 정규화(canonicalization) 함수를 가져야 한다.
정규화(canonicalization) 함수는 특히 일관된 포함 또는 제외 범위의 항등 표현을 갖는 범위 타입으로 값을 동등하게 변환할 책임이 있다. 정규화(canonicalization) 함수가 지정되지 않으면 사실상 동일한 값 집합을 나타내더라도 형식이 서로 다른 범위는 항상 비등가로 처리된다.

기본 제공 범위 타입 int4range, int8range 및 daterange는 모두 하한계는 포함하고 상한계는 제외하는, 즉 [)인 정규형(canonical) 양식을 사용한다. 그러나, 사용자가 정의한 범위 타입은 다른 표기법을 사용할 수 있다.

Defining New Range Types

사용자는 자신만의 범위 타입을 정의할 수 있다. 이렇게 하는 가장 일반 이유는 기본 제공 범위 타입으로 제공되지 않는 서브타입의 범위를 사용하기 위해서이다. 서브타입 float8의 새 범위 타입을 정의하는 예이다.

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

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

float8은 의미가 있는 “단계”가 아니므로 우리는 이 예제에서 정규화(canonicalization) 함수를 정의하지 않는다.

서브타입이 연속 값이 아닌 불연속 값을 갖는 경우 CREATE TYPE 명령은 canonical 함수를 지정해야 한다. 정규화(canonicalization) 함수는 입력 범위 값을 취하고, 경계와 형식이 다를 수 있는 등가의 범위 값을 리턴해야 한다. 정수 범위 [1, 7] 및 [1, 8)처럼, 동일한 값 집합을 나타내는 2개의 범위에 대한 정규형(canonical) 출력은 동일해야 한다.

형식이 서로 다른 2개의 등가 값이 항상 동일한 형식의 동일한 값으로 매핑되는 한, 어떤 표현을 정규형(canonical)으로 선택하든 상관 없다. 경계 포함/제외 형식을 조절하는 것 외에, 서브타입이 저장 가능한 크기보다 원하는 단계 크기가 클 경우에 정규화(canonicalization) 함수는 경계 값을 잘 처리할 수 있다.

예를 들면, timestamp를 벗어난 범위 타입은 단계 크기가 시간인 것으로 정의할 수 있다. 이때 정규화(canonicalization) 함수는 시간의 배수가 아닐 경우 반올림이 필요할 수 있거나 그 대신 에러가 발생할 수 있다.

자신만의 범위 타입을 정의하면, 주어진 범위에 어떤 값이 속하는지를 결정하는 정렬 순서를 변경하기 위해 서로 다른 서브 타입 B-tree 연산자 클래스 또는 사용할 콜레이션을 지정할 수 있다.

또한, GiST 또는 SP-GiST 인덱스에서 사용하기 위한 범위 타입은 서브타입 차이 또는 subtype_diff, 함수를 정의해야 한다. (인덱스는 subtype_diff 없이도 작동되지만 차이 함수가 제공된 경우보다 효율이 상당히 떨어진다.) 서브타입 차이 함수는 서브타입의 입력 값 2개를 취하고 float8 값으로 표현된 차이를 리턴한다(예: X 빼기 Y ).

위의 예제에서 일반 float8 빼기 연산자의 바탕이 되는 함수를 사용할 수 있지만 그 외 서브타입의 경우 일부 타입 변환이 필요할 수 있다. 차이를 숫자로 표현하는 방법에 대한 몇 가지 창의적 생각이 필요할 수도 있다. 가능한 한 가장 큰 범위까지 subtype_diff 함수는 선택된 연산자 클래스 및 콜레이션에서 암시하는 정렬 순서에 동의해야 한다.

즉, 이것의 결과는 정렬 순서에 따라 첫 번째 인수가 두 번째 인수보다 클 때에는 항상 양의 값이어야 한다.

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;

범위 타입 생성에 대한 자세한 내용은 User-defined Type을 참조 바란다.

Indexing

GiST 및 SP-GiST 인덱스는 범위 타입의 테이블 컬럼을 생성할 수 있다. GiST 인덱스를 생성하는 예이다.

CREATE INDEX reservation_idx ON reservation USING GIST (during);

GiST 또는 SP-GiST 인덱스는 이러한 범위 연산자 =, &&, <@, @>, <<, >>, -|-, &< 및 &>와 관련된 쿼리를 가속화할 수 있다.

또한, B-tree 및 해시 인덱스는 범위 타입의 테이블 컬럼을 생성할 수 있다. 이러한 인덱스 타입의 경우 기본적으로 유일하게 유용한 범위 연산은 같음이다. 해당 < 및 > 연산자를 사용하고 범위 값에 대해 정의되는 B-tree 정렬 순서가 있지만, 순서가 제멋대로라서 실세계에서는 별로 유용하지 않다. 범위 타입의 B-tree 및 해시 지원은 실제 인덱스를 생성하기 보다는 주로 쿼리 내부적으로 정렬 및 해싱을 허용하기 위한 것이다.

Constraints on Ranges

UNIQUE는 스칼라 값에 대해 자연스러운 제약 조건이지만, 범위 타입에는 일반적으로 적절하지 않다. 대신, 주로 제외 제약 조건이 좀 더 적절하다. 제외 제약 조건은 범위 타입에서 “비 오버랩” 같은 제약 조건의 사양을 허용한다.

Examples:

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 확장을 사용하여 일반 스칼라 데이터 타입에서 제외 제약 조건을 정의할 수 있는데, 그럴 경우 최대한의 유연성으로 범위 제외와 결합이 가능하다.
예를 들어, btree_gist가 설치된 후 다음 제약 조건은 회의실 수가 동일한 경우에만 오버랩되는 범위를 거부한다.

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