Vertices

Vertices are the most basic elements in the graph data model. They represent entities in the real world and have properties.

A graph has vertices and edges as the base units. In AgensGraph, both vertices and edges may contain Properties. While entities are usually represented by vertices, they may be indicated using edges in some cases. Unlike edges and properties, vertices may have zero or multiple label values.

The simplest form of a graph consists of a single vertex. A vertex can have zero or more properties.

The next step is to construct a graph with multiple vertices. Add two or more vertices to the graph of the previous step and add one or more properties to the existing vertex.

Edges

Edges connect vertices. When two vertices are connected via en edge and each vertex plays as start vertex or end vertex depending on the direction of the edge. Like vertices, edges have properties.
The edges between vertices play an important role in the graph database, especially when you search for linked data.
With edges, you may make vertices into a variety of data structures, such as lists, trees, maps, and composite entities. By adding edges to the example we are building, we can represent more meaningful data.

In the example, ACTED_IN and DIRECTEDare used as edge types. The ACTED_IN property, Roles, stores the value of the array type.

The ACTED_IN edge has Tom Hanks vertex as start vertex and Forrest Gump vertex as end vertex. In other words, we can say that the Tom Hanks vertex has an outgoing edge and the Forrest Gump vertex has an incoming edge.

If there is an edge in a single direction, you do not have to duplicate the edge and add it in the opposite direction; this is related to the graph traversal or performance.

Edges are always directional, but they may ignore directionality if it is not needed in your application. The diagram below shows a vertex having an edge pointing to itself.
All edges are recommended to have an edge type to perform the graph traversal in a more efficient manner.

Properties

Both vertices and edges may have properties. Properties are attribute values, and each attribute name should be defined only as a string type.

The available data types for property values are:

• Numeric type
  
• String type
  
• Boolean type
  
• List type (a collection of various data types)

NULL values cannot be used as property values. If NULL is entered, the property itself is assumed to be absent. NULL values, however, can be used in List.

Labels

You may define the roles or types of vertices or edges using labels. Vertices or edges with similar characteristics can be grouped and the name of such a group can be defined, which is called a “label.” That is, all vertices or edges with similar labels belong to the same group.

Database query statements can be performed only on the group (not the entire graph) using labels, which is helpful for more efficient querying.

Using labels for vertices is optional, and each vertex may have zero or only one label.

Labels can also be used to define constraints on properties or to add indexes.

You may also assign a label similar to a vertex to an edge. Unlike vertices, there is no edge without a label; all edges should have at least one label.

Let us add Person and Movie labels to the existing example graph.

Traversal

Traversal is to traverse paths while exploring a graph to answer the requested query. Traversal is a process of searching for the relevant vertices from the start vertex to find the answer to the requested query. In other words, it refers to following the vertices that are traversing the graph and the derived edges according to a specific rule.

In the examples illustrated so far, we try to find a movie featured by Tom Hanks. Starting with the Tom Hanks vertex, you can traverse all the processes that end at the vertex of Forrest Gump along the ACTED_IN edge associated with it.

By using the traversal of cypher query statements and additional techniques in the graph database, you may derive better result data. For more information, see Cypher Query Language.

Paths

Paths are the result data of a query statement or traversal, which shows one or more vertices and the edges connected to them.

The path (traversal result data) from the previous example is as follows:

The length of the above path is 1. The shortest path length is 0, which is the case when a single vertex does not have edges.

If the vertex has an edge pointing to itself, the length of the path is 1.

Get the pre-compiled binary

AgensGraph works on Linux/Windows and can be installed in two ways. One is to download its binary package, and the other is to download its source code and compile the package. The binary package can be downloaded from Bitnines’s website, and the source code can be downloaded from Github. If you want to know your system environment, enter the following command in the command window.

uname -sm

Extract the package

Unzip the downloaded file in the desired location. (e.g.: /usr/local/AgensGraph/)

tar xvf /path/to/your/use

Setting environment variables (Optional)

