SQL Conformance
Appendix C. SQL Conformance
- Table of Contents
- C.1. Supported Features
- C.2. Unsupported Features
This section attempts to outline to what extent PostgreSQL conforms to the SQL standard. Full compliance to the standard or a complete statement about the compliance to the standard is complicated and not particularly useful, so this section can only give an overview.
The formal name of the SQL standard is ISO/IEC 9075 "Database Language SQL". A revised version of the standard is released from time to time; the most recent one appearing in 1999. That version is refered to as ISO/IEC 9075:1999, or informally as SQL99. The version prior to that was SQL92. PostgreSQL development tends to aim for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. At the time of this writing, ballotting is under way for a new revision of the standard, which, if approved, will eventually become the conformance target for future PostgreSQL development.
SQL92 defined three feature sets for conformance: Entry, Intermediate, and Full. Most database products claiming SQL standard conformance were conforming at only the Entry level, since the entire set of features in the Intermediate and Full levels was either too voluminous or in conflict with legacy behaviors.
SQL99 defines a large set of individual features rather than the ineffectively broad three levels found in SQL92. A large subset of these features represents the "core" features, which every conforming SQL implementation must supply. The rest of the features are purely optional. Some optional features are grouped together to form "packages", which SQL implementations can claim conformance to, thus claiming conformance to particular groups of features.
The SQL99 standard is also split into 5 parts: Framework, Foundation, Call Level Interface, Persistent Stored Modules, and Host Language Bindings. PostgreSQL only covers parts 1, 2, and 5. Part 3 is similar to the ODBC interface, and part 4 is similar to the PL/pgSQL programming language, but exact conformance is not specifically intended in either case.
In the following two sections, we provide a list of those features that PostgreSQL supports, followed by a list of the features defined in SQL99 which are not yet supported in PostgreSQL. Both of these lists are approximate: There may be minor details that are nonconforming for a feature that is listed as supported, and large parts of an unsupported feature may in fact be implemented. The main body of the documentation always contains the most accurate information about what does and does not work.
Note: Feature codes containing a hyphen are subfeatures. Therefore, if a particular subfeature is not supported, the main feature is listed as unsupported even if some other subfeatures are supported.
C.1. Supported Features
| Identifier | Package | Description | Comment |
|---|---|---|---|
| B012 | Core | Embedded C | |
| B021 | Direct SQL | ||
| E011 | Core | Numeric data types | |
| E011-01 | Core | INTEGER and SMALLINT data types | |
| E011-02 | Core | REAL, DOUBLE PRECISON, and FLOAT data types | |
| E011-03 | Core | DECIMAL and NUMERIC data types | |
| E011-04 | Core | Arithmetic operators | |
| E011-05 | Core | Numeric comparison | |
| E011-06 | Core | Implicit casting among the numeric data types | |
| E021 | Core | Character data types | |
| E021-01 | Core | CHARACTER data type | |
| E021-02 | Core | CHARACTER VARYING data type | |
| E021-03 | Core | Character literals | |
| E021-04 | Core | CHARACTER_LENGTH function | |
| E021-05 | Core | OCTET_LENGTH function | |
| E021-06 | Core | SUBSTRING function | |
| E021-07 | Core | Character concatenation | |
| E021-08 | Core | UPPER and LOWER functions | |
| E021-09 | Core | TRIM function | |
| E021-10 | Core | Implicit casting among the character data types | |
| E021-11 | Core | POSITION function | |
| E011-12 | Core | Character comparison | |
| E031 | Core | Identifiers | |
| E031-01 | Core | Delimited identifiers | |
| E031-02 | Core | Lower case identifiers | |
| E031-03 | Core | Trailing underscore | |
| E051 | Core | Basic query specification | |
| E051-01 | Core | SELECT DISTINCT | |
| E051-02 | Core | GROUP BY clause | |
| E051-04 | Core | GROUP BY can contain columns not in <select list> | |
| E051-05 | Core | Select list items can be renamed | AS is required |
| E051-06 | Core | HAVING clause | |
| E051-07 | Core | Qualified * in select list | |
| E051-08 | Core | Correlation names in the FROM clause | |
| E051-09 | Core | Rename columns in the FROM clause | |
| E061 | Core | Basic predicates and search conditions | |
| E061-01 | Core | Comparison predicate | |
| E061-02 | Core | BETWEEN predicate | |
| E061-03 | Core | IN predicate with list of values | |
| E061-04 | Core | LIKE predicate | |
| E061-05 | Core | LIKE predicate ESCAPE clause | |
| E061-06 | Core | NULL predicate | |
| E061-07 | Core | Quantified comparison predicate | |
| E061-08 | Core | EXISTS predicate | |
| E061-09 | Core | Subqueries in comparison predicate | |
| E061-11 | Core | Subqueries in IN predicate | |
| E061-12 | Core | Subqueries in quantified comparison predicate | |
| E061-13 | Core | Correlated subqueries | |
| E061-14 | Core | Search condition | |
| E071 | Core | Basic query expressions | |
| E071-01 | Core | UNION DISTINCT table operator | |
| E071-02 | Core | UNION ALL table operator | |
| E071-03 | Core | EXCEPT DISTINCT table operator | |
| E071-05 | Core | Columns combined via table operators need not have exactly the same data type | |
| E071-06 | Core | Table operators in subqueries | |
| E081-01 | Core | SELECT privilege | |
| E081-02 | Core | DELETE privilege | |
| E081-03 | Core | INSERT privilege at the table level | |
| E081-04 | Core | UPDATE privilege at the table level | |
| E081-06 | Core | REFERENCES privilege at the table level | |
| E091 | Core | Set functions | |
| E091-01 | Core | AVG | |
| E091-02 | Core | COUNT | |
| E091-03 | Core | MAX | |
| E091-04 | Core | MIN | |
| E091-05 | Core | SUM | |
| E091-06 | Core | ALL quantifier | |
| E091-07 | Core | DISTINCT quantifier | |
| E101 | Core | Basic data manipulation | |
| E101-01 | Core | INSERT statement | |
| E101-03 | Core | Searched UPDATE statement | |
| E101-04 | Core | Searched DELETE statement | |
| E111 | Core | Single row SELECT statement | |
| E121-01 | Core | DECLARE CURSOR | |
| E121-02 | Core | ORDER BY columns need not be in select list | |
| E121-03 | Core | Value expressions in ORDER BY clause | |
| E121-08 | Core | CLOSE statement | (cursor) |
| E121-10 | Core | FETCH statement implicit NEXT | |
| E131 | Core | Null value support (nulls in lieu of values) | |
| E141 | Core | Basic integrity constraints | |
| E141-01 | Core | NOT NULL constraints | |
| E141-02 | Core | UNIQUE constraints of NOT NULL columns | |
| E141-03 | Core | PRIMARY KEY constraints | |
| E141-04 | Core | Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action | |
| E141-06 | Core | CHECK constraints | |
| E141-07 | Core | Column defaults | |
| E141-08 | Core | NOT NULL inferred on PRIMARY KEY | |
| E141-10 | Core | Names in a foreign key can be specified in any order | |
| E151 | Core | Transaction support | |
| E151-01 | Core | COMMIT statement | |
| E151-02 | Core | ROLLBACK statement | |
| E152-01 | Core | SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause | |
| E161 | Core | SQL comments using leading double minus | |
| F031 | Core | Basic schema manipulation | |
| F031-01 | Core | CREATE TABLE statement to create persistent base tables | |
| F031-02 | Core | CREATE VIEW statement | |
| F031-03 | Core | GRANT statement | |
| F031-04 | Core | ALTER TABLE statement: ADD COLUMN clause | |
| F031-13 | Core | DROP TABLE statement: RESTRICT clause | |
| F031-16 | Core | DROP VIEW statement: RESTRICT clause | |
| F032 | CASCADE drop behavior | ||
| F033 | ALTER TABLE statement: DROP COLUMN clause | ||
| F041 | Core | Basic joined table | |
| F041-01 | Core | Inner join (but not necessarily the INNER keyword) | |
| F041-02 | Core | INNER keyword | |
| F041-03 | Core | LEFT OUTER JOIN | |
| F041-04 | Core | RIGHT OUTER JOIN | |
| F041-05 | Core | Outer joins can be nested | |
| F041-07 | Core | The inner table in a left or right outer join can also be used in an inner join | |
| F041-08 | Core | All comparison operators are supported (rather than just =) | |
| F051 | Core | Basic date and time | |
| F051-01 | Core | DATE data type (including support of DATE literal) | |
| F051-02 | Core | TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 | |
| F051-03 | Core | TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 | |
| F051-04 | Core | Comparison predicate on DATE, TIME, and TIMESTAMP data types | |
| F051-05 | Core | Explicit CAST between datetime types and character types | |
| F051-06 | Core | CURRENT_DATE | |
| F051-07 | Core | LOCALTIME | |
| F051-08 | Core | LOCALTIMESTAMP | |
| F052 | Enhanced datetime facilities | Intervals and datetime arithmetic | |
| F081 | Core | UNION and EXCEPT in views | |
| F111-02 | READ COMMITTED isolation level | ||
| F131 | Core | Grouped operations | |
| F131-01 | Core | WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views | |
| F131-02 | Core | Multiple tables supported in queries with grouped views | |
| F131-03 | Core | Set functions supported in queries with grouped views | |
| F131-04 | Core | Subqueries with GROUP BY and HAVING clauses and grouped views | |
| F131-05 | Core | Single row SELECT with GROUP BY and HAVING clauses and grouped views | |
| F171 | Multiple schemas per user | ||
| F191 | Enhanced integrity management | Referential delete actions | |
| F201 | Core | CAST function | |
| F221 | Core | Explicit defaults | |
| F222 | INSERT statement: DEFAULT VALUES clause | ||
| F251 | Domain support | ||
| F261 | Core | CASE expression | |
| F261-01 | Core | Simple CASE | |
| F261-02 | Core | Searched CASE | |
| F261-03 | Core | NULLIF | |
| F261-04 | Core | COALESCE | |
| F271 | Compound character literals | ||
| F281 | LIKE enhancements | ||
| F302 | OLAP facilities | INTERSECT table operator | |
| F302-01 | OLAP facilities | INTERSECT DISTINCT table operator | |
| F302-02 | OLAP facilities | INTERSECT ALL table operator | |
| F304 | OLAP facilities | EXCEPT ALL table operator | |
| F311 | Core | Schema definition statement | |
| F311-01 | Core | CREATE SCHEMA | |
| F311-02 | Core | CREATE TABLE for persistent base tables | |
| F311-03 | Core | CREATE VIEW | |
| F311-05 | Core | GRANT statement | |
| F321 | User authorization | ||
| F361 | Subprogram support | ||
| F381 | Extended schema manipulation | ||
| F381-01 | ALTER TABLE statement: ALTER COLUMN clause | ||
| F381-02 | ALTER TABLE statement: ADD CONSTRAINT clause | ||
| F381-03 | ALTER TABLE statement: DROP CONSTRAINT clause | ||
| F391 | Long identifiers | ||
| F401 | OLAP facilities | Extended joined table | |
| F401-01 | OLAP facilities | NATURAL JOIN | |
| F401-02 | OLAP facilities | FULL OUTER JOIN | |
| F401-03 | OLAP facilities | UNION JOIN | |
| F401-04 | OLAP facilities | CROSS JOIN | |
| F411 | Enhanced datetime facilities | Time zone specification | |
| F421 | National character | ||
| F431-01 | FETCH with explicit NEXT | ||
| F431-04 | FETCH PRIOR | ||
| F431-06 | FETCH RELATIVE | ||
| F441 | Extended set function support | ||
| F471 | Core | Scalar subquery values | |
| F481 | Core | Expanded NULL predicate | |
| F491 | Enhanced integrity management | Constraint management | |
| F511 | BIT data type | ||
| F531 | Temporary tables | ||
| F555 | Enhanced datetime facilities | Enhanced seconds precision | |
| F561 | Full value expressions | ||
| F571 | Truth value tests | ||
| F591 | OLAP facilities | Derived tables | |
| F611 | Indicator data types | ||
| F651 | Catalog name qualifiers | ||
| F701 | Enhanced integrity management | Referential update actions | |
| F761 | Session management | ||
| F791 | Insensitive cursors | ||
| F801 | Full set function | ||
| S071 | Enhanced object support | SQL paths in function and type name resolution | |
| S111 | Enhanced object support | ONLY in query expressions | |
| S211 | Enhanced object support, SQL/MM support | User-defined cast functions | |
| T031 | BOOLEAN data type | ||
| T141 | SIMILAR predicate | ||
| T151 | DISTINCT predicate | ||
| T191 | Enhanced integrity management | Referential action RESTRICT | |
| T201 | Enhanced integrity management | Comparable data types for referential constraints | |
| T211-01 | Enhanced integrity management | Triggers activated on UPDATE, INSERT, or DELETE of one base table | |
| T211-02 | Enhanced integrity management | BEFORE triggers | |
| T211-03 | Enhanced integrity management | AFTER triggers | |
| T211-04 | Enhanced integrity management | FOR EACH ROW triggers | |
| T211-07 | Enhanced integrity management | TRIGGER privilege | |
| T231 | SENSITIVE cursors | ||
| T241 | START TRANSACTION statement | ||
| T312 | OVERLAY function | ||
| T321-01 | Core | User-defined functions with no overloading | |
| T321-03 | Core | Function invocation | |
| T322 | PSM, SQL/MM support | Overloading of SQL-invoked functions and procedures | |
| T323 | Explicit security for external routines | ||
| T351 | Bracketed SQL comments (/*...*/ comments) | ||
| T441 | ABS and MOD functions | ||
| T501 | Enhanced EXISTS predicate | ||
| T551 | Optional key words for default syntax | ||
| T581 | Regular expression substring function | ||
| T591 | UNIQUE constraints of possibly null columns |