Summary of Greenplum Features
This section provides a high-level overview of the system requirements and feature set of Greenplum Database. It contains the following topics:
Greenplum 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. Greenplum Database 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 Greenplum Database as they relate to the SQL standards. For a feature-by-feature list of Greenplum'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 Greenplum Database. The following SQL constructs are not supported:
- Some set returning subqueries in EXISTS or NOT EXISTS clauses that Greenplum's parallel optimizer cannot rewrite into joins.
- UNION ALL of joined tables with subqueries.
- Set-returning functions in the FROM clause of a subquery.
- Backwards scrolling cursors, including the use of FETCH PRIOR, FETCH FIRST, FETCH ABOLUTE, 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 execute on the segments, and so are generally limited to being passed literal values as the arguments to their parameters.
- Triggers are not supported since they typically rely on the use of VOLATILE functions.
- Referential integrity constraints (foreign keys) are not enforced in Greenplum Database. Users can declare foreign keys and this information is kept in the system catalog, however.
- Sequence manipulation functions CURRVAL and LASTVAL.
- DELETE WHERE CURRENT OF and UPDATE WHERE CURRENT OF (positioned delete and positioned update operations).
SQL 1992 Conformance
The following features of SQL 1992 are not supported in Greenplum Database:
- 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 Greenplum Database.
- CREATE ASSERTION statement.
- INTERVAL literals are supported in Greenplum Database, but do not conform to the standard.
- GET DIAGNOSTICS statement.
- GRANT INSERT or UPDATE privileges on columns. Privileges can only be granted on tables in Greenplum Database.
- GLOBAL TEMPORARY TABLEs and LOCAL TEMPORARY TABLEs. Greenplum TEMPORARY TABLEs do not conform to the SQL standard, but many commercial database systems have implemented temporary tables in the same way. Greenplum temporary tables are the same as VOLATILE TABLEs in Teradata.
- UNIQUE predicate.
- MATCH PARTIAL for referential integrity checks (most likely will not be implemented in Greenplum Database).
SQL 1999 Conformance
The following features of SQL 1999 are not supported in Greenplum Database:
- Large Object data types: BLOB, CLOB, NCLOB. However, the BYTEA and TEXT columns can store very large amounts of data in Greenplum Database (hundreds of megabytes).
- MODULE (SQL client modules).
-
CREATE PROCEDURE (SQL/PSM). This can
be worked around in Greenplum Database by creating a FUNCTION
that returns void, and invoking the function as follows:
SELECT myfunc(args);
- The PostgreSQL/Greenplum function definition language (PL/PGSQL) is a subset of Oracle's PL/SQL, rather than being compatible with the SQL/PSM function definition language. Greenplum Database 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.
- Greenplum 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 Greenplum 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).
- CURRENT_SCHEMA function.
- CREATE DISTINCT TYPE statement. CREATE DOMAIN can be used as a work-around in Greenplum.
- The explicit table construct.
SQL 2003 Conformance
The following features of SQL 2003 are not supported in Greenplum Database:
- MERGE statements.
- 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.
- MULTISET modifiers on data types.
- ROW data type.
- Greenplum Database syntax for using sequences is non-standard. For example, nextval('seq') is used in Greenplum instead of the standard NEXT VALUE FOR seq.
- GENERATED ALWAYS AS columns. Views can be used as a work-around.
- The sample clause (TABLESAMPLE) on SELECT statements. The random() function can be used as a work-around to get random samples from tables.
- NULLS FIRST/NULLS LAST clause on SELECT statements and subqueries (nulls are always last in Greenplum Database).
- The partitioned join tables construct (PARTITION BY in a join).
- GRANT SELECT privileges on columns. Privileges can only be granted on tables in Greenplum Database. Views can be used as a work-around.
- For CREATE TABLE x (LIKE(y)) statements, Greenplum does not support the [INCLUDING|EXCLUDING][DEFAULTS|CONSTRAINTS|INDEXES] clauses.
- Greenplum array 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 Greenplum Database:
- BINARY and VARBINARY data types. BYTEA can be used in place of VARBINARY in Greenplum Database.
-
FETCH FIRST or FETCH NEXT clause for
SELECT, for
example:
SELECT id, name FROM tab1 ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Greenplum has LIMIT and LIMIT OFFSET clauses instead.
- The ORDER BY clause is ignored in views and subqueries unless a LIMIT clause is also used. This is intentional, as the Greenplum 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.
Greenplum and PostgreSQL Compatibility
Greenplum Database is based on PostgreSQL 8.2 with a few features added in from the 8.3 release. To support the distributed nature and typical workload of a Greenplum Database system, some SQL commands have been added or modified, and there are a few PostgreSQL features that are not supported. Greenplum has also added features not found in PostgreSQL, such as physical data distribution, parallel query optimization, external tables, resource queues for workload management and enhanced table partitioning. For full SQL syntax and references, see the SQL Command Reference.
SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
---|---|---|
ALTER AGGREGATE | YES | |
ALTER CONVERSION | YES | |
ALTER DATABASE | YES | |
ALTER DOMAIN | YES | |
ALTER FILESPACE | YES | Greenplum Database parallel tablespace feature - not in PostgreSQL 8.2.15. |
ALTER FUNCTION | YES | |
ALTER GROUP | YES | An alias for ALTER ROLE |
ALTER INDEX | YES | |
ALTER LANGUAGE | YES | |
ALTER OPERATOR | YES | |
ALTER OPERATOR CLASS | NO | |
ALTER RESOURCE QUEUE | YES | Greenplum Database workload management feature - not in PostgreSQL. |
ALTER ROLE | YES |
Greenplum Database Clauses: RESOURCE QUEUE queue_name | none |
ALTER SCHEMA | YES | |
ALTER SEQUENCE | YES | |
ALTER TABLE | YES |
Unsupported Clauses / Options: CLUSTER ON ENABLE/DISABLE TRIGGER Greenplum 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 | |
ALTER USER | YES | An alias for ALTER ROLE |
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' Greenplum Database Clauses: [LOG ERRORS INTO error_table] SEGMENT REJECT LIMIT count [ROWS|PERCENT] |
CREATE AGGREGATE | YES |
Unsupported Clauses / Options: [ , SORTOP = sort_operator ] Greenplum Database Clauses: [ , PREFUNC = prefunc ] 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 EXTERNAL TABLE | YES | Greenplum Database parallel ETL feature - not in PostgreSQL 8.2.15. |
CREATE FUNCTION | YES |
Limitations: Functions defined as STABLE or VOLATILE can be executed in Greenplum Database provided that they are executed on the master only. STABLE and VOLATILE functions cannot be used in statements that execute at the segment level. |
CREATE GROUP | YES | An alias for CREATE ROLE |
CREATE INDEX | YES |
Greenplum Database Clauses: USING bitmap (bitmap indexes) Limitations: UNIQUE indexes are allowed only if they contain all of (or a superset of) the Greenplum 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 Greenplum. |
CREATE LANGUAGE | YES | |
CREATE OPERATOR | YES |
Limitations: The function used to implement the operator must be an IMMUTABLE function. |
CREATE OPERATOR CLASS | NO | |
CREATE OPERATOR FAMILY | NO | |
CREATE RESOURCE QUEUE | YES | Greenplum Database workload management feature - not in PostgreSQL 8.2.15. |
CREATE ROLE | YES |
Greenplum Database 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. Greenplum Database Clauses: DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY PARTITION BY type (column [, ...]) ( partition_specification, [...] ) WITH (appendonly=true [,compresslevel=value,blocksize=value] ) |
CREATE TABLE AS | YES | See CREATE TABLE |
CREATE TABLESPACE | NO |
Greenplum Database Clauses: FILESPACE filespace_name |
CREATE TRIGGER | NO | |
CREATE TYPE | YES |
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 are non-updatable, and cannot be backward-scrolled. Forward scrolling is supported. |
DELETE | YES |
Unsupported Clauses / Options: RETURNING |
DROP AGGREGATE | YES | |
DROP CAST | YES | |
DROP CONVERSION | YES | |
DROP DATABASE | YES | |
DROP DOMAIN | YES | |
DROP EXTERNAL TABLE | YES | Greenplum Database parallel ETL feature - not in PostgreSQL 8.2.15. |
DROP FILESPACE | YES | Greenplum Database parallel tablespace feature - not in PostgreSQL 8.2.15. |
DROP FUNCTION | YES | |
DROP GROUP | YES | An alias for DROP ROLE |
DROP INDEX | YES | |
DROP LANGUAGE | YES | |
DROP OPERATOR | YES | |
DROP OPERATOR CLASS | NO | |
DROP OWNED | NO | |
DROP RESOURCE QUEUE | YES | Greenplum Database workload management feature - not in PostgreSQL 8.2.15. |
DROP ROLE | YES | |
DROP RULE | YES | |
DROP SCHEMA | YES | |
DROP SEQUENCE | YES | |
DROP TABLE | YES | |
DROP TABLESPACE | NO | |
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 |
Unsupported Clauses / Options: RETURNING |
LISTEN | NO | |
LOAD | YES | |
LOCK | YES | |
MOVE | YES | See FETCH |
NOTIFY | NO | |
PREPARE | YES | |
PREPARE TRANSACTION | NO | |
REASSIGN OWNED | YES | |
REINDEX | YES | |
RELEASE SAVEPOINT | YES | |
RESET | YES | |
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 FETCH FIRST or FETCH NEXT clauses not supported Greenplum Database 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 Greenplum Database. |
SET ROLE | YES | |
SET SESSION AUTHORIZATION | YES | Deprecated as of PostgreSQL 8.1 - see SET ROLE |
SET TRANSACTION | YES | |
SHOW | YES | |
START TRANSACTION | YES | |
TRUNCATE | YES | |
UNLISTEN | NO | |
UPDATE | YES |
Unsupported Clauses: RETURNING Limitations: SET not allowed for Greenplum distribution key columns. |
VACUUM | YES |
Limitations: VACUUM FULL is not recommended in Greenplum Database. |
VALUES | YES |