Add the following three lines to your shell startup file (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

Creating a database cluster

Create a database cluster using the following command: If you do not specify the -D option, use the AGDATA specified in .bash_profile.

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

Starting the server

You can start AgensGraph with the following command.

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

Creating a database

createdb [dbname]

If you do not specify the database name and user name, the default value will apply. The default is the name of the current user.

Running a terminal

agens [dbname]

When you run a terminal like above, you can see the following screen:

username=#

If it is a superuser, “= #” will be displayed at the prompt; “=>” will be displayed for other users.

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

In case you are in the middle of entering a query, - is displayed. If you are typing content to be included in parentheses, ( is displayed. Even multi-parentheses can be expressed as needed. You may customize the input prompt, and the detailed options can be found in the following link.

Creating Graphs

AgensGraph may store multiple graphs in a single database. Cypher cannot figure out multiple graphs. AgensGraph supports variables for generating and managing graphs using DDL and Cypher. The following syntax generates a graph called the network and sets the current graph.

CREATE GRAPH network;
SET graph_path = network;

In this example, the graph_path variable is set to network. However, if graph_path is not set before creating a graph, it will be set automatically after a graph is generated.

Creating Users

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

You can create new users to manage ownership and other privileges on database objects. If a new user wants to generate a label in the graph, the new user must be in the same group as the user who created the graph. See this link for more options regarding creating users.

Creating Labels

You should generate a label before generating graph data in principle. However, for your convenience, a label will be automatically created if it is specified when Cypher’s CREATE is executed. In AgensGraph, you should have one label for a vertex and an edge. The following example creates a vertex label named person and an edge label knows.

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

Creating Vertices and Edges

This section uses CREATE of Cypher to create the person vertex and knows edge. The CREATE clause creates a pattern consisting of vertices and edges. (variable:label {property: value, ...}) is a vertex type, and  
-[variable:label {property: value, ...}]- is an edge type. The direction of the edge can be represented by < or >. Variables may or may not exist in the forms of vertices and edges.

Note : AgensGraph does not support -- edge patterns. -- means a comment at the end of a sentence.

In the following example, patterns such as “Tom knows Summer,” “Pat knows Nikki” and “Olive knows Todd” are created.

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 uses the jsonb type for vertex/edge attributes. Attributes are expressed using JSON objects.

Creating Graph

AgensGraph is capable of storing multiple graphs within a single database. Thus, you should first create/select graphs to use to enable graph query using Cypher.

• Create Graph

  CREATE GRAPH graphName;
  SET graph_path = graphName;

  CREATE GRAPH is a command to create a graph. The command is used along with the name of the graph to be generated (Spaces are allowed in graphName from v1.3).
  graph_path is a variable to handle the current graph. Set the name of the graph you want to handle using SET.

• Drop Graph

  DROP GRAPH graphname CASCADE;       

  DROP GRAPH is a command to delete a graph from a graph database. To be noticed, a graph automatically contains vertex and edge labels when it is created, so to delete a graph you first must delete labels that are bound to that graph. To delete a graph and its’ labels altogether, use DROP GRAPH graphname CASCADE.

• Create Labels

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

  CREATE VLABEL creates vlabel, and CREATE ELABEL creates elabel. Each command is used along with the name of vlabel or elabel to generate.
  inherits() is a command that inherits another label. When you create a label, you can inherit other labels by specifying the names of the parent labels along with the corresponding keyword after the child label name. There can be multiple parent labels for inheritance.

• Drop Labels

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

  DROP VLABEL deletes vlabel and DROP ELABEL deletes elabel. Each command should be used with the names of vlabel and elabel you wish to erase. A vlabel that is inherited by other vlabels cannot be deleted directly, so you must use CASCADE command to delete all dependant data.

• Create vertices and edges

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

  The CREATE clause is used to create vertices and edges. When using this command, make sure to write vertices or edges to generate correctly using the pattern. (The pattern that represents various clauses and vertices/edges along with the CREATE clause will be described in detail later on).

Querying Graph

Querying a graph is the process of getting the information you need from the graph(s) by describing some specific graph pattern and finding it in that graph.

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

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

The MATCH clause finds graph data that matches the pattern in the clause. In the RETURN clause, specify only the elements that you want to return from the graph you find.

Manipulating Graph

In order to modify the existing graph data, the pattern of the MATCH clause should be displayed; after finding the matched graph, modifications can be made.

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

You can use the SET clause to set property values of vertices and edges.

Vertex

Vertex is expressed using parentheses and can be specified with vlabel, property, and variable to further refine the vertex to be searched.

( )

Vertex is represented by ( ). A pattern that does not have vlabel or property in parentheses, as in the example above, means all vertices.

(:person)

To represent vlabel in a vertex, use (:vlabelName). The name of vlabel is indicated with a colon in parentheses indicating the vertex. The above example represents a vertex with vlabel named “person.”

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

If you want to assign a variable to the vertex, use (variableName). A variable can be named a combination of alphanumeric (a~z, 0~9) and underbars (note that it should not start with a number). When you want to display the variable and vlabel at the same time in vertex, use (variableName: vlabelName).

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

You can further refine the properties by listing them in the vertex. If you want to represent a property, use  
({propertyName:propertyValue}). If the value is a string, the value must be wrapped with ‘ ’. If you want to represent multiple properties, , should be used.

Edge

An edge is expressed with two dashes and can be marked along with elabel, property, and variable.

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

An edge is expressed using -[]-. You can express direction with < >. In the above example, -[ ]- expressed as dashes only means “all edges” since no constraint is indicated. -[ ]-> <-[ ]- with additional angle brackets means “all edges with one directionality.”

-[:knows]->

If you want to express other elements in the edge, specify them in [ ]. If you want to represent elabel on the edge, use -[:elabelName]->. The above example means an edge with elabel named “knows.”

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

If you want to assign a variable to an edge, use -[variableName]->. When you want to display variable and elabel simultaneously on the edge, use -[variableName:elabelName]->.

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

If you want to represent a property on the edge use -[{propertyName:propertyValue}]->. If the value is a string, use ‘ ’ . If you want to represent multiple properties, ,should be used.

Vertices and Edges

Patterns can be used to express vertices, edges, or a combination of them.

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

The basic frame of a pattern consisting of a combination of vertices and edges is ( )-[]->( ). It is good to give meaning to the pattern by specifying properties and labels on vertices and edges. Variables can be assigned to individual vertices and edges, or the entire pattern. If you want to assign a variable to the entire pattern, use =. In the above example, p is a variable and is applied to the entire pattern by using =.

Path

The number of vertices and edges in a pattern may continue to increase. The unit for a series of path in a pattern is called a path.

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

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

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

In the case of a long path, it can be written in abbreviated form for better readability and ease of writing. If n vertices go through an edge n-1 times, you can put an asterisk (*) and n-1 in brackets (see example above).

Flexible Length

Depending on the situation, you may need to dynamically change the number of edges that must be gone through in a query.

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

If you want to give a dynamic change to the length of the path, .. can be used. The example above shows a path with two or more edges, a path with seven or fewer paths, a path with three or more and five or fewer paths, and an infinite path (in sequence from top to bottom).

MATCH

The MATCH clause is a clause that describes the graph pattern you want to find in the database. This is the most basic way to import data. For more information on the Pattern specification, see the Pattern section above.

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

  The graph pattern to be searched is described in the MATCH clause and then returned using the RETURN clause.

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

  The above three queries output the names of common acquaintances of Jack and Emily. They all output the same result, but the number of MATCH clauses and the pattern notations in the MATCH clause is different. The first query used the MATCH clause twice, while the second query expressed two patterns using a comma in one MATCH clause; the third query used one pattern in one MATCH clause. Likewise, you can use the MATCH clause in a variety of ways to derive the same result.

  When you generate, query, delete, add, or modify graph data, you will need to find a graph that matches a certain pattern first in most cases. Thus, the MATCH clause is a very basic and important clause.
   
  

  [Reference]

  When a large volume of data across multiple pages are printed in Agens, you may see help on scrolling by using the ? keyword.

  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
  Finding a single shortest path between two vertices using shortestpath function.

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

  Find single shortest path between two vertices, Alice and Jack, between which 15 edges exist. Feed a graph path consists of the start vertex, the end vertex, and the edges between them (single link path) into the function as its argument. Edge type, max hops and directions are all used in finding the shortest path.

• All Shortest Paths
  Find all the shortest paths between two vertices.

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

  Find all the shortest paths between two vertices. Alice and Jack.

OPTIONAL MATCH

The OPTIONAL MATCH clause, like the MATCH clause, is a clause describing the graph pattern to be searched. The OPTIONAL MATCH clause differs from the MATCH clause in that it returns NULL if there are no results to return.

• Optional Matching

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

  The use of the OPTIONAL MATCH clause is the same as the MATCH clause. Put the pattern you want to find in the OPTIONAL MATCH clause and return the result through the RETURN clause. The returned result may contain NULL.

MATCH ONLY

The Only keyword allows you to use the MATCH query to return results that exclude child labels.

• Match only

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

RETURN

The RETURN clause is a clause that specifies the result of a cypher query. It returns data that matches the graph pattern you are looking for and can return vertices, edges, properties, and so on.

• Vertex Return

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

  If you want to return a vertex, specify the vertex you want to find in the MATCH clause and assign a variable to it. You can then return a vertex by specifying the corresponding variable in the RETURN clause.

• Edge Return

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

  If you want to return an edge, specify the edge you want to find in the MATCH clause and assign a variable to it. You can then return an edge by specifying the corresponding variable in the RETURN clause.

• Property Return

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

  If you want to return a property of a vertex or edge, specify the vertex or edge you want to find in the MATCH clause and assign a variable to it. In the RETURN clause, you can return the property by specifying . with the variable and property.

• All Return

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

  If you want to return all the elements in the MATCH clause, specify * in the RETURN clause. The elements returned are the elements to which variables are assigned. Elements with no variable assigned will not be returned.

• Path Return

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

  If you want to return a path that matches the pattern shown in the MATCH clause, you should apply a variable to the entire pattern. If the pattern is preceded by a variable and =, the variable can be applied to the entire pattern. You can then return the path by specifying the variable in the RETURN clause.

• Alias Return

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

  You can output an alias to the column of the returned result. The returned element is followed by alias with the AS keyword. If you list the alias without double quotation marks, the output will be in lower case; with double quotation marks, the output will be printed as it is.

• 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);

  It provides a function to obtain only id and properties for vertices and edges, and a function to get the length and each element separately of the path. You may also use the aggregation function supported by PostgreSQL.

• Constant Return

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

  Constant values and their operators can be expressed. All of the expressions useable in the SQL SELECT statement can be used with the RETURN clause, except for table and column expressions. For more information, see PostgreSQL Expression.

• Unique Return

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

  Removes the duplicate records of the returned result and outputs only the unique result. You can add DISTINCT before the returned element.

WITH

The WITH clause is a clause that connects multiple cypher queries. It passes the result of the preceding WITH clause query to the following WITH clause query. That is, the WITH clause can be regarded as a RETURN clause that passes the value of the preceding query to the input of the following query.

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

  The WITH clause is described as an example of the above three queries. The first query returns the number of acquaintances that Jack and ‘someone’ know in common with the ‘someone’. If we think the second query is in line with the first query, we can figure out that it is a query that returns ‘someone’ when the number of common acquaintances is greater than one (1). The second query cannot be executed alone; it is only meaningful when combined with the first query. The WITH clause plays the role of connecting the two queries.

  The third query is a query that includes a WITH clause that links the first and second queries. It holds the returned results of the first query in variable and alias forms, and then passes the results to the second query. In the case where you want to connect multiple queries, as in this case, and the output of the preceding query is used as the input of the trailing query, you can concatenate them with the WITH clause. The WITH clause must be held in variable or alias form.

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

  It is important to partition the entire query when using the WITH clause. Think of the WITH clause as a RETURN clause, and consider ORDER BY, LIMIT as a partition after RETURN. Therefore, if there is an ORDER BY, LIMIT clause in the preceding query, the clause must be written after the WITH clause, and then the following query is created.

WHERE

The WHERE clause is a clause that adds constraints to the MATCH or OPTIONAL MATCH clause or filters the results of the WITH clause. The WHERE clause cannot be used alone; it is dependent on the MATCH, OPTIONAL MATCH, START, and WITH clauses when used.

ORDER BY

The ORDER BY clause is a clause that sorts result values. It is used with the RETURN clause or WITH clause and determines whether to sort in ascending or descending order by column.

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

  The ORDER BY clause basically sorts by properties of vertices and edges. You may specify the aliases of the properties that will be the basis of sorting in the ORDER BY clause. That is, in the RETURN clause, an alias is given to a property to be sorted, and then the alias is specified in the ORDER BY clause. If multiple sorting criteria are specified in the ORDER BY clause, sorting is done first based on the first criterion and then the second sorting is performed only for duplicated values after the first sorting.
  However, if a property is not specified in the RETURN clause, you can directly specify the property other than the corresponding alias in the ORDER BY clause.

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

  The ORDER BY clause basically sorts in ascending order. If you want to sort in descending order, you can write the DESC keyword. If multiple criteria are specified in the ORDER BY clause, DESC must be followed by the elements to be sorted in descending order.

SKIP

The SKIP clause is a clause that changes the search start location.

• Skip

  MATCH (a)
  RETURN a.name
  SKIP 3;

  When searching for a pattern in the MATCH clause, skip the number of patterns specified in the SKIP clause and start the search.

LIMIT

The LIMIT clause is a clause that limits the number of results in a result set.

• Limit Result Set

  MATCH (a)
  RETURN a.name
  LIMIT 10;

  If you want to limit the number of results you want to output, you can specify the number in the LIMIT clause. If the number of results in the result set is smaller than or equal to the number specified in the LIMIT clause, all results will be output; if bigger, only the number of results specified in the LIMIT clause will be output.

CREATE

The CREATE clause is a clause that creates graph elements such as vertices and edges.

• Create Vertex

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

  When you want to create a vertex, you need to write a vertex-related pattern in the CREATE clause. When creating a vertex, you can create it by referring to the vertex specified earlier as in the last example.

• 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);

  An edge is a link between two vertices. Therefore, we need to create an edge after finding two vertices through the MATCH clause. Note that two vertices do not necessarily mean different vertices. Like the third query above, self-edge is also possible.

