CREATE VLABEL#

Creates a vertex label — a table of (id, properties) that inherits the graph’s base vertex label, optionally with properties promoted to typed columns.

Synopsis#

CREATE [ UNLOGGED ] VLABEL [ IF NOT EXISTS ] label_name [ ( promoted_property [, ... ] ) ]
    [ DISABLE INDEX ]
    [ INHERITS ( parent_label [, ... ] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]

CREATE [ UNLOGGED ] VLABEL ONLY label_name ( label_id ) [ option ... ]

where promoted_property is one of:

    column_name data_type GENERATED
    column_name data_type GENERATED ( property_key )
    column_name data_type GENERATED ALWAYS AS ( expression ) [ STORED ]

bold keywords · italic placeholders you supply · [ ] optional · ... repeatable. See Syntax conventions.

Description#

A vertex label is a table with an id of type graphid and a properties column of type jsonb. It inherits the graph’s ag_vertex unless you name a parent. Creating one also creates the sequence that numbers its ids and the primary key on id. How a graph is stored covers that layout.

The label is created in the graph named by graph_path.

The options are position-sensitive. They have to appear in the order shown: DISABLE INDEX, then INHERITS, then WITH, then TABLESPACE. Any other order is a syntax error.
A parent named in INHERITS must be in the same graph and must be a vertex label.
IF NOT EXISTS and ONLY belong to separate forms and cannot be combined.
Cypher creates a label on demand: CREATE (:film) against a graph with no film label creates one. Use CREATE VLABEL when you want options, promoted columns or a parent.

Promoted properties#

A promoted property is a real typed column beside the properties bag, computed from the bag and stored. Reads of that property go to the column instead of walking the JSON document. The three forms differ in where the value comes from:

column_name data_type GENERATED mirrors the property whose key is the column name.
column_name data_type GENERATED ( property_key ) mirrors the named key, so the column can be called something else. The key is an identifier and is down-cased, matching the way Cypher stores map keys.
column_name data_type GENERATED ALWAYS AS ( expression ) STORED computes the column from a SQL expression over the label’s own columns.

Whether a column answers for a property depends on the expression, not on which form wrote it. A plain read of one key out of the bag, such as properties ->> 'year' with or without a cast, is recorded in ag_label_property. That is the catalog a Cypher property read consults. Wrap the read in anything else and the column is an ordinary generated column: SQL reads it, and n.<key> does not see it. Promoting a property to a column explains when promotion is worth it.

Four things a promoted property cannot be:

Not generated. A bare column_name data_type is refused with a non-generated promoted column is not supported yet.
VIRTUAL. A promoted column has to be materialized to carry an index, so VIRTUAL is refused with promoted generated properties must be STORED. STORED is the default and may be left out.
GENERATED BY DEFAULT. Only GENERATED ALWAYS is accepted.
Named id, properties, start or end. Those are the structural columns of a graph element, and the name is refused as reserved.

The data type has to be one whose text input function is immutable, and it cannot fix a length. date, timestamp, timestamptz and array types are refused with generation expression is not immutable. varchar(n) and char(n) are refused because fitting a value to a length would let the column and the property hold different values. smallint, int, bigint, numeric, real, double precision, text, boolean, uuid and jsonb are accepted.

Parameters#

UNLOGGED: Create the label’s table as unlogged: its contents are not written to the write-ahead log and are lost after a crash.
IF NOT EXISTS: Issue a notice instead of an error when a label of that name already exists. Nothing is created; the existing label’s options are not changed.
label_name: Name of the label to create, in the graph named by graph_path.
promoted_property: A property to mirror into a typed column, in one of the three forms above. Columns are added after properties, in the order written.
DISABLE INDEX: Create the label’s indexes but leave them invalid, so that loading rows does not maintain them. Reads and writes still work; the planner ignores an invalid index. Rebuild with REINDEX VLABEL. There is no ENABLE INDEX.
INHERITS ( parent_label [, …] ): Parent labels, replacing the default parent ag_vertex. Each must be a vertex label in the same graph. See Label inheritance.
WITH ( storage_parameter [= value] [, …] ): Storage parameters for the label’s table, as for CREATE TABLE.
TABLESPACE tablespace_name: Tablespace for the label’s table. Without it, default_tablespace decides.
ONLY label_name ( label_id ): Create the label with the given label id, and without the default parent, the id default or the primary key. The label id is the high 16 bits of every graphid the label hands out. This is the form pg_dump emits so that a restore preserves the ids in a dumped graph. A label created this way is not usable from Cypher until those are put back, so write it only when reproducing a dump.

Examples#

The examples build their own graph, filmstore, rather than using the example dataset, and drop it at the end.

Create a vertex label#

CREATE GRAPH filmstore;
SET graph_path = filmstore;
CREATE VLABEL film;

CREATE VLABEL

\d filmstore.film

                                                          Table "filmstore.film"
   Column   |  Type   | Collation | Nullable |                                           Default                                           
------------+---------+-----------+----------+---------------------------------------------------------------------------------------------
 id         | graphid |           | not null | graphid(graph_labid('filmstore.film'::cstring), nextval('filmstore.film_id_seq'::regclass))
 properties | jsonb   |           | not null | jsonb_build_object()
Indexes:
    "film_pkey" PRIMARY KEY, btree (id)
Inherits: filmstore.ag_vertex

Create a label only if it is not there#

CREATE VLABEL IF NOT EXISTS film;

NOTICE:  label "film" already exists, skipping
CREATE VLABEL

Create a label under a parent#

CREATE VLABEL work;
CREATE VLABEL documentary INHERITS (work);

\dGv filmstore.documentary

              List of labels
   Graph   |    Name     |  Type  | Owner
-----------+-------------+--------+-------
 filmstore | documentary | vertex | agens
(1 row)

Vertex label "filmstore.documentary"
--
Inherits: filmstore.work

documentary inherits work instead of ag_vertex, so MATCH (n:work) also matches documentaries. Label inheritance covers the rest.

Load a label before building its indexes#

CREATE VLABEL bulk DISABLE INDEX;

\d filmstore.bulk

                                                          Table "filmstore.bulk"
   Column   |  Type   | Collation | Nullable |                                           Default                                           
------------+---------+-----------+----------+---------------------------------------------------------------------------------------------
 id         | graphid |           | not null | graphid(graph_labid('filmstore.bulk'::cstring), nextval('filmstore.bulk_id_seq'::regclass))
 properties | jsonb   |           | not null | jsonb_build_object()
Indexes:
    "bulk_pkey" PRIMARY KEY, btree (id) INVALID
Inherits: filmstore.ag_vertex

The primary key exists but is marked INVALID. Load the rows, then run REINDEX VLABEL bulk to build it.

Set storage parameters and a tablespace#

CREATE UNLOGGED VLABEL staging WITH (fillfactor = 70) TABLESPACE pg_default;

SELECT relpersistence, reloptions FROM pg_class WHERE oid = 'filmstore.staging'::regclass;

 relpersistence |   reloptions    
----------------+-----------------
 u              | {fillfactor=70}
(1 row)

Promote two properties to typed columns#

CREATE VLABEL title (year int GENERATED, name text GENERATED);

\d filmstore.title

                                                           Table "filmstore.title"
   Column   |  Type   | Collation | Nullable |                                            Default                                            
------------+---------+-----------+----------+-----------------------------------------------------------------------------------------------
 id         | graphid |           | not null | graphid(graph_labid('filmstore.title'::cstring), nextval('filmstore.title_id_seq'::regclass))
 properties | jsonb   |           | not null | jsonb_build_object()
 year       | integer |           |          | generated always as (ag_property_text(properties, 'year'::text, 'n'::"char")::integer) stored
 name       | text    |           |          | generated always as (ag_property_text(properties, 'name'::text, 's'::"char")) stored
Indexes:
    "title_pkey" PRIMARY KEY, btree (id)
Inherits: filmstore.ag_vertex

Nothing about writing changes. The property still goes into the bag, and the column follows:

CREATE (:title {name: 'Arrival', year: 2016});
MATCH (t:title) RETURN t.name AS name, t.year AS year;

  name   | year 
---------+------
 Arrival | 2016
(1 row)

The same values are in the columns, reachable from SQL:

SELECT name, year FROM filmstore.title;

  name   | year 
---------+------
 Arrival | 2016
(1 row)

A property whose value does not match the column’s kind is refused at write time, naming both sides:

CREATE (:title {name: 42, year: 2016});

ERROR:  property "name" holds a number
DETAIL:  A column promoting this property holds strings.
CONTEXT:  while computing the promoted typed columns of graph label "title"

Promote a property whose key differs from the column name#

CREATE VLABEL review (released text GENERATED (releaseDate));

\d filmstore.review

                                                           Table "filmstore.review"
   Column   |  Type   | Collation | Nullable |                                             Default                                             
------------+---------+-----------+----------+-------------------------------------------------------------------------------------------------
 id         | graphid |           | not null | graphid(graph_labid('filmstore.review'::cstring), nextval('filmstore.review_id_seq'::regclass))
 properties | jsonb   |           | not null | jsonb_build_object()
 released   | text    |           |          | generated always as (ag_property_text(properties, 'releasedate'::text, 's'::"char")) stored
Indexes:
    "review_pkey" PRIMARY KEY, btree (id)
Inherits: filmstore.ag_vertex

The key was written releaseDate and stored as releasedate, because a property key is an identifier here and identifiers are down-cased. Cypher reads this property as n.releasedate; SQL reads the column as released.

Compute a column from the property bag#

The long form takes a SQL expression over the label’s own columns rather than a property key:

CREATE VLABEL slug (handle text GENERATED ALWAYS AS (lower(properties ->> 'name')) STORED);

\d filmstore.slug

                                                          Table "filmstore.slug"
   Column   |  Type   | Collation | Nullable |                                           Default                                           
------------+---------+-----------+----------+---------------------------------------------------------------------------------------------
 id         | graphid |           | not null | graphid(graph_labid('filmstore.slug'::cstring), nextval('filmstore.slug_id_seq'::regclass))
 properties | jsonb   |           | not null | jsonb_build_object()
 handle     | text    |           |          | generated always as (lower(properties ->> 'name'::text)) stored
Indexes:
    "slug_pkey" PRIMARY KEY, btree (id)
Inherits: filmstore.ag_vertex

CREATE (:slug {name: 'Arrival'});

UPDATE 1

A Cypher write reports its row count with the tag UPDATE, whatever the write was, and the column is computed and stored:

SELECT handle FROM filmstore.slug;

 handle  
---------
 arrival
(1 row)

But it answers to no property key, so Cypher does not see it:

MATCH (s:slug) RETURN s.handle AS handle;

 handle 
--------
 
(1 row)

s.handle reads the key handle out of the bag, which nothing ever wrote.

A long form whose expression is a plain read of one key is recorded like the shorthand, and Cypher does see it:

CREATE VLABEL runtime (minutes int GENERATED ALWAYS AS ((properties ->> 'minutes')::int) STORED);
CREATE (:runtime {minutes: 116});
MATCH (r:runtime) RETURN r.minutes AS minutes;

 minutes 
---------
     116
(1 row)

So reach for one key when the column should answer for that property, and compute from it when the column is for SQL alone.

Refusals worth recognizing#

Each of these is rejected outright:

CREATE VLABEL bad1 (name varchar(40) GENERATED);

ERROR:  a promoted property cannot have a length-limited string type
DETAIL:  Type character varying fits a value to its length by truncating or padding it.
HINT:  Use text, which stores the property as it is.

CREATE VLABEL bad2 (name text);

ERROR:  a non-generated promoted column is not supported yet
HINT:  Use GENERATED to mirror the property from the jsonb bag.

CREATE VLABEL bad3 (released date GENERATED);

ERROR:  generation expression is not immutable

CREATE VLABEL bad4 (id int GENERATED);

ERROR:  promoted property name "id" is reserved

CREATE VLABEL bad5 (name text GENERATED ALWAYS AS (properties ->> 'name') VIRTUAL);

ERROR:  promoted generated properties must be STORED
LINE 1: ...e text GENERATED ALWAYS AS (properties ->> 'name') VIRTUAL);
                                                              ^
