1 of 4

D. SQL 相容性

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 update appearing in 2016. The 2016 version is referred to as ISO/IEC 9075:2016, or simply as SQL:2016. The versions prior to that were SQL:2011, SQL:2008, SQL:2006, SQL:2003, 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. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time.

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.

D.2. Unsupported Features

SQL:2016 中所定義的以下功能並未在此版本的 PostgreSQL 中實作。在某些情況下，有一些等效的功能可以使用。

Identifier

Core?

Description

Comment

B011

Embedded Ada

B013

D.3. XML Limits and Conformance to SQL/XML

Significant revisions to the XML-related specifications in ISO/IEC 9075-14 (SQL/XML) were introduced with SQL:2006. PostgreSQL's implementation of the XML data type and related functions largely follows the earlier 2003 edition, with some borrowing from later editions. In particular:

Where the current standard provides a family of XML data types to hold “document” or “content” in untyped or XML Schema-typed variants, and a type XML(SEQUENCE) to hold arbitrary pieces of XML content, PostgreSQL provides the single xml type, which can hold “document” or “content”. There is no equivalent of the standard's “sequence” type.
PostgreSQL provides two functions introduced in SQL:2006, but in variants that use the XPath 1.0 language, rather than XML Query as specified for them in the standard.

This section presents some of the resulting differences you may encounter.

D.3.1. Queries Are Restricted to XPath 1.0

The PostgreSQL-specific functions xpath() and xpath_exists() query XML documents using the XPath language. PostgreSQL also provides XPath-only variants of the standard functions XMLEXISTS and XMLTABLE, which officially use the XQuery language. For all of these functions, PostgreSQL relies on the libxml2 library, which provides only XPath 1.0.

There is a strong connection between the XQuery language and XPath versions 2.0 and later: any expression that is syntactically valid and executes successfully in both produces the same result (with a minor exception for expressions containing numeric character references or predefined entity references, which XQuery replaces with the corresponding character while XPath leaves them alone). But there is no such connection between these languages and XPath 1.0; it was an earlier language and differs in many respects.

There are two categories of limitation to keep in mind: the restriction from XQuery to XPath for the functions specified in the SQL standard, and the restriction of XPath to version 1.0 for both the standard and the PostgreSQL-specific functions.

D.3.1.1. Restriction Of XQuery To XPath

Features of XQuery beyond those of XPath include:

XQuery expressions can construct and return new XML nodes, in addition to all possible XPath values. XPath can create and return values of the atomic types (numbers, strings, and so on) but can only return XML nodes that were already present in documents supplied as input to the expression.
XQuery has control constructs for iteration, sorting, and grouping.
XQuery allows declaration and use of local functions.

Recent XPath versions begin to offer capabilities overlapping with these (such as functional-style for-each and sort, anonymous functions, and parse-xml to create a node from a string), but such features were not available before XPath 3.0.

D.3.1.2. Restriction Of XPath To 1.0

For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0 presents a number of differences to contend with:

The fundamental type of an XQuery/XPath expression, the sequence, which can contain XML nodes, atomic values, or both, does not exist in XPath 1.0. A 1.0 expression can only produce a node-set (containing zero or more XML nodes), or a single atomic value.
Unlike an XQuery/XPath sequence, which can contain any desired items in any desired order, an XPath 1.0 node-set has no guaranteed order and, like any set, does not allow multiple appearances of the same item.

The differences highlighted here are not all of them. In XQuery and the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility mode, and the W3C lists of and applied in that mode offer a more complete (but still not exhaustive) account of the differences. The compatibility mode cannot make the later languages exactly equivalent to XPath 1.0.

D.3.1.3. Mappings Between SQL And XML Data Types And Values

In SQL:2006 and later, both directions of conversion between standard SQL data types and the XML Schema types are specified precisely. However, the rules are expressed using the types and semantics of XQuery/XPath, and have no direct application to the different data model of XPath 1.0.

When PostgreSQL maps SQL data values to XML (as in xmlelement), or XML to SQL (as in the output columns of xmltable), except for a few cases treated specially, PostgreSQL simply assumes that the XML data type's XPath 1.0 string form will be valid as the text-input form of the SQL datatype, and conversely. This rule has the virtue of simplicity while producing, for many data types, results similar to the mappings specified in the standard.

Where interoperability with other systems is a concern, for some data types, it may be necessary to use data type formatting functions (such as those in ) explicitly to produce the standard mappings.

D.3.2. Incidental Limits of the Implementation

This section concerns limits that are not inherent in the libxml2 library, but apply to the current implementation in PostgreSQL.

D.3.2.1. Only `BY VALUE` Passing Mechanism Is Supported

The SQL standard defines two passing mechanisms that apply when passing an XML argument from SQL to an XML function or receiving a result: BY REF, in which a particular XML value retains its node identity, and BY VALUE, in which the content of the XML is passed but node identity is not preserved. A mechanism can be specified before a list of parameters, as the default mechanism for all of them, or after any parameter, to override the default.

To illustrate the difference, if x is an XML value, these two queries in an SQL:2006 environment would produce true and false, respectively:

PostgreSQL will accept BY VALUE or BY REF in an XMLEXISTS or XMLTABLE construct, but it ignores them. The xml data type holds a character-string serialized representation, so there is no node identity to preserve, and passing is always effectively BY VALUE.

D.3.2.2. Cannot Pass Named Parameters To Queries