• 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);

  You may create each element separately, but you may also create a pattern you want at a time by listing the path in the CREATE clause. Be careful when creating a path at once. If it is not used with the MATCH clause like the first query, it creates new data even if there is the same data as the corresponding pattern (i.e. duplicate data). If you use it with a MATCH clause like the second query, it finds the Edward vertex and then creates the likes edge on the vertex and Alice vertex. If you want to create only the likes edges in existing Edward and Alice vertices, you can use it as the third query.

DELETE

The DELETE clause is a clause that removes vertices or edges.

• Delete vertex

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

  Find the vertices you want to remove through the MATCH clause and remove them by marking corresponding variables in the DELETE clause. However, if the vertices you want to remove are connected to other vertices and edges, remove the edges first before removing the vertices.

• Delete edge

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

  Find the edges you want to remove with the MATCH clause and remove them by marking corresponding variables in the DELETE clause.

DETACH DELETE

• Delete a vertex with all its relationships

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

  If the DETACH keyword is used together, the edges associated with the vertices are also removed. If the vertices you want to remove are linked to other vertices and edges, you may skip the edge-removing process.

SET

The SET clause is a clause that adds, sets, or removes properties, or adds vlabels.

• Add Property

  MATCH (E:person {name: 'Edward'})
  SET E.habit = 'play the guitar';

  Find the vertices or edges to which you want to add properties through the MATCH clause, and specify the names and values of the properties to add to the SET clause. When describing a property, mark it with single or double quotation marks.

• Modify Property

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

  Find the vertices or edges whose properties you want to change through the MATCH clause, and specify the names of the properties to be changed in the SET clause. When describing a property, mark it with single or double quotation marks.

• Remove Property

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

  Find the vertices or edges from which you want to remove the properties through the MATCH clause, and mark the names of the properties to be removed and NULL in the SET clause.

• 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 can be used to copy all properties from one vertex/edge to another vertex/edge. All properties of “pn” are copied to the properties of “at”, and all existing properties of “at” are removed.

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

  Use the = operator to replace the existing properties of the vertex/edge with the properties provided by the map.

• Remove all properties using an empty map and =

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

  Use the = operator to remove all existing properties of the vertex/edge.

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

  Use the += operator to change existing properties of the vertex/edge or add new ones. The properties of the vertex and edge are not removed if the map is empty as shown below.

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

  Sets multiple properties at once by separating them with commas (,).

REMOVE

The REMOVE clause is a clause that removes properties.

• Remove property

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

  How to remove a property using the SET clause has already been described in SET. You can also remove a property using the REMOVE clause.
  Locate the element from which you want to remove the property through the MATCH clause, and name the property to be removed in the REMOVE clause.

MERGE

The MERGE clause is a clause that i) adds a corresponding pattern (like the CREATE clause) if the specified pattern does not exist in the graph, and ii) verifies the existence of the pattern (like the Match clause) if it already exists in the graph. The MERGE clause recognizes the entire pattern specified in the clause.

• Merge

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

  If you specify a pattern in the MERGE clause and execute it, you can see whether the pattern exists or not in the graph. If it is already in the graph, it functions like the MATCH clause; if it is not in the graph, it newly creates the pattern like the CREATE clause.

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

  The MERGE clause recognizes the entire pattern. Even if the specified pattern partially exists in the graph, it does not create only the rest of it that does not exist. We will explain this with the above query as an example. The first query does not create an edge if the Edward and Alice vertices already exist in the graph and are not linked to the likes edge. New Edward and Alice vertices are created and an edge is created between the two vertices.
  If you are not sure whether some elements of the pattern already exist in the graph, you had better divide each element as in the second query and use the MERGE clause.

• 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 and ON MATCH SET clauses can be used to set properties depending on how the MERGE clause behaves. If the MERGE clause behaves like a CREATE clause, the ON CREATE SET clause will be performed; if the MERGE clause behaves like a MATCH clause, the ON MATCH SET clause will be performed.

UNION and UNION ALL

The UNION clause combines the results of several queries into one.

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

  If you put the UNION keyword between the two queries, you can combine the two results together. In the case of duplicate values, UNION outputs it only once. When you use both the UNION and ALL keywords, the duplicate values (when the two results are combined) will be output as they are.

LOAD FROM

You can load data by table using the LOAD FROM clause.

• 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

Provides the ability to control data by setting constraints on properties.

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

  If the constraint name is omitted, it is generated automatically.
  UNIQUE restricts the value of a field to be unique in that label.
  check_expr returns a true or false value of the properties that are newly entered or modified. If the result is false, the corresponding input/change will fail.
  You may use \dGv, \dGe commands to check the constraints when querying information on the vertices and edges.

  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()
  Returns the natural logarithm of a number.

  RETURN log(27);

  The natural logarithm of 27 is returned.

• log10()
  Returns the common logarithm (base 10) of a number.

  RETURN log10(27);

  The common logarithm of 27 is returned.

• exp()
  The exp function returns a power value to base (e) exponentiated by the numeric value passed as an argument. That is, exp(1) returns e^1≒2.71828182845905, exp(2) returns e^2≒7.38905609893065, and exp(-1), e^-1≒0.367879441171442.

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

• sqrt()
  Returns the square root of the numeric value passed as an argument. The sqrt function cannot pass a negative number as an argument.

  RETURN sqrt(25);

Trigonometric

• sin()/cos()/tan()
  The sin, cos, and tan functions return the sine, cosine, and tangent values of the numeric values passed as arguments, respectively. sin(), cos(), and tan() print the values in radians; sind(), cosd(), and tand() are used to print the values in degrees.

  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()
  The cot function returns a cotangent value (inverse of tangent) of the numeric value passed as an argument, the asin function returns an arcsine value (inverse of sine) of the numeric value passed as the argument, and the acos function returns an arccosine value of the numeric value (inverse of cosine). The atan, atan2 functions return the arctangent value (inverse of tangent) of the numeric value passed as an argument. The argument range of the acos function is a numeric value between -1 and 1. atan2 has two arguments in order to make the atan function more granular. Conceptually, atan2(a, b) is equivalent to atan (a/b). However, it is not clear whether atan(-5) is atan(-15/3) or atan (15/(-3)). The trigonometric function requires a definite distinction because the argument is a radian value. Therefore, using atan2 rather than atan makes it more accurate.

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

• pi()/degrees()/radians()
  The pi function returns pi as a number. The degrees function takes the arguments passed as radians and returns them to degrees. The radians function converts the arguments passed to degrees into radians.

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

Integer Types

smallint, integer, and bigint types store a wide range of integers without decimal fractions. If you try to store a value beyond the allowable range, an error will occur.

• integer type: This type is generally chosen as it provides the best balance point of range, storage size, and performance.
  
• smallint type: Typically used only when there is insufficient disk space.
• bigint type: To be used when the integer type range is insufficient.

SQL specifies only integer (or int), smallint, and bigint. (available as int2, int4, and int8 as well).

Arbitrary Precision Numbers

Numeric types may store a very large number of digits and perform calculations correctly. It is especially recommended when storing amounts and quantities that should be accurate. The arithmetic of numeric values, however, is much slower than the integer types or floating-point types described in the next section.

