<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.39 2004/11/15 06:32:13 neilc Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.40 2004/11/27 21:27:05 petere Exp $ -->

<sect1 id="arrays">

<title>Arrays</title>

</para>

<para>

An alternative, SQL99-standard syntax may be used for one-dimensional arrays.

<structfield>pay_by_quarter</structfield> could have been defined as:

An alternative syntax, which conforms to the SQL:1999 standard, may

be used for one-dimensional arrays.

<structfield>pay_by_quarter</structfield> could have been defined

as:

<programlisting>

pay_by_quarter integer ARRAY[4],

</programlisting>

<!--

$PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.150 2004/09/20 22:48:25 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.151 2004/11/27 21:27:05 petere Exp $

-->

<chapter id="datatype">

</para>

<para>

For <type>timestamp [without time zone]</type>, any explicit time

zone specified in the input is silently ignored. That is, the

resulting date/time value is derived from the explicit date/time

The <acronym>SQL</acronym> standard differentiates <type>timestamp without time zone</type>

and <type>timestamp with time zone</type> literals by the existence of a

<quote>+</quote>; or <quote>-</quote>. Hence, according to the standard,

<programlisting>TIMESTAMP '2004-10-19 10:23:54'</programlisting>

is a <type>timestamp without time zone</type>, while

<programlisting>TIMESTAMP '2004-10-19 10:23:54+02'</programlisting>

is a <type>timestamp with time zone</type>.

<productname>PostgreSQL</productname>

differs from the standard by requiring that <type>timestamp with time zone</type>

literals be explicitly typed:

<programlisting>TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'</programlisting>

If a literal is not explicitly indicated as being of <type>timestamp with time zone</type>,

PostgreSQL will silently ignore any time zone indication in the literal.

That is, the resulting date/time value is derived from the date/time

fields in the input value, and is not adjusted for time zone.

</para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.32 2004/11/15 06:32:13 neilc Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.33 2004/11/27 21:27:06 petere Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

<para>

In previous versions of <productname>PostgreSQL</productname>, the

default behavior was not to include child tables in queries. This was

found to be error prone and is also in violation of theSQL99

found to be error prone and is also in violation of theSQL:1999

standard. Under the old syntax, to get the sub-tables you append

<literal>*</literal> to the table name.

For example

<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.10 2004/11/15 06:32:13 neilc Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.11 2004/11/27 21:27:06 petere Exp $ -->

<appendix id="errcodes-appendix">

<title><productname>PostgreSQL</productname> Error Codes</title>

<row>

<entry>Class 02</entry>

<entry>No Data &mdash; this is also a warning class perSQL99</entry>

<entry>No Data &mdash; this is also a warning class perSQL:1999</entry>

</row>

<row>

<!--

$PostgreSQL: pgsql/doc/src/sgml/features.sgml,v 2.22 2003/11/29 19:51:37 pgsql Exp $

$PostgreSQL: pgsql/doc/src/sgml/features.sgml,v 2.23 2004/11/27 21:27:06 petere Exp $

-->

<appendix id="features">

<title>SQL Conformance</title>

<para>

This section attempts to outline to what extent

<productname>PostgreSQL</productname> conforms to theSQL standard.

Full compliance to the standard or a complete statementabout the

compliance tothestandard is complicated and not particularly

useful, so this section can only give an overview.

</para>

<productname>PostgreSQL</productname> conforms to thecurrent SQL

standard. The following information is not a full statementof

conformance, but it presentsthemain topics in as much detail as is

both reasonable and useful for users.

</para>

<para>

The formal name of the SQL standard is ISO/IEC 9075 <quote>Database

Language SQL</quote>. A revised version of the standard is released

from time to time; the most recent one appearing in 1999. That

version is referred to as ISO/IEC 9075:1999, or informally as SQL99.

The version prior to that was SQL92.

<productname>PostgreSQL</productname> development tends to aim for

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.

<productname>PostgreSQL</productname> development aims 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, balloting is under way for a

new revision of the standard, which, if approved, will eventually

become the conformance target for future

<productname>PostgreSQL</productname> development.

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.

</para>

<para>

<acronym>SQL-92</acronym> defined three feature sets for

