6.7 Supported PGQL Features and Limitations

The following table provides the complete list of supported and unsupported PGQL functionalities for:

• PGQL on RDBMS (PG_SCHEMA)
• PGQL on RDBMS (PG_VIEW)
• PGQL on the graph server (PGX)

Table 6-2 Supported PGQL Functionalities and Limitations

Feature	PGQL on RDBMS (PG_SCHEMA)	PGQL on RDBMS (PG_VIEW)	PGQL on the Graph Server (PGX)
CREATE PROPERTY GRAPH	Supported	Supported	Supported Limitations: No composite keys for vertices
DROP PROPERTY GRAPH	Supported	Supported	Not Supported
Fixed-length pattern matching	Supported	Supported	Supported
Variable-length pattern matching goals	Supported: Reachability Limitations: ANY ANY SHORTEST TOP k SHORTEST ALL SHORTEST ANY CHEAPEST TOP k CHEAPEST ALL	Supported: Reachability ANY ANY SHORTEST TOP k SHORTEST ALL Limitations: ALL SHORTEST ANY CHEAPEST TOP k CHEAPEST	Supported: Reachability ANY ANY SHORTEST TOP k SHORTEST ALL SHORTEST ANY CHEAPEST TOP k CHEAPEST ALL
Variable-length pattern matching quantifiers	Supported: * + ? { n } { n, } { n, m } { , m }	Supported: * + ? { n } { n, } { n, m } { , m }	Supported: * + ? { n } { n, } { n, m } { , m } Limitations: ? is only supported for reachability In case of ANY CHEAPEST and TOP k CHEAPEST, only * is supported
Variable-length path unnesting	Not supported	Not supported	Supported: ONE ROW PER VERTEX ONE ROW PER EDGE
GROUP BY	Supported	Supported	Supported
HAVING	Supported	Supported	Supported
Aggregations	Supported: COUNT MIN, MAX, AVG, SUM Limitations: LISTAGG ARRAY_AGG	Supported: COUNT MIN, MAX, AVG, SUM, LISTAGG Limitations: ARRAY_AGG	Supported: COUNT MIN, MAX, AVG, SUM LISTAGG ARRAY_AGG Limitations: ARRAY_AGG is only supported as horizontal aggregation (in combination with variable-length path) but not in combination with vertical aggregation
DISTINCT SELECT DISTINCT Aggregation with DISTINCT (such as, COUNT(DISTINCT e.prop))	Supported	Supported	Supported
SELECT v.*	Not Supported	Supported	Supported
ORDER BY (+ASC/DESC), LIMIT, OFFSET	Supported	Supported	Supported
Data Types	Supported: NVARCHAR2(15000) NUMBER BOOLEAN (stored like NVARCHAR) TIMESTAMP(6) WITH TIME ZONE	All available Oracle RDBMS data types supported	Supported: INTEGER (32-bit) LONG (64-bit) FLOAT (32-bit) DOUBLE (64-bit) STRING (no maximum length) BOOLEAN DATE TIME TIME WITH TIME ZONE TIMESTAMP TIMESTAMP WITH TIME ZONE
JSON	No built-in JSON support. However, JSON strings (VARCHAR2) can be mapped into NVARCHAR2(15000) data type.	Supported: JSON storage: JSON strings (VARCHAR2) JSON objects JSON functions: Any JSON function call that follows the syntax, json_function_name(arg1, arg2,…). For example: json_value(department_data, '$.department') Limitations: Simple Dot Notation Any optional clause in a JSON function call (such as RETURNING, ERROR, and so on) is not supported. For example: json_value(department_data, '$.employees[1].hireDate' RETURNING DATE)	No built-in JSON support. However, JSON values can be stored as STRING and manipulated or queried through user-defined functions (UDFs) written in Java or JavaScript.
Operators	Supported: Relational: +, -, *, /, %, - (unary minus) Arithmetic: =, <>, <, >, <=, >= Logical: AND, OR, NOT Limitations: String: || (concat)	Supported: Relational: +, -, *, /, %, - (unary minus) Arithmetic: =, <>, <, >, <=, >= Logical: AND, OR, NOT String: || (concat)	Supported: Relational: +, -, *, /, %, - (unary minus) Arithmetic: =, <>, <, >, <=, >= Logical: AND, OR, NOT String: || (concat)
Functions and predicates	Supported: IS NULL, IS NOT NULL JAVA_REGEXP_LIKE (based on CONTAINS) ABS, CEIL/CEILING, FLOOR, ROUND EXTRACT ID LABEL, HAS_LABEL ALL_DIFFERENT IN_DEGREE, OUT_DEGREE CAST CASE IN and NOT IN Limitations: LOWER, UPPER SUBSTRING LABELS	Supported are all available functions in the Oracle RDBMS that take the form function_name(arg1, arg2, ...) with optional schema and package qualifiers. Supported PGQL functions/predicates: IS NULL, IS NOT NULL JAVA_REGEXP_LIKE (based on CONTAINS) LOWER, UPPER SUBSTRING ABS, CEIL/CEILING, FLOOR, ROUND EXTRACT ID LABEL, HAS_LABEL ALL_DIFFERENT CAST CASE IN and NOT IN Limitations: LABELS IN_DEGREE, OUT_DEGREE	Supported: IS NULL, IS NOT NULL JAVA_REGEXP_LIKE (based on CONTAINS) LOWER, UPPER SUBSTRING ABS, CEIL/CEILING, FLOOR, ROUND EXTRACT ID LABEL, LABELS, HAS_LABEL ALL_DIFFERENT IN_DEGREE, OUT_DEGREE CAST CASE IN and NOT IN MATCH_NUMBER ELEMENT_NUMBER
User-defined functions	Not supported	Supported: PL/SQL functions Functions created via the Oracle Database Multilingual Engine (MLE)	Supported: Java UDFs JavaScript UDFs
Subqueries: Scalar subqueries EXISTS and NOT EXISTS subqueries	Supported	Not supported	Supported
INSERT/UPDATE/DELETE	Supported	Supported	Supported