Scale in the numeric type means the number of digits to the right of the decimal point. Precision means the total number of significant digits of the total number. That is the total number of digits on both sides of the decimal point. Thus, the precision and scale of the number 23.5141 are 6 and 4, respectively. The scale of the whole number can be considered 0 (Scale 0).

You may configure both the maximum precision and the maximum scale of a numeric column.

To declare a column of numeric type, use the following syntax:

NUMERIC(precision, scale)

Precision must be positive and scale must be zero or positive.

NUMERIC(precision)

If you specify numeric without precision or scale as follows, it selects Scale 0.

NUMERIC

If you create a numeric column that can store precision and scale values without specifying them, you may store the precision value. This kind of column can be used without restriction if it does not specify a specific scale. However, a numeric column with a scale value specified will be limited by the scale value. (When transferring data, make sure to specify the precision and scale always).

Note: The maximum allowable precision is 1000 if explicitly specified in the type declaration. NUMERICs that do not specify precision are limited to the ranges described in the table.

In the case where the scale in the value to be stored is greater than the scale declared in the column, the system rounds off the value to the specified scale; after rounding-off, if the number of digits to the right of the decimal point exceeds “the declared scale subtracted from the declared precision,” an error will occur. Numeric values are physically stored without extra 0 values. Therefore, the precision and scale of the declared column are the maximum (not a fixed allocation). In this sense, numeric types are closer to varchar (n) than to char (n). The actual storage requirement is 2 bytes for each 4-digit group plus 3 for 8-byte overhead.

The decimal and numeric types are the same and both are SQL standards.

Floating-Point Types

The real and double precision data types are inaccurate variable-precision numeric types. Inaccuracy means that some values cannot be accurately converted to their internal form and are stored as approximate values, making the storage and retrieval of values somewhat inconsistent.

• If you need accurate storage and computation (e.g. amount), use the numeric type instead.
  
• If you need to perform complex calculations using this type for some unavoidable reason (e.g. when you need a specific behavior in boundary cases (infinity, underflow)), you should be careful with the implementation.
  
• Comparing two floating-point values to see if they are equal may not always work as expected.

On most platforms, the real type has a minimum range of 1E-37 to 1E+37, with a precision of at least 6. The double-precision type generally has a precision of at least 15, ranging from 1E-307 to 1E+308. Too large or too small values will generate an error. If the precision of the entered number is too large, it may be rounded up. An underflow error occurs if the number is so close to zero that it cannot be marked as non-zero.

Serial Types

The smallserial, serial, and bigserial data types are not actual types, but are international notations for creating unique identifier columns (similar to the AUTO_INCREMENT attribute supported by some other databases).

The following two statements work in the same manner:

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

Create an integer column and sort it by default assigned in the sequence. A NOT NULL constraint is applied to prevent null values from being inserted. (In most cases, it is possible to prevent duplicate values from being accidentally entered with a UNIQUE or PRIMARY KEY constraint; such a constraint is not automatically generated.)

Finally, the sequence is marked as column “owned by,” so that it is not deleted unless the column or table is deleted.

The serial column default is specified in order to insert the next value of the sequence into the serial column. This can be done by excluding columns from the column list of the INSERT statement or by using the DEFAULT keyword.

• The serial type is the same as serial4; both generate an integer column.
  
• The bigserial and serial8 types work the same except when creating a bigint column. bigserial should be used if you expect to use more than 231 identifiers throughout the lifetime of the table.
  
• The smallserial and serial2 types operate the same except when creating a smallint column.

The sequence created for the serial column is automatically deleted when the owning column is deleted. You can delete the sequence without deleting the column, but the column base expression is forcibly deleted.

Date/Time Input

The date and time input can be specified in the order of the day, month, and year as follows:

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

Set the DateStyle parameter to MDY to select the month-day-year interpretation, DMY to select the day-month-year interpretation, or YMD to select the year-month-day interpretation.

The date or time input must be preceded and followed by single quotation marks, like a text string.

• Date
  Available date types:

• Time
  The visual type is time [(p)] without the time zone and time [(p)] with the time zone. time alone is the same as time without time zone. A valid entry of this type consists of time, followed by an optional time zone. (See the table below.) If the time zone is specified as an input for time without time zone, it is ignored. You can specify the date, but ignore it except when using the time zone name associated with daylight saving time, such as America/New_York. In this case, a date specification is required to determine whether the standard time or daylight saving time period is applied. The appropriate time zone offset is recorded in the time with the time zone value.

Table - [Enter Time]

Table - [En ter Time Slot]

See the Time Zones section below for how to specify the time zone.

• Timestamp
  The valid input of timestamp type consists of a connection of date and time followed by an optional time zone and optional AD or BC. (AD/BC may appear before the time zone, which is not the preferred order.)

  1999-01-08 04:05:06
  1999-01-08 04:05:06 -8:00

  These are valid values in accordance with the ISO 8601 standard. The following command format is also supported:

  January 8 04:05:06 1999 PST

  The SQL standard distinguishes timestamp without time zone and timestamp with time zone literals by the presence of a “+” or “-” sign and the time zone offset after that time.

   -- timestamp without time zone
   TIMESTAMP '2004-10-19 10:23:54'
  
   -- timestamp with time zone
   TIMESTAMP '2004-10-19 10:23:54+02'

  If you do not specify timestamp with time zone, it is treated as timestamp without time zone; thus, if you use timestamp with time zone, you must specify an explicit type.

  TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

• Special values
  Some special date/time input values are supported for convenience as shown in the table. The values infinity and -infinity are simple abbreviations that are specially represented within the system, without modification, but otherwise converted to the default date/time value when read. (Note the now and related strings are converted to specific time values at the moment they are read.) If you use all of these values as constants in your SQL command, you must use single quotation marks around the constants.

You can also use the following SQL-compatible functions to get the current time values of the data types such as CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP.

Date/Time Output

The output format of the date/time type can be set to one of four styles: ISO 8601, SQL (Ingres), typical POSTGRES (Unix date format), or German. The default is ISO format.

The following table shows an example of each output style. The output of date/time type has usually only the date or time depending on the example given.

If the DMY field order is specified, it is output in the order of month, day. In other cases, it is output in the order of day and month. The following table is an example:

The date/time style can be selected by the user using SET datestyle command, DateStyle parameter in the postgresql.conf configuration file, or PGDATESTYLE environment variables of the server or client. The formatting function to_char allows you to specify date/time output formats in a more flexible manner.

Time Zones

Time zones and notations are affected by political decisions in addition to topographical features. Worldwide time zones have been standardized in the 1900s but are constantly changing due to daylight time regulations. AgensGraph uses the IANA (Olson) timezone database. For future times, it considers that the latest known rules for a specified time zone will be complied with indefinitely in the distant future. However, it has a peculiar mix of dates, time types, and features.

Two obvious problems are:

• Date type does not have an associated time zone (only time type has). In the real world, the time zone has no meaning unless it is associated with date and time, since the offset can vary over the year containing the daylight saving time boundary.

• The default time zone is specified as a constant numeric offset from UTC. Thus, it may not be possible to adapt to daylight saving time when performing date/time calculations across DST boundaries.

When using time zone to solve this problem, we recommend using a date/time type that includes both date and time (this is supported for compatibility but we do not recommend using the time with time zone type). Assume a local time zone for types that only contain dates or times. The date and time in the time zone are internally stored in UTC. Then, they are converted to a local time in the Time Zone specified by the TimeZone configuration parameters before being displayed to the client. Allow the time zone to be specified in three different formats.

• The full names of time zone (e.g. America/New_York). The recognized time zone names are listed in the   
  pg_timezone_names view. (Identical time zone names are recognized by many other software applications as well.)
  

• Time zone abbreviations (e.g. PST). In contrast to the full names of the time zone that can imply the daylight time conversion date rule set, the corresponding specification simply defines a specific offset from UTC. The recognized abbreviations are listed in the pg_timezone_abbrevs view. You cannot set the configuration parameters TimeZone or log_timezone as a time zone abbreviation, but may use an abbreviation and AT TIME ZONE operator as date/time input values.