conformance: Entry, Intermediate, and Full. Most database

management systems claiming <acronym>SQL</acronym> 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.

</para>

<para>

<acronym>SQL92</acronym> defined three feature sets for conformance:

Entry, Intermediate, and Full. Most database management systems claiming

<acronym>SQL</acronym> 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 <acronym>SQL:1999</acronym>, the SQL standard defines

a large set of individual features rather than the ineffectively

broad three levels found in <acronym>SQL-92</acronym>. A large

subset of these features represents the <quote>Core</quote>

features, which every conforming SQL implementation must supply.

The rest of the features are purely optional. Some optional

features are grouped together to form <quote>packages</quote>, which

SQL implementations can claim conformance to, thus claiming

conformance to particular groups of features.

</para>

<para>

<acronym>SQL99</acronym> defines a large set of individual features

rather than the ineffectively broad three levels found in

<acronym>SQL92</acronym>. A large subset of these features

represents the <quote>core</quote> features, which every conforming

SQL implementation must supply. The rest of the features are purely

optional. Some optional features are grouped together to form

<quote>packages</quote>, which SQL implementations can claim

conformance to, thus claiming conformance to particular groups of

features.

The <acronym>SQL:2003</acronym> standard is also split into a number

of parts. Each is known by a shorthand name. Note that these parts

are not consecutively numbered.

<itemizedlist>

