Summary of WarehousePG Features v6.27.4

This section is adapted from the WarehousePG v6.x documentation

This section provides a high-level overview of the system requirements and feature set of WarehousePG. It contains the following topics:

WarehousePG SQL Standard Conformance

The SQL language was first formally standardized in 1986 by the American National Standards Institute (ANSI) as SQL 1986. Subsequent versions of the SQL standard have been released by ANSI and as International Organization for Standardization (ISO) standards: SQL 1989, SQL 1992, SQL 1999, SQL 2003, SQL 2006, and finally SQL 2008, which is the current SQL standard. The official name of the standard is ISO/IEC 9075-14:2008. In general, each new version adds more features, although occasionally features are deprecated or removed.

It is important to note that there are no commercial database systems that are fully compliant with the SQL standard. WarehousePG is almost fully compliant with the SQL 1992 standard, with most of the features from SQL 1999. Several features from SQL 2003 have also been implemented (most notably the SQL OLAP features).

This section addresses the important conformance issues of WarehousePG as they relate to the SQL standards. For a feature-by-feature list of WarehousePG's support of the latest SQL standard, see SQL 2008 Optional Feature Compliance.

Core SQL Conformance

In the process of building a parallel, shared-nothing database system and query optimizer, certain common SQL constructs are not currently implemented in WarehousePG. The following SQL constructs are not supported:

Some set returning subqueries in EXISTS or NOT EXISTS clauses that WarehousePG's parallel optimizer cannot rewrite into joins.

Backwards scrolling cursors, including the use of FETCH PRIOR, FETCH FIRST, FETCH ABSOLUTE, and FETCH RELATIVE.

In CREATE TABLE statements (on hash-distributed tables): a UNIQUE or PRIMARY KEY clause must include all of (or a superset of) the distribution key columns. Because of this restriction, only one UNIQUE clause or PRIMARY KEY clause is allowed in a CREATE TABLE statement. UNIQUE or PRIMARY KEY clauses are not allowed on randomly-distributed tables.

CREATE UNIQUE INDEX statements that do not contain all of (or a superset of) the distribution key columns. CREATE UNIQUE INDEX is not allowed on randomly-distributed tables.
Note that UNIQUE INDEXES (but not UNIQUE CONSTRAINTS) are enforced on a part basis within a partitioned table. They guarantee the uniqueness of the key within each part or sub-part.

VOLATILE or STABLE functions cannot run on the segments, and so are generally limited to being passed literal values as the arguments to their parameters.
Triggers are not generally supported because they typically rely on the use of VOLATILE functions. PostgreSQL Event Triggers are supported because they capture only DDL events.

Referential integrity constraints (foreign keys) are not enforced in WarehousePG. Users can declare foreign keys and this information is kept in the system catalog, however.

Sequence manipulation functions CURRVAL and LASTVAL.

SQL 1992 Conformance

The following features of SQL 1992 are not supported in WarehousePG:

2. `NATIONAL CHARACTER` (`NCHAR`) and `NATIONAL CHARACTER VARYING` (`NVARCHAR`). Users can declare the `NCHAR` and `NVARCHAR` types, however they are just synonyms for `CHAR` and `VARCHAR` in WarehousePG.4. `CREATE ASSERTION` statement.6. `INTERVAL` literals are supported in WarehousePG, but do not conform to the standard.8. `GET DIAGNOSTICS` statement.10. `GLOBAL TEMPORARY TABLE`s and `LOCAL TEMPORARY TABLE`s. WarehousePG `TEMPORARY TABLE`s do not conform to the SQL standard, but many commercial database systems have implemented temporary tables in the same way. WarehousePG temporary tables are the same as `VOLATILE TABLE`s in Teradata.12. `UNIQUE` predicate.14. `MATCH PARTIAL` for referential integrity checks (most likely will not be implemented in WarehousePG).

SQL 1999 Conformance

The following features of SQL 1999 are not supported in WarehousePG:

Large Object data types: BLOB, CLOB, NCLOB. However, the BYTEA and TEXT columns can store very large amounts of data in WarehousePG (hundreds of megabytes).

MODULE (SQL client modules).
CREATE PROCEDURE (SQL/PSM). This can be worked around in WarehousePG by creating a FUNCTION that returns void, and invoking the function as follows:
```
SELECT <myfunc>(<args>);
```

The PostgreSQL/WarehousePG function definition language (PL/PGSQL) is a subset of Oracle's PL/SQL, rather than being compatible with the SQL/PSM function definition language. WarehousePG also supports function definitions written in Python, Perl, Java, and R.