• In addition to time zone names and abbreviations, it accepts POSIX style time zone specifications of STDoffset or STDoffsetDST; STD is a regional abbreviation, offsetUTC is the numerical offset for the time of the west, and DST is the optional summertime local abbreviation that is assumed to be one hour earlier than the specified offset. For instance, if EST5EDT is not yet a recognized local name, it is accepted and becomes functionally identical to the US East Coast time. In this syntax, local abbreviations can be any string of characters or any string using angle brackets (<>). If a daylight saving area abbreviation is present, it is assumed to be used in accordance with the same daylight saving time conversion rules as those used in the posixrules entry in the IANA time zone database. As posixrules are identical with US/Eastern in a standard AgensGraph installation, the POSIX style time zone specification complies with the USA Daylight Saving Time rules. You can adjust this behavior by replacing the posixrules file, if necessary.

Simply put, this is the difference between abbreviations and full names. Time zone abbreviations represent a specific offset from UTC, while many time zone full names imply a local daylight time rule and have two possible UTC offsets. For instance, “2014-06-04 12:00 America/New_York” representing the noon in the New York area was Eastern Daylight Time (UTC-4) for the day. Accordingly, “2014-06-04 12:00 EDT” specifies the same time instance. However, “2014-06-04 12:00 EST” specifies noon Eastern Standard Time (UTC-5) regardless of whether daylight saving time is nominally in effect on that date.

To complicate the problem, some areas use the same time zone abbreviations meaning different UTC offsets at different times. For example, MSK in Moscow meant UTC+3 for several years and UTC+4 for other cases. Even if these abbreviations are interpreted according to their meanings for a given date (or most recent meaning), as in the above EST example, this does not necessarily match the local time of the date. Note that the POSIX style time zone function does not check for the correctness of local abbreviations; this means you may silently accept false input. For example, SET TIMEZONE TO FOOBAR0 works by letting the system use specific abbreviations for UTC.

Another problem to keep in mind is that positive offset is used for Greenwich’s position west in the POSIX time zone region name. Elsewhere, AgensGraph conforms to the ISO-8601 notation, where the positive time-zone offset is east of Greenwich. Time zone names and abbreviations are case sensitive and are not embedded in the server; they are to be searched from the configuration files stored in the installation directory (…/share/timezone/and …/share/timezonesets/). TimeZone configuration parameters can be set in other standard ways, which is different from postgresql.conf as follows:

• The SQL command SET TIME ZONE sets a time zone for the session. You can use SET TIMEZONE TO, which is more compatible with the SQL specification.
  
• The PGTZ environment variable is used by the libpq client to send SET TIME ZONE command to the server when connected.

Interval Input

The interval value can be written using the following detailed syntax:

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

Where quantity is a number and unit can be microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, or their abbreviations, singular or plural; direction can be ago or empty. The at (@) sign is optional noise. Quantities in different units are implicitly added using an appropriate sign accounting. This syntax is also used for interval output when IntervalStyle is set to postgres_verbose.

Days, hours, minutes, and seconds can be specified without explicitly marking units. For example, “1 12:59:10” is read the same as “1 day 12 hours 59 min 10 sec.”

Combinations of years and months can be specified using dashes as well. For example, “200-10” is read the same as “200 years 10 months.” (This short format is actually the only one allowed by the SQL standards, and is used for output if IntervalStyle is set to sql_standard).

The format using specifiers:

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

The string must contain P, and may contain T to include the unit of time. Available unit abbreviations are listed in the table below. Units can be omitted and can be specified in any order, but units of less than one day must appear after T. In particular, the meaning of M varies depending on whether it is before or after T.
 

Table [ISO 8601 Interval Unit Abbreviations]

Alternative format:

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

The string must start with P, and T separates the date and time of the interval. The value is specified as a number similar to the ISO 8601 date. If you create an interval constant with the fields specification, or if you assign a string to an interval column defined by the fields specification, the interpretation of the unmarked quantity varies depending on the fields. For example, INTERVAL ‘1’ YEAR is interpreted as a year, whereas INTERVAL ‘1’ means 1 second. The lowest right field value allowed by the fields specification is ignored.

For example, INTERVAL ‘1 day 2:03:04’ HOUR TO MINUTE will eventually delete the “seconds” field (not the date field). As all fields of interval values must have the same signs according to the SQL standards, the leading negative signs apply to all fields. For example, the negative sign in the interval literal ‘-1 2:03:04’ applies to both date and hour/minute/second parts. As the fields are allowed to have different signs and the signs of each field are independently processed in a text representation, the hour/minute/second part is regarded as a positive value in this example. If IntervalStyle is set to sql_standard, the leading sign is assumed to be applied to all fields (if there is no additional sign).

If the field is negative, it is better to explicitly append the (negative) sign to avoid ambiguity. Internally, interval values are stored as months, days, and seconds. This is because the number of days in the month is different, and a day can be 23 or 25 hours depending on the implementation of daylight saving time. The month and day fields are integers, and the second field can be stored as decimals. Since the interval is usually generated by constant strings or timestamp subtraction, this way of storage is not problematic in most cases. The functions justify_days and justify_hours are useful when you want to control overflowing days and times in the normal range. Field values in some fields of the detailed input format and the simpler input field may have a decimal fraction (e.g. “1.5 weeks” or “01:02:03.45”). These inputs are converted to an appropriate number of months, days, and seconds for storage. This results in the decimal(s) of months or days being added to a subfield using the conversion factor Jan=30 days and 1 day=24 hour. For example, “1.5 months” is one month and 15 days. Only the seconds are displayed with a decimal fraction. The table below shows some examples of valid interval inputs.  

Table [Interval Input]

Interval Output

The output format of interval type can be set to one of the four styles sql_standard, postgres, postgres_verbose, or iso_8601 using the command SET intervalstyle. The default is postgres. The table below shows an example of each output style. The sql_standard style generates an output that conforms to the SQL standard specification for the interval literal string if the interval value satisfies the standard limit (year-month or day-minute only if positive and negative values are not mixed). Otherwise, a sign is explicitly added to eliminate the confusion of the sign mixing interval; the output appears as if the standard year-month literal string is followed by a day-time literal string.  

Table [Example of interval output style]

Points

Points are the basic two-dimensional building blocks for geometric types. The value of point type is specified using one of the following syntaxes:

( x , y )
  x , y

Where x and y are the floating-point coordinates, respectively. Points are output using the first syntax.

Lines

Lines are expressed by a linear equation Ax+By+C=0; where A and B are both nonzero. The value of line type is the input and output in the following format.

{ A, B, C }

Alternatively, you can use one of the following formats for input:

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

Where (x1, y1) and (x2, y2) are two different points on a straight line.

Line Segments

A line segment is represented by a pair of points that form the two endpoints of a line segment. The value of lseg type is specified using one of the following syntaxes.

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

Where (x1, y1) and (x2, y2) are the two endpoints of a line segment. The Line Segment is output using the first syntax.

Boxes

A box is represented by a pair of points that form the opposite corners of the box. The value of box type is specified using one of the following syntaxes:

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

Where (x1, y1) and (x2, y2) are the two opposite corners of the box. A box is an output using the second syntax. Two opposite corners are provided as inputs, but values are reordered as needed to be saved as upper right corner and lower left corner.

Paths

A path is represented by a list of connected points. If the first and last points of the list are considered unconnected, it is an open path. If the first and last points are considered to be connected, it is a closed path. The value of path type is specified using one of the following syntaxes:

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

Where the points are the two endpoints of a line segment constituting the path. Square brackets ([]) indicate open paths and parentheses (()) indicate closed paths. If the outermost parentheses are omitted, as in the third through fifth statements, they are considered closed paths. The path is output using the second syntax properly.

Polygons

Polygons are represented by a list of points (polygon vertices). Polygons are very similar to closed paths, but are stored differently and have their own set of routines supported. The value of polygon type is specified using one of the following syntaxes:

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

Where the points are the two endpoints of a line segment constituting the boundary of the polygon. The polygon is output using the first syntax.

Circles

A circle is represented by the center point and radius. The value of circle type is specified using one of the following syntaxes:

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

Where (x, y) is the center of the circle and r is the radius. The circle is output using the first syntax.

Creating XML Values

To generate an xml type value from character data, use the function below:

XMLPARSE ( { DOCUMENT | CONTENT } value)

Examples:

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

The XML type does not check the input value for the document type definition (DTD) even if the input value is specified as DTD. It does not support validation of other XML schema languages (e.g. XML schema).

The inverse operation to generate a string value in xml uses the following function:

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

type can be character, character varying, text (or alias in it). According to the SQL standard, this is the only way to convert XML and character types, but may simply cast the values.

Choosing DOCUMENT and CONTENT is determined by the “XML Option” session configuration parameters, which can be set using standard commands when a string value is cast to xml type or passes through XMLSARIALIZE without going through XMLPARSE or XMLSERIALIZE.

