Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Appendix D. SQL Conformance

Table of Contents
D.1. Supported Features
D.2. Unsupported Features

This section attempts to outline to what extent PostgreSQL conforms to the current SQL standard. The following information is not a full statement of conformance, but it presents the main topics in as much detail as is both reasonable and useful for users.

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 late 2003. That version is referred to as ISO/IEC 9075:2003, or simply as SQL:2003. The versions prior to that were SQL:1999 and SQL-92. Each version replaces the previous one, so claims of conformance to earlier versions have no official merit. PostgreSQL development aims for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. The PostgreSQL project was not represented in the ISO/IEC 9075 Working Group during the preparation of SQL:2003. Even so, many of the features required by SQL:2003 are already supported, though sometimes with slightly differing syntax or function. Further moves towards conformance may be expected in later releases.

SQL-92 defined three feature sets for conformance: Entry, Intermediate, and Full. Most database management systems 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.

Starting with SQL:1999, the SQL standard defines a large set of individual features rather than the ineffectively broad three levels found in SQL-92. 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 SQL:2003 standard is also split into a number of parts. Each is known by a shorthand name. Note that these parts are not consecutively numbered.

  • ISO/IEC 9075-1 Framework (SQL/Framework)

  • ISO/IEC 9075-2 Foundation (SQL/Foundation)

  • ISO/IEC 9075-3 Call Level Interface (SQL/CLI)

  • ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)

  • ISO/IEC 9075-9 Management of External Data (SQL/MED)

  • ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)

  • ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)

  • ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)

  • ISO/IEC 9075-14 XML-related specifications (SQL/XML)

PostgreSQL covers parts 1, 2, and 11. 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 or verified in either case.

PostgreSQL supports most of the major features of SQL:2003. Out of 164 mandatory features required for full Core conformance, PostgreSQL conforms to at least 150. In addition, there is a long list of supported optional features. It may be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2003.

In the following two sections, we provide a list of those features that PostgreSQL supports, followed by a list of the features defined in SQL:2003 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.

D.1. Supported Features

Identifier Package Description Comment
B012   Embedded C  
B021   Direct SQL  
E011 Core Numeric data types  
E011-01 Core INTEGER and SMALLINT data types  
E011-02 Core REAL, DOUBLE PRECISION, 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 trims trailing spaces from CHARACTER values before counting
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 string types  
E021-11 Core POSITION function  
E021-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  
E081-08 Core WITH GRANT OPTION  
E081-10 Core EXECUTE privilege  
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-04 Core OPEN statement  
E121-08 Core CLOSE statement  
E121-10 Core FETCH statement implicit NEXT  
E121-17 Core WITH HOLD cursors  
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 Core Basic SET TRANSACTION statement  
E152-01 Core SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause  
E152-02 Core SET TRANSACTION statement: READ ONLY and READ WRITE clauses  
E161 Core SQL comments using leading double minus  
E171 Core SQLSTATE support  
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 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  
F031-19 Core REVOKE statement: RESTRICT clause  
F032   CASCADE drop behavior  
F033   ALTER TABLE statement: DROP COLUMN 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  
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 string types  
F051-06 Core CURRENT_DATE  
F051-07 Core LOCALTIME  
F051-08 Core LOCALTIMESTAMP  
F052 Enhanced datetime facilities Intervals and datetime arithmetic  
F053   OVERLAPS predicate  
F081 Core UNION and EXCEPT in views  
F111   Isolation levels other than SERIALIZABLE  
F111-01   READ UNCOMMITTED isolation level  
F111-02   READ COMMITTED isolation level  
F111-03   REPEATABLE READ 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  
F231   Privilege tables  
F231-01   TABLE_PRIVILEGES view  
F231-02   COLUMN_PRIVILEGES view  
F231-03   USAGE_PRIVILEGES view  
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   INTERSECT table operator  
F302-01   INTERSECT DISTINCT table operator  
F302-02   INTERSECT ALL table operator  
F304   EXCEPT ALL table operator  
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   Extended joined table  
F401-01   NATURAL JOIN  
F401-02   FULL OUTER JOIN  
F401-04   CROSS JOIN  
F411 Enhanced datetime facilities Time zone specification differences regarding literal interpretation
F421   National character  
F431   Read-only scrollable cursors  
F431-01   FETCH with explicit NEXT  
F431-02   FETCH FIRST  
F431-03   FETCH LAST  
F431-04   FETCH PRIOR  
F431-05   FETCH ABSOLUTE  
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  
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  
F531   Temporary tables  
F555 Enhanced datetime facilities Enhanced seconds precision  
F561   Full value expressions  
F571   Truth value tests  
F591   Derived tables  
F611   Indicator data types  
F651   Catalog name qualifiers  
F672   Retrospective check constraints  
F701 Enhanced integrity management Referential update actions  
F711   ALTER domain  
F761   Session management  
F771   Connection management  
F781   Self-referencing operations  
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 User-defined cast functions  
T031   BOOLEAN data type  
T071   BIGINT data type  
T141   SIMILAR predicate  
T151   DISTINCT predicate  
T171   LIKE clause in table definition  
T191 Enhanced integrity management Referential action RESTRICT  
T201 Enhanced integrity management Comparable data types for referential constraints  
T211-01 Active database, Enhanced integrity management Triggers activated on UPDATE, INSERT, or DELETE of one base table  
T211-02 Active database, Enhanced integrity management BEFORE triggers  
T211-03 Active database, Enhanced integrity management AFTER triggers  
T211-04 Active database, Enhanced integrity management FOR EACH ROW triggers  
T211-07 Active database, Enhanced integrity management TRIGGER privilege  
T212 Enhanced integrity management Enhanced trigger capability  
T231   Sensitive cursors  
T241   START TRANSACTION statement  
T271   Savepoints  
T312   OVERLAY function  
T321-01 Core User-defined functions with no overloading  
T321-03 Core Function invocation  
T321-06 Core ROUTINES view  
T321-07 Core PARAMETERS view  
T322 PSM Overloading of SQL-invoked functions and procedures  
T323   Explicit security for external routines  
T351   Bracketed SQL comments (/*...*/ comments)  
T441   ABS and MOD functions  
T461   Symmetric BETWEEN predicate  
T501   Enhanced EXISTS predicate  
T551   Optional key words for default syntax  
T581   Regular expression substring function  
T591   UNIQUE constraints of possibly null columns  


 
 
  Published courtesy of The PostgreSQL Global Development Group Design by Interspire