HINT:  A VIRTUAL generated column cannot carry an index.

A property key has to lead to one column all the way down a hierarchy. So promoting a key a parent already promotes is refused unless the column name matches:

CREATE VLABEL feature (year int GENERATED) INHERITS (work);
CREATE VLABEL short (released int GENERATED (year)) INHERITS (feature);

ERROR:  property "year" is already promoted on graph label "feature"
DETAIL:  It answers from column "year" there and would answer from column "released" here.
HINT:  Promote it to the same column name, or leave it to the label that has it.

The mirror image is refused too: promoting a different key to a column name an ancestor already uses would make two keys read one inherited column.

Recreate a label with a given label id#

Label ids come from the graph’s ag_label_seq in creation order. ONLY sets one explicitly, which is how a restore reproduces the ids a dump captured:

CREATE VLABEL ONLY restored (300);

SELECT l.labname, l.labid FROM pg_catalog.ag_label l
  JOIN pg_catalog.ag_graph g ON g.oid = l.graphid
 WHERE g.graphname = 'filmstore' ORDER BY l.labid;

   labname   | labid 
-------------+-------
 ag_vertex   |     1
 ag_edge     |     2
 film        |     3
 work        |     4
 documentary |     5
 bulk        |     6
 staging     |     7
 title       |     8
 review      |     9
 slug        |    10
 runtime     |    11
 feature     |    12
 restored    |   300