SET xmloption TO { DOCUMENT | CONTENT };

As the default is CONTENT, XML data in any format are allowed.

Note: You cannot directly convert a string containing DTD to an XML format, since the definition of an XML content fragment does not allow XML if the default XML option settings are used. You should use XMLPARSE or change the XML option.

Encoding Handling

Care should be taken when you process multiple character encodings on the client and when XML data is passed through it. If you use text mode to pass a query to the server and pass a query result to the client (normal mode), convert all character data passed between the client and server, and convert the character encoding backward at each end. It contains a text representation of the XML value as shown in the example above. This usually this implies that the encoding declaration contained in the XML data can be invalidated, as the embedded encoding declaration does not change, if the character data is converted to another encoding during transmission between the client and server. To cope with this behavior, the encoding declaration contained in the character string that exists for the XML type input is ignored and CONTENT is regarded as the current server encoding. As a result, strings of XML data must be transmitted from the client through the current client encoding for proper processing. It is the client’s responsibility to convert the document to the current client encoding or to properly adjust the client encoding before sending it to the server. The value of type XML in the output does not have an encoding declaration, and the client considers all data to be the current client encoding.

If you use binary mode to pass query parameters to the server and pass the query results back to the client, the character set conversion is not performed, making things more difficult. In this case, the encoding declaration of the XML data is complied with; if absent, the data is assumed to be UTF-8 (note that this is required by the XML standard and does not support UTF-16). At the output, the data has an encoding declaration specifying the client encoding, if it is not the client encoding is UTF-8; if it is, the encoding declaration will be omitted.

Processing XML data is less likely to cause errors and is more efficient when the XML data encoding, client encoding, and server encoding are all identical. Since XML data is internally processed as UTF-8, the calculation is most efficient when the server is UTF-8 as well.

Note: Some XML-related functions may not work with non-ASCII data if the server encoding is not UTF-8. This is especially known as a problem in xpath ().

Accessing XML Values

The XML data type is unique in that it does not provide a comparison operator. This is because there is no well-defined and universally useful comparison algorithm for XML data. As a result, you cannot retrieve a row by comparing the XML column with the search value. You must use XML with a separate key field (e.g. ID). An alternative solution to compare XML values is to convert to a string first. Note, however, that string comparisons have nothing to do with useful XML comparison methods.

Since there is no comparison operator for XML data types, it is not possible to create an index directly on this type of column. If you need to retrieve XML data fast, the possible solutions include casting and indexing expressions to character string types or indexing XPath expressions. Of course, the search should be adjusted by the indexed expressions in actual querying.

The text search feature can also be used to speed up the retrieval of entire documents in XML data. However, the necessary preprocessing support is not yet available.

JSON Input and Output Syntax

The input/output syntaxes in the JSON data type can be specified as in RFC 7159. The following example shows all valid JSON (or JSONB) expressions.

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

As mentioned earlier, when a JSON value is input and printed without further processing, JSON outputs the same text as the input, and JSONB does not retain syntactically insignificant content (e.g. spaces). Refer to the differences presented below.

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)

One thing to note, though not syntactically-significant, is that it is in JSONB, where numbers are printed according to the default numeric type. In practice, this means that the number marked as “e” will be omitted when printed. Here is an example:

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

As shown in this example, however, JSONB preserves the trailing zero (0) for a checkup even if it is syntactically insignificant.

Designing JSON documents effectively

Expressing data in JSON can be much more flexible than that in traditional relational data models where requirements are enforced in a variable environment. Both methods may coexist and complement each other in the same application. However, in applications that require maximum flexibility, it is recommended that the JSON documents have a slightly fixed structure. Structures are not generally applicable (you can also apply some business rules declaratively), but using a predictable structure makes it easier to write queries that usefully summarize a “document” (datum) set of tables. JSON data is affected by the same concurrency control considerations as other data types when stored in tables. It should be noted that, even though it is feasible to store a large document, updates obtain row-level locking for the entire row. You should consider limiting the JSON document to a manageable size in order to reduce lock contention between update transactions. In principle, JSON documents indicate that the atomic data pointed by business rules cannot be segmented into smaller datums, each of which can be modified independently.

JSONB Containment and Existence

Testing containment is an important feature of JSONB. There is no feature set similar to the JSON type. Containment tests whether a single JSONB document is contained in another document. This example returns true except where noted.

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

The general principle is that the structure and data content of the contained objects are consistent with the containing objects. It may be possible after discarding unmatched (inconsistent) array elements or object key/value pairs from the containing object. However, when comparing containments, the order of array elements is not important, and duplicate array elements are actually considered only once.

As a special exception to the general principle that structures must be matched, arrays may contain primitive values.

-- 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 has the existence operator that is a variation of the containing theme, which tests whether a string (specified as a text value) appears at the top level of the JSONB value as an object key or array element. This example returns true, except where noted.

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

Unlike arrays, JSON objects are optimized internally for search and do not perform linear searches. This means that they are more suitable than arrays that test for containment or existence when there are many related keys and elements.

JSONB Indexing

The GIN index can be used to efficiently search for a key or a key/value pair in a number of JSONB documents (datums). Two GIN “operator classes” with different performance and flexibility tradeoffs are provided. The default GIN operator classes for JSONB support queries using operators such as @>, ?, ?&, and ?|. Here is an example of creating an index within this operator class:

CREATE INDEX idxgin ON api USING GIN (jdoc);

jsonb_path_ops, which is not a default GIN operator class, supports indexing of @> operator only. Here is an example of creating an index within this operator class:

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

Consider a table example that stores a JSON document retrieved from a third-party Web service using a documented schema definition. The general document will be as follows:

{
    "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"
    ]
}

Save this document as a JSONB column called jdoc in a table called api. When a GIN index is created in this column, the following query uses the index.

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

However, even though the operator “?” can be indexed, it is not directly applied to the indexed column jdoc. Thus, the index cannot be used in the following query.

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

Still, the above query can use the index, if an expression index is properly used. If a query for a particular item is common within the “tags” key, an index definition like the one below is useful.

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

WHERE clause jdoc->‘tags’ ? ‘qui’ is an application of the indexable operator “?” and the indexed expression jdoc -> ‘tags’ is recognized.

Another way to query is to make the best use of containment. For example, a simple GIN index on a jdoc column can support this query.

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

However, while these indexes store copies of all the keys and values of the jdoc column, the expression index in the previous example stores only the data found under the tags key. It is true that the simple index approach is much more flexible (as it supports queries on arbitrary keys), but the targeted expression index is much smaller and more fast-searched than the simple index.

The jsonb_path_ops operator class only supports queries using @> operator, but it has a good performance advantage over the default operator class, jsonb_ops. With the same data, the size and search specificity of the jsonb_path_ops index are generally much smaller and better than those of the jsonb_ops index, respectively. This is especially true for queries that contain keys that appear frequently in the data. Searches with this class are therefore generally much better than using the default operator class.

