References → SQLi Query Limitations
Overview
The SQL Interface (SQLi) allows running PostgreSQL queries over Incorta, which enables third-party BI tools, such as Tableau and Power BI, to connect to Incorta as a PostgreSQL data source and Incorta to serve as their data source.
In addition to queries from third-party tools, SQLi supports the syntax of PostgreSQL 9.6 in the following contexts in Incorta:
- PostgreSQL materialized views (MVs)
- PostgreSQL views
- Incorta SQL tables
- Physical PostgreSQL tables created based on an Incorta PostgreSQL data source that uses the SQLi connection to query data available in physical and business schemas in Incorta
Although SQLi is PostgreSQL-compliant, there are some limitations and discrepancies between queries supported by PostgreSQL and Incorta SQLi.
Unsupported syntax
The following table illustrates the unsupported PostgreSQL syntax in Incorta SQLi.
| Unsupported Syntax | Notes |
|---|---|
ALL | |
ANY | |
ARRAY_AGG | Returns a string, not an array, because Incorta does not support the array data type |
CUBE | |
CURRENT_TIMESTAMP(precision) | |
CURRENT_TIME(precision) | |
EXCEPT ALL | Returns incorrect results |
factorial | ● Supported in PostgreSQL MVs and physical PostgreSQL tables for computations with results that do not exceed the INT8 (64-bit integer) value range ● Not supported in PostgreSQL views or Incorta SQL tables |
GROUP BY | The function is supported; however, using brackets () is not supported. |
GROUP SET | |
INTERSECT ALL | |
LIMIT | The function is supported; however, it does not support Double values. |
LOCALTIME / LOCALTIME(precision) | |
LOCALTIMESTAMP / LOCALTIMESTAMP(precision) | |
OFFSET | ● Ignored on the Engine port ● Works correctly on the Spark port Does not support Double values |
ORDER BY with the USING operator | Returns incorrect results |
overlay | |
quote_ident | |
RECURSIVE in WITH clauses | |
ROLLUP | |
STRING_AGG | |
All functions that return arrays, such as: REGEXP_MATCH REGEXP_MATCHES REGEXP_SPLIT_TO_ARRAY | |
All functions that return time data type, such as time | Incorta and Spark do not support the TIME data type; however, both support DATE and TIMESTAMP data types. As a result, SQLi queries that attempt to use the TIME data type will fail. To ensure compatibility, use the TIMESTAMP data type instead. ● Failing Example: SELECT EXTRACT(EPOCH FROM time '1970-01-01 12:30:20') FROM SALES.SALES LIMIT 1; ● Working Example: SELECT EXTRACT(EPOCH FROM timestamp '1970-01-01 12:30:20') FROM SALES.SALES LIMIT 1; |
| Division by zero | Returns NULL in Incorta, while it should throw an error, as in PostgreSQL |
SQLi query best practices
Engine functions vs. PostgreSQL functions
While SQLi may accept Incorta built-in functions, they are not native PostgreSQL functions, and using them may lead to inconsistent results or unexpected behavior.
Always use native PostgreSQL functions for reliability.
Identifier naming conventions
- Always wrap identifier names, including schema, table, view, and column names, in double quotes, especially when:
- Using PostgreSQL reserved keywords for the name.
- Enforcing case-sensitivity.
- The maximum allowed length for the names of schemas, tables, views, and columns referenced in a SQLi query is 127 characters. Any name exceeding this limit will result in a query error. Recommendation
Keep object names within this character limit to ensure compatibility.
Date and Timestamp data
- The supported timestamp ranges are:
- Month: 0 to 11 (where 0 = January, 11 = December)
- Date: 1 to 31
- Hour: 0 to 23
- Minute: 0 to 59
- Second: 0 to 59
- Nano: 0 to 999,999,999