The XPath-based functions support passing one parameter to serve as the XPath expression's context item, but do not support passing additional values to be available to the expression as named parameters.

D.3.2.3. No `XML(SEQUENCE)` Type

The PostgreSQL xml data type can only hold a value in DOCUMENT or CONTENT form. An XQuery/XPath expression context item must be a single XML node or atomic value, but XPath 1.0 further restricts it to be only an XML node, and has no node type allowing CONTENT. The upshot is that a well-formed DOCUMENT is the only form of XML value that PostgreSQL can supply as an XPath context item.

D.3. XML Limits and Conformance to SQL/XML

Where the current standard provides a family of XML data types to hold “document” or “content” in untyped or XML Schema-typed variants, and a type XML(SEQUENCE) to hold arbitrary pieces of XML content, PostgreSQL provides the single xml type, which can hold “document” or “content”. There is no equivalent of the standard's “sequence” type.
PostgreSQL provides two functions introduced in SQL:2006, but in variants that use the XPath 1.0 language, rather than XML Query as specified for them in the standard.

This section presents some of the resulting differences you may encounter.

D.3.1. Queries Are Restricted to XPath 1.0

D.3.1.1. Restriction Of XQuery To XPath

Features of XQuery beyond those of XPath include:

XQuery expressions can construct and return new XML nodes, in addition to all possible XPath values. XPath can create and return values of the atomic types (numbers, strings, and so on) but can only return XML nodes that were already present in documents supplied as input to the expression.
XQuery has control constructs for iteration, sorting, and grouping.
XQuery allows declaration and use of local functions.

D.3.1.2. Restriction Of XPath To 1.0

For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0 presents a number of differences to contend with:

The fundamental type of an XQuery/XPath expression, the sequence, which can contain XML nodes, atomic values, or both, does not exist in XPath 1.0. A 1.0 expression can only produce a node-set (containing zero or more XML nodes), or a single atomic value.
Unlike an XQuery/XPath sequence, which can contain any desired items in any desired order, an XPath 1.0 node-set has no guaranteed order and, like any set, does not allow multiple appearances of the same item.

D.3.1.3. Mappings Between SQL And XML Data Types And Values

Where interoperability with other systems is a concern, for some data types, it may be necessary to use data type formatting functions (such as those in ) explicitly to produce the standard mappings.

D.3.2. Incidental Limits of the Implementation

This section concerns limits that are not inherent in the libxml2 library, but apply to the current implementation in PostgreSQL.

D.3.2.1. Only `BY VALUE` Passing Mechanism Is Supported

To illustrate the difference, if x is an XML value, these two queries in an SQL:2006 environment would produce true and false, respectively:

D. SQL 相容性

D.2. Unsupported Features

D.3. XML Limits and Conformance to SQL/XML

D.3.1. Queries Are Restricted to XPath 1.0

D.3.1.1. Restriction Of XQuery To XPath

D.3.1.2. Restriction Of XPath To 1.0

D.3.1.3. Mappings Between SQL And XML Data Types And Values

D.3.2. Incidental Limits of the Implementation

D.3.2.1. Only `BY VALUE` Passing Mechanism Is Supported

D.3.2.2. Cannot Pass Named Parameters To Queries

D.3.2.3. No `XML(SEQUENCE)` Type

D.3. XML Limits and Conformance to SQL/XML

D.3.1. Queries Are Restricted to XPath 1.0

D.3.1.1. Restriction Of XQuery To XPath

D.3.1.2. Restriction Of XPath To 1.0

D.3.1.3. Mappings Between SQL And XML Data Types And Values

D.3.2. Incidental Limits of the Implementation

D.3.2.1. Only `BY VALUE` Passing Mechanism Is Supported

D.3.2.2. Cannot Pass Named Parameters To Queries

D.3.2.3. No `XML(SEQUENCE)` Type

D. SQL 相容性

Note

D.2. Unsupported Features

D.1. Supported Features

D. SQL 相容性

D.2. Unsupported Features

D.3. XML Limits and Conformance to SQL/XML

D.3.1. Queries Are Restricted to XPath 1.0

D.3.1.1. Restriction Of XQuery To XPath

D.3.1.2. Restriction Of XPath To 1.0

D.3.1.3. Mappings Between SQL And XML Data Types And Values

D.3.2. Incidental Limits of the Implementation

D.3.2.1. Only BY VALUE Passing Mechanism Is Supported

D.3.2.2. Cannot Pass Named Parameters To Queries

D.3.2.3. No XML(SEQUENCE) Type

D.3. XML Limits and Conformance to SQL/XML

D.3.1. Queries Are Restricted to XPath 1.0

D.3.1.1. Restriction Of XQuery To XPath

D.3.1.2. Restriction Of XPath To 1.0

D.3.1.3. Mappings Between SQL And XML Data Types And Values

D.3.2. Incidental Limits of the Implementation

D.3.2.1. Only BY VALUE Passing Mechanism Is Supported

D.3.2.2. Cannot Pass Named Parameters To Queries

D.3.2.3. No XML(SEQUENCE) Type

D. SQL 相容性

Note

D.2. Unsupported Features

D.1. Supported Features

D.3.2.1. Only `BY VALUE` Passing Mechanism Is Supported

D.3.2.3. No `XML(SEQUENCE)` Type

D.3.2.1. Only `BY VALUE` Passing Mechanism Is Supported

D.3.2.3. No `XML(SEQUENCE)` Type