<listitem><para>ISO/IEC 9075-1 Framework (<acronym>SQL/Framework</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-2 Foundation (<acronym>SQL/Foundation</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-3 Call Level Interface (<acronym>SQL/CLI</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-4 Persistent Stored Modules (<acronym>SQL/PSM</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-9 Management of External Data (<acronym>SQL/MED</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-10 Object Language Bindings (<acronym>SQL/OLB</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-11 Information and Definition Schemas (<acronym>SQL/Schemata</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-13 Routines and Types using the Java Language (<acronym>SQL/JRT</acronym>)</para></listitem>

<listitem><para>ISO/IEC 9075-14 XML-related specifications (<acronym>SQL/XML</acronym>)</para></listitem>

</itemizedlist>

</para>

<para>

The <acronym>SQL99</acronym> standard is also split into 5 parts:

Framework, Foundation, Call Level Interface, Persistent Stored

Modules, and Host Language Bindings.

<productname>PostgreSQL</productname> only covers parts 1, 2, and 5.

<productname>PostgreSQL</productname> covers parts 1, 2, and 11.

Part 3 is similar to the ODBC interface, and part 4 is similar to

the <application>PL/pgSQL</application> programming language, but

exact conformance is not specifically intended in either case.

exact conformance is not specifically intended or verified in either

case.

</para>

<para>

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.

</para>

<para>

In the following two sections, we provide a list of those features

that <productname>PostgreSQL</productname> supports, followed by a

list of the features defined inSQL99 which are not yet supported in

<productname>PostgreSQL</productname>. 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.

list of the features defined in<acronym>SQL:2003</acronym> which

are not yet supported in<productname>PostgreSQL</productname>.

Both of these lists areapproximate: There may be minor details that

are nonconforming for afeature that is listed as supported, and

large parts of anunsupported feature may in fact be implemented.

The main body ofthe documentation always contains the most accurate

informationabout what does and does not work.

</para>

<note>

</para>

</note>

<sect1 id="features-sql99">

<sect1 id="features-sql-standard">

<title>Supported Features</title>

<para>

</para>

</sect1>

<sect1 id="unsupported-features-sql99">

<sect1 id="unsupported-features-sql-standard">

<title>Unsupported Features</title>

<para>

The following features defined in <acronym>SQL99</acronym> are not

The following features defined in <acronym>SQL:2003</acronym> are not

implemented in this release of

<productname>PostgreSQL</productname>. In a few cases, equivalent

functionality is available.

<!--

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.223 2004/11/15 06:32:13 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.224 2004/11/27 21:27:06 petere Exp $

PostgreSQL documentation

-->

</indexterm>

<para>

There are three separate approaches to pattern matching provided by

<productname>PostgreSQL</productname>: the traditional

<acronym>SQL</acronym>

<function>LIKE</function> operator, the more recent

<acronym>SQL99</acronym>

<function>SIMILAR TO</function> operator, and

<acronym>POSIX</acronym>-style regular expressions.

There are three separate approaches to pattern matching provided

by <productname>PostgreSQL</productname>: the traditional

<acronym>SQL</acronym> <function>LIKE</function> operator, the

more recent <literal>>SIMILAR TO</literal> operator (since

SQL:1999), and <acronym>POSIX</acronym>-style regular expressions.

Additionally, a pattern matching function,

<function>substring</function>, is available, using either

<acronym>SQL99</acronym>-style or POSIX-style regular expressions.

<literal>SIMILAR TO</literal>-style or POSIX-style regular

expressions.

</para>

<tip>

</sect2>

<sect2 id="functions-sql99-regexp">

<title><function>SIMILAR TO</function> and <acronym>SQL99</acronym>

Regular Expressions</title>

<sect2 id="functions-similarto-regexp">

<title><function>SIMILAR TO</function> Regular Expressions</title>

<indexterm zone="functions-sql99-regexp">

<indexterm zone="functions-similarto-regexp">

<primary>regular expression</primary>

<!-- <seealso>pattern matching</seealso> breaks index build -->

</indexterm>

</synopsis>

<para>

The <function>SIMILAR TO</function> operator returns true or false

depending on whether its pattern matches the given string. It is

much like <function>LIKE</function>, except that it interprets the

pattern using <acronym>SQL99</acronym>'s definition of a regular

expression.

<acronym>SQL99</acronym>'s regular expressions are a curious cross

between <function>LIKE</function> notation and common regular expression

notation.

The <function>SIMILAR TO</function> operator returns true or

false depending on whether its pattern matches the given string.

It is much like <function>LIKE</function>, except that it

interprets the pattern using the SQL standard's definition of a

regular expression. SQL regular expressions are a curious cross

between <function>LIKE</function> notation and common regular

expression notation.

</para>

<para>

<function>substring(<parameter>string</parameter> from

<replaceable>pattern</replaceable> for

<replaceable>escape-character</replaceable>)</function>, provides

extraction of a substring that matchesa <acronym>SQL99</acronym>

extraction of a substring that matchesan SQL

regular expression pattern. As with <literal>SIMILAR TO</>, the

specified pattern must match to the entire data string, else the

function fails and returns null. To indicate the part of the

<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.18 2004/11/15 06:32:13 neilc Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.19 2004/11/27 21:27:06 petere Exp $ -->

<chapter id="information-schema">

<title>The Information Schema</title>

<entry>

The year the standard referenced in

<literal>sql_language_source</literal> was approved; currently

<literal>1999</>

<literal>2003</>

</entry>

</row>

<entry><type>character_data</type></entry>

<entry>

The standard conformance level for the language binding. For

ISO 9075:1999 this is always <literal>CORE</literal>.

ISO 9075:2003 this is always <literal>CORE</literal>.

</entry>

</row>

<!--

$PostgreSQL: pgsql/doc/src/sgml/intro.sgml,v 1.25 2004/08/08 01:52:14 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/intro.sgml,v 1.26 2004/11/27 21:27:06 petere Exp $

-->

<preface id="preface">

<para>

<productname>PostgreSQL</productname> is an open-source descendant

of this original Berkeley code. It supportsSQL92, SQL99 and

SQL2003 and offers many modern features:

of this original Berkeley code. It supportsa large part of the SQL:2003

standard and offers many modern features:

<itemizedlist spacing="compact">

<listitem>

<!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.14 2004/10/18 17:09:03 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.15 2004/11/27 21:27:06 petere Exp $ -->

<appendix id="sql-keywords-appendix">

<title><acronym>SQL</acronym> Key Words</title>

<row>

<entry>Key Word</entry>

<entry><productname>PostgreSQL</productname></entry>

<entry><acronym>SQL</acronym>2003</entry>

<entry><acronym>SQL</acronym>1999</entry>

<entry><acronym>SQL</acronym> 1992</entry>

<entry>SQL:2003</entry>

<entry>SQL:1999</entry>

<entry>SQL-92</entry>

</row>

</thead>