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. 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. 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 The following features defined in SQL99 are not implemented in the current release of PostgreSQL. In a few cases, equivalent functionality is available. Identifier Package Description Comment B011 Core Embedded Ada B013 Core Embedded COBOL B014 Core Embedded Fortran B015 Core Embedded MUMPS B016 Core Embedded Pascal B017 Core Embedded PL/I B031 Basic dynamic SQL B032 Extended dynamic SQL B032-1 <describe input> statement B041 Extensions to embedded SQL exception declarations B051 Enhanced execution rights E081 Core Basic Privileges E081-05 Core UPDATE privilege at the column level E081-07 Core REFERENCES privilege at the column level E081-08 Core WITH GRANT OPTION E121 Core Basic cursor support E121-04 Core OPEN statement (cursor) E121-06 Core Positioned UPDATE statement (cursor) E121-07 Core Positioned DELETE statement (cursor) E121-17 Core WITH HOLD cursors Cursor to stay open across transactions E152 Core Basic SET TRANSACTION statement E152-02 Core SET TRANSACTION statement: READ ONLY and READ WRITE clauses Syntax accepted; READ ONLY not supported E153 Core Updatable queries with subqueries E171 Core SQLSTATE support F181 Multiple module support E182 Core Module language F021 Core Basic information schema F021-01 Core COLUMNS view F021-02 Core TABLES view F021-03 Core VIEWS view F021-04 Core TABLE_CONSTRAINTS view F021-05 Core REFERENTIAL_CONSTRAINTS view F021-06 Core CHECK_CONSTRAINTS view F031-19 Core REVOKE statement: RESTRICT clause F034 Extended REVOKE statement F034-01 REVOKE statement performed by other than the owner of a schema object F034-02 REVOKE statement: GRANT OPTION FOR clause F034-03 REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION F111 Isolation levels other than SERIALIZABLE F111-01 READ UNCOMMITTED isolation level F111-03 REPEATABLE READ isolation level F121 Basic diagnostics management F121-01 GET DIAGNOSTICS statement F121-02 SET TRANSACTION statement: DIAGNOSTICS SIZE clause F231 Privilege Tables F231-01 TABLE_PRIVILEGES view F231-02 COLUMN_PRIVILEGES view F231-03 USAGE_PRIVILEGES view F291 UNIQUE predicate F301 CORRESPONDING in query expressions F311-04 Core CREATE VIEW: WITH CHECK OPTION F341 Usage tables F431 Read-only scrollable cursors F431-02 FETCH FIRST F431-03 FETCH LAST F431-05 FETCH ABSOLUTE F451 Character set definition F461 Named character sets F501 Core Features and conformance views F501-01 Core SQL_FEATURES view F501-02 Core SQL_SIZING view F501-03 Core SQL_LANGUAGES view F502 Enhanced documentation tables F502-01 SQL_SIZING_PROFILES view F502-02 SQL_IMPLEMENTATION_INFO view F502-03 SQL_PACKAGES view F521 Enhanced integrity management Assertions F641 OLAP facilities Row and table constructors F661 Simple tables F671 Enhanced integrity management Subqueries in CHECK intentionally omitted F691 Collation and translation F711 ALTER domain F721 Deferrable constraints foreign keys only F731 INSERT column privileges F741 Referential MATCH types no partial match yet F751 View CHECK enhancements F771 Connection management F781 Self-referencing operations F811 Extended flagging F812 Core Basic flagging F813 Extended flagging for "Core SQL Flagging" and "Catalog Lookup" only F821 Local table references F831 Full cursor update F831-01 Updatable scrollable cursors F831-02 Updatable ordered cursors S011 Core Distinct data types S011-01 Core USER_DEFINED_TYPES view S023 Basic object support, SQL/MM support Basic structured types S024, SQL/MM support Enhanced object support Enhanced structured types S041 Basic object support Basic reference types S043 Enhanced object support Enhanced reference types S051 Basic object support Create table of type S081 Enhanced object support Subtables S091 SQL/MM support Basic array support PostgreSQL arrays are different S091-01 SQL/MM support Arrays of built-in data types S091-02 SQL/MM support Arrays of distinct types S091-03 SQL/MM support Array expressions S092 SQL/MM support Arrays of user-defined types S094 Arrays of reference types S151 Basic object support Type predicate IS OF S161 Enhanced object support Subtype treatment TREAT(expr AS type) S201 SQL routines on arrays S201-01 Array parameters S201-02 Array as result type of functions S231 Enhanced object support Structured type locators S232 Array locators S241 Enhanced object support Transform functions S251 User-defined orderings CREATE ORDERING FOR S261 Specific type method T011 Timestamp in Information Schema T041 Basic object support Basic LOB data type support T041-01 Basic object support BLOB data type T041-02 Basic object support CLOB data type T041-03 Basic object support POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types T041-04 Basic object support Concatenation of LOB data types T041-05 Basic object support LOB locator: non-holdable T042 Extended LOB data type support T051 Row types T111 Updatable joins, unions, and columns T121 WITH (excluding RECURSIVE) in query expression T131 Recursive query T171 LIKE clause in table definition CREATE TABLE T1 (LIKE T2) T211 Enhanced integrity management, Active database Basic trigger capability T211-05 Enhanced integrity management Ability to specify a search condition that must be true before the trigger is invoked T211-06 Enhanced integrity management Support for run-time rules for the interaction of triggers and constraints T211-08 Enhanced integrity management Multiple triggers for the same the event are executed in the order in which they were created T212 Enhanced integrity management Enhanced trigger capability T251 SET TRANSACTION statement: LOCAL option T261 Chained transactions T271 Savepoints T281 SELECT privilege with column granularity T301 Functional Dependencies T321 Core Basic SQL-invoked routines T321-02 Core User-defined stored procedures with no overloading T321-04 Core CALL statement T321-05 Core RETURN statement T321-06 Core ROUTINES view T321-07 Core PARAMETERS view T331 Basic roles T332 Extended roles T401 INSERT into a cursor T411 UPDATE statement: SET ROW option T431 OLAP facilities CUBE and ROLLUP operations T461 Symmetric BETWEEN predicate T471 Result sets return value T491 LATERAL derived table T511 Transaction counts T541 Updatable table references T561 Holdable locators T571 Array-returning external SQL-invoked functions T601 Local cursor references