A technical difference between jsonb_ops and jsonb_path_ops GIN indexes is that the former creates index entries that are independent for each key and value of the data, while the latter only creates index entries for the data values. By default, each jsonb_path_ops index entry is a hash of values and keys leading to it. Let’s take a look at an index {“foo”: {“bar”: “baz”} as an example; a single index entry is generated by combining all three of foo, bar, and baz into a hash value. Thus, a constraint query that looks for such a structure results in an extremely specific index search. However, there is no way to know at all whether foo will appear as a key. Conversely, the jsonb_ops index creates three index entries that represent foo, bar, and baz, respectively. Then, to execute a constraint query, it looks up a row that contains all three of these items. A GIN index can perform its AND search very efficiently, but is less specific and slower than an equivalent jsonb_path_ops search, especially when there are many rows containing one of the three index entries. A disadvantage of the jsonb_path_ops approach is that it does not create index entries for JSON structures that do not contain values such as {“a”: {}}. A full index scan is required if a document search containing the structure is requested, which is very slow. Thus, jsonb_path_ops is not suitable for applications that perform frequent searches. JSONB also supports btree and hash indexes, which is useful only when checking the equivalence of the entire JSON document. The B-tree ordering of JSONB data is not that important, but is necessary for completeness.

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

Objects that have the same number of pairs are compared in the following order:

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

Object keys are compared in the order of storage; in particular, storing short keys in front of long keys leads to non-intuitive results:

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

Similarly, arrays with the same number of elements are compared in the following order:

element-1, element-2 ...

The native JSON values are compared using the comparison rules, which also apply to the primitive data types. Strings are compared using the default database collation.

Declaration of Array Types

In order to explain use of array types, let’s create a table as follows:

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

As indicated, the array data type is named by adding square brackets ([]) to the array element’s data type name. The above command creates a table called sal_emp with a column of type text (name), where a one-dimensional array of type integer (pay_by_quarter) represents the employee’s quarterly salary and a two-dimensional array of text (schedule) indicates the employee’s weekly schedule. CREATE TABLE allows the exact size of the array to be specified. For example:

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

However, the current implementation ignores the array size limits provided. That is, the operation is the same as an array of unspecified length. The current implementation does not enforce the declared number of dimensions. Arrays of particular element types are considered to be of the same type, regardless of their size or the number of dimensions. Therefore, declaring an array size or the number of dimensions in CREATE TABLE is simply documentation and does not affect runtime behavior.

You can utilize the keyword ARRAY to use an alternative syntax that conforms to the SQL standard for one-dimensional arrays. pay_by_quarter may have been defined as:

pay_by_quarter  integer ARRAY[4],

If the array size is not specified:

pay_by_quarter  integer ARRAY,

In any case, however, size constraints are not enforced.

Array Value Input

To create an array value as a literal constant, place the value in braces and separate it with a comma. You can use double quotes around the value; you should do this if it contains a comma or brace. The general form of an array constant:

'{ val1 delim val2 delim ... }'

In the example above, delim is the delimiter for the type recorded in the pg_type entry. All of the standard data types provided by the AgensGraph distribution use commas, except for the type box that uses semicolons (;). Each val is a constant of the array element type or subarray. For example:

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

This constant is a two-dimensional 3x3 array consisting of three integer sub-arrays. To set an element of an array constant to NULL, create NULL for the element value. (NULL case can be changed.) If you want the actual string value “NULL,” use double quotation marks before and after NULL. (Constants are initially processed as strings and passed to the array input conversion routine, which may require explicit type specifications.)

The following is an example of INSERT statement and its execution result:

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)

A multidimensional array must have a matching range for each dimension, and an inconsistency will cause the following error:

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

The constructor syntax of ARRAY can also be used.

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

An array element is a regular SQL constant or expression. A string literal is enclosed in single quotes instead of double quotes as in an array literal.

Accessing Arrays

You can run some queries from the table created above. Access a single element of an array. The following query retrieves the name of the employee whose salary has been changed in the second quarter.

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

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

Array numbers are enclosed in square brackets; use array notation starting from 1. That is, the array of n elements starts at array[1] and ends at array[n].

This query retrieves the salary of all employees for the third quarter.

SELECT pay_by_quarter[3] FROM sal_emp;

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

You can also access any rectangular slice in an array or subarray. An array slice is represented using lower-bound:upper-bound for one or more array dimensions.

For example, the following query retrieves the first item in Bill’s schedule on the first two days of the week.

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

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

If you create a dimension as a slice (for example, including a colon), all dimensions are processed as slices. A dimension with only a single number (no colon) is processed as being from 1 to the specified number. For example, [2] is processed as [1: 2], as follows:

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

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

To avoid confusion in non-slice cases, it is best to use the slice syntax for all dimensions such as [1:2] [1:1] rather than [2] [1:1].

You can omit the lower-bound or upper-bound of the slice specifier. The missing bounds are replaced by lower or upper of the array, as shown in the following example:

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)

The array subscript expression returns null if the array itself or subscript expression is null. In addition, null is returned if the subscript is out of the bounds of the array (in which case no errors are produced). For example, schedule [3][3], which is referenced when the schedule is the current dimension [1:3] [1:2], prints NULL. Similarly, an array referencing an incorrect number of subscripts prints null, not an error.

Array slice expressions similarly return null if the array itself or the subscript expression is null. However, in other cases (e.g. selecting an array slice that is completely out of the bounds of the current array), the slice expression outputs an empty (zero-dimensional) array instead of null. If the requested slice partially overlaps the array bounds, it is reduced to an overlap region instead of returning null.

The current dimensions of array values can be retrieved using the array_dims function.

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

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

array_dims produces a text result, which is more readable to humans, but inconvenient to the program. Dimensions can also be retrieved using array_upper and array_lower, which return the upper and lower bounds of the specified array dimension, respectively.

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

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

array_length returns the length of the specified array dimension.

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

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

cardinality returns the total number of elements in an array of all dimensions; it is actually the number of rows generated by the call on unnest.

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

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

Modifying Arrays

You may update the array value as a whole;

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

or use ARRAY expression syntax.

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

An array can update a single element;

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

or update it only partially.

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

The omitted lower-bound or upper-bound slice syntax can be used only when updating NULL or nonzero array values.

The stored array value can be expanded by assigning it to an element that does not yet exist. The position between the previously existing element and the newly allocated element is filled with null. For example, if the array myarray currently has 4 elements, it will have 6 elements after an update of assigning to myarray [6]. myarray [5] contains null. Currently, the expansion in this manner is only allowed for one-dimensional arrays (not multidimensional arrays). Subscripted assignments allow you to create arrays whose subscripts do not start at one (1). For example, to create an array with subscript values -2 through 7, you may assign it to myarray [-2:7].

The new array value can also be created using the concatenation operator ||.

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)

The concatenation operator lets you push a single element into the beginning or end of a one-dimensional array. It accommodates two N-dimensional arrays (or N-dimensional and N+1-dimensional arrays).

If you push a single element to the beginning or end of a one-dimensional array as shown in the following example, the result is an array filled with the same lower bound subscript as the array operand.

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)

When concatenating two arrays having the same number of dimensions, the result holds the lower bound of the outer dimension of the left operand. The result is that all the elements of the right operand are followed by an array of all the elements of the left operand, as the following example shows:

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)

If you push an N-dimensional array from the beginning to the end of an N+1-dimensional array, the result is similar to the element array example above. Each N-dimensional subarray is essentially an element of the outer dimension of the N+1-dimensional array, as shown in the following example:

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

You can also create functions using the functions array_prepend, array_append, or array_cat; while the first two only support one-dimensional arrays, array_cat supports multidimensional arrays. The concatenation operator discussed above prefers the direct use of these functions. In effect, these functions exist primarily for use when implementing a concatenation operator. However, they can also be useful when creating user-defined aggregates. For example:

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

In a simple case, the concatenation operator described above is preferred over using these functions directly. However, using one of these functions may help avoid ambiguity, since the concatenation operator is overloaded to process all three cases.

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}

In the above example, parser sees an integer array on one side of the concatenation operator, and a constant of indeterminate type on the other. An empirical method used to analyze the constant type is to assume it is the same type as the other inputs of the operator (in this case, an integer array). So the concatenation operator is considered array_cat, not array_append. If that is a wrong choice, you can modify the constant by casting it to an element type of the array. Using array_append can be a desirable solution.

Searching in Arrays

To retrieve the value of an array, you should check each value; you may do this if you know the size of the array. For example:

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;

However, in large arrays, it quickly gets bored and is not helpful when the array size is unknown. The above query can be replaced by:

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

You can also find all the rows with an array value equal to 10000:

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

Alternatively, you can use the generate_subscripts function.

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;

It is also possible to search for an array using && operator, which checks whether the left operand overlaps with the right operand.

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

You can also use array_position and array_positions functions to retrieve a specific value in an array. The former returns the subscript of the first value in the array, and the latter returns an array with all the subscripts of the values in the array.

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

The external text expression of an array value consists of the I/O conversion rules for the array element type and the items that are interpreted according to the decoration denoting the array structure. The decoration consists of braces at both ends of the value of an array and delimiters between adjacent items. The delimiters are usually commas (,), but can be something else. It is determined by the typedelim setting for the element type of the array. All of the standard data types use commas, except for type box that uses semicolons (;). In a multidimensional array, each dimension (row, plane, cube, etc.) has its own level of braces and delimiters must be used between adjacent levels of brace entities.

Array output routines use double quotes around element values if they are empty strings, contain braces, delimiters, double quotes, backslashes, or whitespace, or match the word NULL. Double quotes and backslashes embedded in element values are escaped by a backslash. In the case of numeric data types, it is safe to assume that double quotes never appear. However, for text data types you must cope with the presence or absence of quotation marks. By default, the lower bound index value is set to 1 in the dimension of the array. If you specify an array using another lower bound, the range of array subscripts can be explicitly specified before creating the array content. This type of decoration consists of brackets ([]) before and after the lower/upper limits of each array dimension and colons (:) as space separator s. An array dimension decoration is followed by an equal sign (=).

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)

