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