(13 rows)

The label has no parent, no id default and no primary key. So it is not usable from Cypher as it stands:

\d filmstore.restored

              Table "filmstore.restored"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 id         | graphid |           | not null | 
 properties | jsonb   |           | not null | 

Reusing an id the graph has already given out fails on the unique index over (graphid, labid).

Finally, drop the graph the examples built:

DROP GRAPH filmstore CASCADE;

Notes#

The label name cannot be qualified. CREATE VLABEL filmstore.film is a syntax error, and the graph always comes from graph_path. With none set, the statement stops with graph_path is NULL.

\dGv lists vertex labels and describes each one it matches; \dGl lists labels of both kinds without the detail.

Note

enable_property_promotion decides whether a Cypher property read is answered from a promoted column. It defaults to on, and turning it off can change results as well as plans: a column compares and orders values with its own type’s rules, while the jsonb bag uses jsonb ordering.

Compatibility#

ISO/IEC 39075 (GQL)#

GQL has no label DDL. Its schema mechanism is the graph type, <create graph type statement> (§12.6). A graph type declares node types and edge types for a whole graph at once, and constrains a graph that references it. AgensGraph declares labels one at a time, and its graphs are open: a label puts no restriction on which properties its vertices carry. Graph types are tracked as planned in the GQL conformance matrix.

PostgreSQL#

CREATE VLABEL corresponds to CREATE TABLE. UNLOGGED, IF NOT EXISTS, INHERITS, WITH and TABLESPACE mean what they mean there, and the promoted-property forms are GENERATED ALWAYS AS ... STORED generated columns. CREATE TABLE has none of the rest: a fixed column list you cannot choose, a registration in ag_label and ag_label_property, an automatic parent, and the position-sensitive option order. Going the other way, a label’s table cannot be reshaped with ALTER TABLE unless enable_graph_ddl is on. See ALTER VLABEL and ALTER ELABEL.