The array output routine includes an explicit dimension in the result only if there is more than one lower bound.

If the value created for an element is NULL (in the case of variation), the element is considered to be NULL. The presence of quotes or backslashes disables this and allows input of the literal string value “NULL.” You may also set the array_nulls configuration parameter to off to prevent NULL from being recognized as NULL. As indicated earlier, you can use double quotes around individual array elements when creating array values. This should be done in the case where the element values can be confused by the array value parser. For example, elements that contain braces, commas (or data type separators), double quotes, backslashes, and leading or trailing whitespace must use double-quotes. Empty strings and strings that match NULL must also be quoted. To add double quotes or backslashes into the quoted array element values, use an escape string syntax and precede it with a backslash. You may also use backslash escaping to avoid quotation marks and protect all data characters that might be processed incorrectly as array syntax.

You may add a space before the left brace or after the right brace. You can also add a space before or after the individual item string. Spaces are ignored in all these cases. Whitespaces within double-quote elements and spaces at both ends of non-whitespace characters in elements are ignored.

Built-in Range Types

The following built-in range types are provided:

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

You can also define your own range types. See User-defined Type for more information.

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

All non-empty ranges have two bounds, a lower bound and an upper bound. All points between these values are included in the range. Inclusive bounds mean that the boundary points themselves are included in the range, and exclusive bounds mean that the boundary points are not included in the range. In the text form of the range, the inclusive lower bound is expressed as “[” and the exclusive lower bound is expressed as ”(.” Similarly, the inclusive upper bound is expressed as ”]”, and the exclusive upper bound is expressed as “).”

The functions lower_inc and upper_inc test the lower and upper bounds of the range value, respectively.

Infinite (Unbounded) Ranges

The lower bound of the range can be omitted, meaning that all points below the upper bound are included in the range. Likewise, if the upper bound of the range is omitted, all points above the lower bound are included in the range. If both the lower and upper bounds are omitted, all values of the element type are considered to be included in the range.

This corresponds to considering that the lower bound is “negative infinity” or the upper bound is “positive infinity.” Note, however, that this infinite value is never a value of the element type of the range, and cannot be part of the range. (Therefore, there is no such thing as inclusive infinite bounds; when you try to create one, it is automatically converted to an exclusive bound).

It is true that some element types have an “infinite” notation, but it is another different value in relation to the range type mechanism. For example, in the timestamp range, [today,] is the same as [today,). However, [today, infinity] can be sometimes different from [today, infinity). The latter excludes the special timestamp value infinity.

The functions lower_inf and upper_inf test infinite lower/upper bounds of each range, respectively.

Range Input/Output

The input for the range value should follow one of the following patterns:

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

The parentheses or square brackets indicate whether the lower and upper bounds are excluded or included as described above. The last pattern is empty, indicating an empty range (a range with no points). The lower-bound can be a string that is a valid input to a subtype, or can be left empty if there is no lower bound. Similarly, upper-bound can be a string that is a valid input to a subtype or can be left empty if there is no upper bound. Each boundary value can be quoted using “(double quote) characters; this is a must since, if the boundary value contains parentheses, square brackets, commas, double quotes, or backslashes, these characters may be mistaken for part of the range syntax. To insert double quotes or backslashes to quoted boundary values, you must precede them with a backslash. (Double quotation pairs within boundary values in double-quotes are also processed as double quotation marks, similar to the rules for single quotation marks in SQL literal strings.) To protect all data characters that might be processed incorrectly with the range syntax, you may avoid quoting and use backslash escaping. In addition, when creating a boundary value that is an empty string, you should write”“; if you do not enter anything, it will mean an infinite boundary. Whitespaces are allowed before and after the range values, but spaces between parentheses or square brackets are considered to be part of the lower or upper bound value. (It may or may not be important depending on the element type).

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

Each range type has a constructor function with the same name as the range type. Using a constructor function is more convenient than writing a range literal constant, because you do not need to quote additional boundary values. A constructor function accepts two or three arguments. While the three-argument form creates a range from the third argument to the boundary of the form specified, the two-argument form creates a range of standard forms (lower bound, excluding upper bound). The third argument must be one of the followings strings: “()”, “(]”, “[]” or “[]”

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

The discrete range is a well-defined “step-by-step” type of which element type is integer or date. In this type, if there is no valid value between two elements, they can be said to be adjacent. In contrast to the continuous range, this is always (or almost always) possible to recognize different element values between the two given values. For example, a range beyond the numeric type, like the range beyond timestamp, is continuous. (timestamp can be processed discretely in theory because of its precision limitations, but it is better to regard it as a sequence when the size of the step is not of interest.) Another way to think about the discrete range type is to have a clear idea of the “next” or “previous” value for each element value. It should be noted that, by selecting the next or previous element value rather than the given original, it is possible to convert between expressions including the boundaries of the ranges and expressions excluding the boundaries of the ranges. For example, integer range types [4,8] and (3,9) denote the same set of values. This is not the case, however, for numerical ranges. The discrete range type must have a canonicalization function that recognizes the desired step size for the element type. The canonicalization function is especially responsible for converting the values equal to the range types that have an identity representation of a consistent inclusion or exclusion range. Unless a canonicalization function is specified, the ranges of different types are always processed as non-equivalence, even though they actually denote the same set of values. The built-in range types, int4range, int8range, and daterange, use the canonical form, which includes the lower bound and excludes the upper bound (i.e.”[)“). User-defined range types may use other notations.

Defining New Range Types

You may define your own range type. The most common reason for doing this is to use a subtype range that is not provided as a built-in range type. This is an example of defining a new range type for the subtype float8.

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

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

Since float8 is not a meaningful “step”, we do not define a canonicalization function in this example. If the subtype has a discrete value rather than a continuous value, CREATE TYPE command must specify a canonical function. The canonicalization function must take an input range value and return an equivalent range value that may be different from the boundary type. The canonical outputs of the two ranges representing the same set of values (e.g. integer range [1, 7] and [1, 8)) should be the same. It does not matter which expression is chosen to be canonical, as long as two equivalent values of the same type are always mapped to the same value of the same type. Besides controlling inclusive/exclusive bounds format, the canonicalization function can process boundary values well if the desired step size is greater than the subtype’s storable size. For example, the step size of a range type beyond timestamp can be defined as time. On this occasion, the canonicalization function may require rounding-off if it is not a multiple of the time, or an error may occur instead. If you define your own range type, you may specify different subtype B-tree operator classes or collations to use in order to change the sort order that determines which values belong to a given range. In addition, a range type to be used in a GiST or SP-GiST index should define a subtype difference or subtype_diff, a function(An index works without subtype_diff, but is much less efficient when a difference function is provided). The subtype difference function takes two input values of a subtype and returns the difference expressed as a float8 value (e.g. X minus Y). In the above example, you may use a function that underlies the normal float8 subtraction operator, but for other subtypes, type conversions may be required. Several creative ideas about how to represent differences in numbers may be needed. To the greatest range possible, the subtype_diff function must agree on the sort order implied by the selected operator class es and collations. That is, the result of this should always be a positive value when the first argument is greater than the second argument according to the sort order.

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;

For more information on creating range types, see User-defined Type.

Indexing

GiST and SP-GiST indexes are able to generate table columns of range type. The following is an example of creating a GiST index.

CREATE INDEX reservation_idx ON reservation USING GIST (during);

GiST or SP-GiST indexes can speed up queries involving range operators such as =, &&, <@, @>, <<, >>, -|-, &<, and &>.

B-tree and hash indexes may create table columns of range type as well. For these index types, the only useful range operation is basically “=”. Even if there is a B-tree sort order that uses “<” and “>” operators and is defined for range values, the order itself is arbitrary and not very useful in the real world. The B-tree and hash support for range types is intended primarily to allow sorting and hashing inside queries rather than creating actual indexes.

Constraints on Ranges

UNIQUE is a natural constraint on scalar values. However, it is appropriate not for range types but for exclusion constraints, mainly. An exclusion constraint allows the specification of constraints such as “nonoverlap” in range types.

Examples:

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

The constraint prevents overlapping values from being simultaneously present in the table.

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

You can use the btree_gist extension to define an exclusion constraint on a regular scalar data type, which makes it possible to exclude/combine the range with the maximum flexibility. For instance, after btree_gist is installed, the following constraint rejects overlapping ranges only when the number of meeting rooms is equal.

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