How to create a unique constraint like in AgensGraph?

[bravius]

In AgensGraph I could create a unique property constraint using this syntax:

CREATE CONSTRAINT ON account ASSERT email IS UNIQUE;

What is the equivalent in Apache AGE for creating a unique constraint on a property?

[pdpotter]

There has been some discussion on this subject in #45. There has recently been a commit (57e11a3) that fixes an issue with updating the indices.

To create a unique constraint, you first need to create an immutable function (a requirement for creating an index)

CREATE OR REPLACE FUNCTION get_email(properties agtype) 
  RETURNS agtype
AS
$BODY$
    select agtype_access_operator($1, '"email"');
$BODY$
LANGUAGE sql
IMMUTABLE;

and then use this function for the creation of the unique constraint

CREATE UNIQUE INDEX account_email_idx ON mygraph.account(get_email(properties)) ;

[pdpotter]

Nice! Thanks for sharing your improved solution, I will probably use it as well.

[JoshInnis]

The root cause of this seems to be that the agtype_access_operator is not IMMUTABLE correct? If that was the case, there would be no need to typecast to JsonB or create helper functions that use the access operator.

[bravius]

If I try this I get "ERROR: operator does not exist: agtype ->> unknown":

CREATE UNIQUE INDEX account_email_index ON account((lower(properties->>'email')))

If I explicitly cast to jsonb instead I get "ERROR: cannot cast type agtype to jsonb":

CREATE UNIQUE INDEX account_email_index ON account((lower(properties::jsonb->>'email')))

If I cast to varchar then jsonb I get "ERROR: functions in index expression must be marked IMMUTABLE":

CREATE UNIQUE INDEX account_email_index ON account((lower(properties::varchar::jsonb->>'email')))

The only way I've been able to get it to work is using a custom cast function, such as the one I mentioned above. Then all the usual json operators can be used for indexing.

[JoshInnis]

Yes, we are working on a patch to improve some performance issues, part of those fixes was to make agtype_access_operator IMMUTABLE, this is was intended to allow Index scans to be usable on property values in a cypher query. However, it seems that this change will affect this too.

[JoshInnis]

Also it appears that creating the -> ->> #> #>> operators for Agtype is desired as well, and functionality to cast agtype to JsonB

[emotionbug]

Looking at this, I hope it have following function.

1. Agtype access operator ( -> ->> #> #>> )
2. Agtype to Jsonb casting function.

[JoshInnis]

Isn't all the functionality offered by -> ->> #> #>> contained with agtype_access_operator? So making those operators would mostly be making helper functions/operators around agtype_access_operator

[emotionbug]

Yes, that's right, but the function is not listed in the document. therefore, there is a problem that users do not know how to approach.

As an alternative, the above operators are PostgreSQL's approach to JSON(B), so I think users can use them naturally. Like the case that the above users wrote.

It's already been worked on, so I'll leave it as PR

[JoshInnis]

Perhaps this should be added to the documentation if users are not sure how to do it.

[JoshInnis]

One issue to be discussed where these operators could be potentially different from the access operator is how they handle vertices, edges, and paths. Should MATCH (n) WHERE n->'prop_name' = 'value' work the same way MATCH (n) WHERE n.'prop_name' = 'value' where the properties of the vertex are used or should the user need to enter MATCH (n) WHERE n->'properties'->'prop_name' = 'value'. Personally I believe the first way is better as it is consistent with the . operator.

[bravius]

The new agtype access operators -> and ->> work great for indexing, thanks for adding those.