BIT and BIT VARYING data types (intentionally omitted). These were deprecated in SQL 2003, and replaced in SQL 2008.

WarehousePG supports identifiers up to 63 characters long. The SQL standard requires support for identifiers up to 128 characters long.

Prepared transactions (PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED). This also means WarehousePG does not support XA Transactions (2 phase commit coordination of database transactions with external transactions).

CHARACTER SET option on the definition of CHAR() or VARCHAR() columns.

Specification of CHARACTERS or OCTETS (BYTES) on the length of a CHAR() or VARCHAR() column. For example, VARCHAR(15 CHARACTERS) or VARCHAR(15 OCTETS) or VARCHAR(15 BYTES).
CREATE DISTINCT TYPE statement. CREATE DOMAIN can be used as a workaround in WarehousePG.

The explicit table construct.

SQL 2003 Conformance

The following features of SQL 2003 are not supported in WarehousePG:

2. `MERGE` statements.4. `IDENTITY` columns and the associated `GENERATED ALWAYS/GENERATED BY DEFAULT` clause. The `SERIAL` or `BIGSERIAL` data types are very similar to `INT` or `BIGINT GENERATED BY DEFAULT AS IDENTITY`.6. `MULTISET` modifiers on data types.8. `ROW` data type.10. WarehousePG syntax for using sequences is non-standard. For example, `nextval('seq')` is used in WarehousePG instead of the standard `NEXT VALUE FOR seq`. 11. `GENERATED ALWAYS AS` columns. Views can be used as a workaround. 12. The sample clause (`TABLESAMPLE`) on `SELECT` statements. The `random()` function can be used as a workaround to get random samples from tables.14. The *partitioned join tables* construct (`PARTITION BY` in a join).16. WarehousePG cluster data types are almost SQL standard compliant with some exceptions. Generally customers should not encounter any problems using them.

SQL 2008 Conformance

The following features of SQL 2008 are not supported in WarehousePG:

BINARY and VARBINARY data types. BYTEA can be used in place of VARBINARY in WarehousePG.

The ORDER BY clause is ignored in views and subqueries unless a LIMIT clause is also used. This is intentional, as the WarehousePG optimizer cannot determine when it is safe to avoid the sort, causing an unexpected performance impact for such ORDER BY clauses. To work around, you can specify a really large LIMIT. For example:
```
SELECT * FROM mytable ORDER BY 1 LIMIT 9999999999
```

The row subquery construct is not supported.

TRUNCATE TABLE does not accept the CONTINUE IDENTITY and RESTART IDENTITY clauses.

WarehousePG and PostgreSQL Compatibility

WarehousePG is based on PostgreSQL 9.4. To support the distributed nature and typical workload of a WarehousePG cluster, some SQL commands have been added or modified, and there are a few PostgreSQL features that are not supported. WarehousePG has also added features not found in PostgreSQL, such as physical data distribution, parallel query optimization, external tables, resource queues, and enhanced table partitioning. For full SQL syntax and references, see the SQL Commands.

Note WarehousePG does not support the PostgreSQL large object facility for streaming user data that is stored in large-object structures.

Note This syntax is deprecated and will be removed in a future WarehousePG release.

