# 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](../../concepts/graph-on-postgresql.md) 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.` does not see it.
[Promoting a property to a column](../../querying/typed-columns.md) 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`](reindex.md). 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](../../concepts/label-inheritance.md).
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](../../get_started/example-dataset.md), and drop it at the end.
### Create a vertex label
```cypher
CREATE GRAPH filmstore;
SET graph_path = filmstore;
CREATE VLABEL film;
```
```
CREATE VLABEL
```
```psql
\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
```cypher
CREATE VLABEL IF NOT EXISTS film;
```
```
NOTICE: label "film" already exists, skipping
CREATE VLABEL
```
### Create a label under a parent
```cypher
CREATE VLABEL work;
CREATE VLABEL documentary INHERITS (work);
```
```psql
\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](../../concepts/label-inheritance.md) covers the rest.
### Load a label before building its indexes
```cypher
CREATE VLABEL bulk DISABLE INDEX;
```
```psql
\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
```cypher
CREATE UNLOGGED VLABEL staging WITH (fillfactor = 70) TABLESPACE pg_default;
```
```sql
SELECT relpersistence, reloptions FROM pg_class WHERE oid = 'filmstore.staging'::regclass;
```
```
relpersistence | reloptions
----------------+-----------------
u | {fillfactor=70}
(1 row)
```
### Promote two properties to typed columns
```cypher
CREATE VLABEL title (year int GENERATED, name text GENERATED);
```
```psql
\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:
```cypher
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:
```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:
```cypher
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
```cypher
CREATE VLABEL review (released text GENERATED (releaseDate));
```
```psql
\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:
```cypher
CREATE VLABEL slug (handle text GENERATED ALWAYS AS (lower(properties ->> 'name')) STORED);
```
```psql
\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
```
```cypher
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:
```sql
SELECT handle FROM filmstore.slug;
```
```
handle
---------
arrival
(1 row)
```
But it answers to no property key, so Cypher does not see it:
```cypher
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:
```cypher
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:
```cypher
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.
```
```cypher
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.
```
```cypher
CREATE VLABEL bad3 (released date GENERATED);
```
```
ERROR: generation expression is not immutable
```
```cypher
CREATE VLABEL bad4 (id int GENERATED);
```
```
ERROR: promoted property name "id" is reserved
```
```cypher
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:
```cypher
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:
```cypher
CREATE VLABEL ONLY restored (300);
```
```sql
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:
```psql
\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:
```cypher
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*, `` (§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](../../appendix/gql-conformance.md).
### 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`](alter-label.md).
## See also
- [`CREATE ELABEL`](create-elabel.md)
- [`ALTER VLABEL` and `ALTER ELABEL`](alter-label.md)
- [`DROP VLABEL` and `DROP ELABEL`](drop-label.md)
- [`CREATE PROPERTY INDEX`](create-property-index.md)
- [Promoting a property to a column](../../querying/typed-columns.md)
- [Label inheritance](../../concepts/label-inheritance.md)