Table 1. SQL Support in WarehousePG
SQL Command	Supported in WarehousePG	Modifications, Limitations, Exceptions
`ALTER AGGREGATE`	YES
`ALTER CONVERSION`	YES
`ALTER DATABASE`	YES
`ALTER DOMAIN`	YES
`ALTER EVENT TRIGGER`	YES
`ALTER EXTENSION`	YES	Changes the definition of a WarehousePG extension - based on PostgreSQL 9.6.
`ALTER FUNCTION`	YES
`ALTER GROUP`	YES	An alias for ALTER ROLE
`ALTER INDEX`	YES
`ALTER LANGUAGE`	YES
`ALTER OPERATOR`	YES
`ALTER OPERATOR CLASS`	YES
`ALTER OPERATOR FAMILY`	YES
`ALTER PROTOCOL`	YES
`ALTER RESOURCE QUEUE`	YES	WarehousePG resource management feature - not in PostgreSQL.
`ALTER ROLE`	YES	WarehousePG Clauses: `RESOURCE QUEUE`queue_name `\| none`
`ALTER SCHEMA`	YES
`ALTER SEQUENCE`	YES
`ALTER SYSTEM`	NO
`ALTER TABLE`	YES	Unsupported Clauses / Options: `CLUSTER ON` `ENABLE/DISABLE TRIGGER` WarehousePG Database Clauses: `ADD \| DROP \| RENAME \| SPLIT \| EXCHANGE PARTITION \| SET SUBPARTITION TEMPLATE \| SET WITH(REORGANIZE=true \| false) \| SET DISTRIBUTED BY`
`ALTER TABLESPACE`	YES
`ALTER TRIGGER`	NO
`ALTER TYPE`	YES	WarehousePG Clauses: `SET DEFAULT ENCODING`
`ALTER USER`	YES	An alias for ALTER ROLE
`ALTER VIEW`	YES
`ANALYZE`	YES
`BEGIN`	YES
`CHECKPOINT`	YES
`CLOSE`	YES
`CLUSTER`	YES
`COMMENT`	YES
`COMMIT`	YES
`COMMIT PREPARED`	NO
`COPY`	YES	Modified Clauses: `ESCAPE [ AS ] '`escape`' \| 'OFF'` WarehousePG Clauses: `[LOG ERRORS] SEGMENT REJECT LIMIT`count `[ROWS\|PERCENT]`
`CREATE AGGREGATE`	YES	Unsupported Clauses / Options: `[ , SORTOP =`sort_operator `]` WarehousePG Clauses: `[ , COMBINEFUNC =` combinefunc`]` Limitations: The functions used to implement the aggregate must be `IMMUTABLE` functions.
`CREATE CAST`	YES
`CREATE CONSTRAINT TRIGGER`	NO
`CREATE CONVERSION`	YES
`CREATE DATABASE`	YES
`CREATE DOMAIN`	YES
`CREATE EVENT TRIGGER`	YES
`CREATE EXTENSION`	YES	Loads a new extension into WarehousePG - based on PostgreSQL 9.6.
`CREATE EXTERNAL TABLE`	YES	WarehousePG parallel ETL feature - not in PostgreSQL 9.4.
`CREATE FUNCTION`	YES	Limitations: Functions defined as`STABLE` or `VOLATILE` can be run in WarehousePG provided that they are run on the coordinator only.`STABLE` and `VOLATILE` functions cannot be used in statements that run at the segment level.
`CREATE GROUP`	YES	An alias for CREATE ROLE
`CREATE INDEX`	YES	WarehousePG Clauses: `USING bitmap` (bitmap indexes) Limitations: `UNIQUE` indexes are allowed only if they contain all of (or a superset of) the WarehousePG distribution key columns. On partitioned tables, a unique index is only supported within an individual partition - not across all partitions. `CONCURRENTLY` keyword not supported in WarehousePG.
`CREATE LANGUAGE`	YES
`CREATE MATERIALIZED VIEW`	YES	Based on PostgreSQL 9.4.
`CREATE OPERATOR`	YES	Limitations: The function used to implement the operator must be an `IMMUTABLE` function.
`CREATE OPERATOR CLASS`	YES
`CREATE OPERATOR FAMILY`	YES
`CREATE PROTOCOL`	YES
`CREATE RESOURCE QUEUE`	YES	WarehousePG resource management feature - not in PostgreSQL 9.4.
`CREATE ROLE`	YES	WarehousePG Clauses: `RESOURCE QUEUE`queue_name `\| none`
`CREATE RULE`	YES
`CREATE SCHEMA`	YES
`CREATE SEQUENCE`	YES	Limitations: The `lastval()` and`currval()` functions are not supported. The`setval()` function is only allowed in queries that do not operate on distributed data.
`CREATE TABLE`	YES	Unsupported Clauses / Options: `[GLOBAL \| LOCAL]` `REFERENCES` `FOREIGN KEY` `[DEFERRABLE \| NOT DEFERRABLE]` Limited Clauses: `UNIQUE` or`PRIMARY KEY` constraints are only allowed on hash-distributed tables (`DISTRIBUTED BY`), and the constraint columns must be the same as or a superset of the distribution key columns of the table and must include all the distribution key columns of the partitioning key. WarehousePG Clauses: `DISTRIBUTED BY (column, [ ... ] ) \|` `DISTRIBUTED RANDOMLY` `PARTITION BY type (column [, ...]) ( partition_specification, [...] )` `WITH (appendoptimized=true [,compresslevel=value,blocksize=value] )`
`CREATE TABLE AS`	YES	See CREATE TABLE
`CREATE TABLESPACE`	YES	WarehousePG Clauses: Specify host file system locations for specific segment instances. `WITH (contentID_1='/path/to/dir1...)`
`CREATE TRIGGER`	NO
`CREATE TYPE`	YES	WarehousePG Clauses: `COMPRESSTYPE \| COMPRESSLEVEL \| BLOCKSIZE` Limitations: The functions used to implement a new base type must be `IMMUTABLE`functions.
`CREATE USER`	YES	An alias for CREATE ROLE
`CREATE VIEW`	YES
`DEALLOCATE`	YES
`DECLARE`	YES	Unsupported Clauses / Options: `SCROLL` `FOR UPDATE [ OF column [, ...] ]` Limitations: Cursors cannot be backward-scrolled. Forward scrolling is supported. PL/pgSQL does not have support for updatable cursors.
`DELETE`	YES
`DISCARD`	YES	Limitation:`DISCARD ALL` is not supported.
`DO`	YES	PostgreSQL 9.0 feature
`DROP AGGREGATE`	YES
`DROP CAST`	YES
`DROP CONVERSION`	YES
`DROP DATABASE`	YES
`DROP DOMAIN`	YES
`DROP EVENT TRIGGER`	YES
`DROP EXTENSION`	YES	Removes an extension from WarehousePG – based on PostgreSQL 9.6.
`DROP EXTERNAL TABLE`	YES	WarehousePG parallel ETL feature - not in PostgreSQL 9.4.
`DROP FUNCTION`	YES
`DROP GROUP`	YES	An alias for DROP ROLE
`DROP INDEX`	YES
`DROP LANGUAGE`	YES
`DROP OPERATOR`	YES
`DROP OPERATOR CLASS`	YES
`DROP OPERATOR FAMILY`	YES
`DROP OWNED`	NO
`DROP PROTOCOL`	YES
`DROP RESOURCE QUEUE`	YES	WarehousePG resource management feature - not in PostgreSQL 9.4.
`DROP ROLE`	YES
`DROP RULE`	YES
`DROP SCHEMA`	YES
`DROP SEQUENCE`	YES
`DROP TABLE`	YES
`DROP TABLESPACE`	YES
`DROP TRIGGER`	NO
`DROP TYPE`	YES
`DROP USER`	YES	An alias for DROP ROLE
`DROP VIEW`	YES
`END`	YES
`EXECUTE`	YES
`EXPLAIN`	YES
`FETCH`	YES	Unsupported Clauses / Options: `LAST` `PRIOR` `BACKWARD` `BACKWARD ALL` Limitations: Cannot fetch rows in a nonsequential fashion; backward scan is not supported.
`GRANT`	YES
`INSERT`	YES
`LATERAL` Join Type	NO
`LISTEN`	YES
`LOAD`	YES
`LOCK`	YES
`MOVE`	YES	See FETCH
`NOTIFY`	YES
`PREPARE`	YES
`PREPARE TRANSACTION`	NO
`REASSIGN OWNED`	YES
`REFRESH MATERIALIZED VIEW`	YES	Based on PostgreSQL 9.4.
`REINDEX`	YES
`RELEASE SAVEPOINT`	YES
`RESET`	YES
`RETRIEVE`	YES	WarehousePG parallel retrieve cursor - not in PostgreSQL 9.4.
`REVOKE`	YES
`ROLLBACK`	YES
`ROLLBACK PREPARED`	NO
`ROLLBACK TO SAVEPOINT`	YES
`SAVEPOINT`	YES
`SELECT`	YES	Limitations: Limited use of `VOLATILE`and `STABLE` functions in `FROM` or`WHERE` clauses Text search (`Tsearch2`) is not supported WarehousePG Clauses (OLAP): `[GROUP BY` grouping_element `[, ...]]` `[WINDOW` window_name `AS (`window_specification`)]` `[FILTER (WHERE` condition`)]` applied to an aggregate function in the `SELECT` list
`SELECT INTO`	YES	See SELECT
`SET`	YES
`SET CONSTRAINTS`	NO	In PostgreSQL, this only applies to foreign key constraints, which are currently not enforced in WarehousePG.
`SET ROLE`	YES
`SET SESSION AUTHORIZATION`	YES	Deprecated as of PostgreSQL 8.1 - see SET ROLE
`SET TRANSACTION`	YES	Limitations: `DEFERRABLE` clause has no effect. `SET TRANSACTION SNAPSHOT` command is not supported.
`SHOW`	YES
`START TRANSACTION`	YES
`TRUNCATE`	YES
`UNLISTEN`	YES
`UPDATE`	YES	Limitations: `SET` not allowed for WarehousePG distribution key columns.
`VACUUM`	YES	Limitations: `VACUUM FULL` is not recommended in WarehousePG.
`VALUES`	YES

On this page
WarehousePG SQL Standard Conformance
WarehousePG and PostgreSQL Compatibility

← Prev

SQL Features, Reserved and Key Words, and Compliance

↑ Up