<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.32 2003/11/01 01:56:28 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.33 2003/11/04 09:55:38 petere Exp $ -->

<sect1 id="arrays">

<title>Arrays</title>

<sect2>

<title>Array Value Input</title>

<para>

Now we can show some <command>INSERT</command> statements. To write an array

value as a literal constant, we enclose the element values within curly

braces and separate them by commas. (If you know C, this is not unlike the

C syntax for initializing structures.) We may put double quotes around any

element value, and must do so if it contains commas or curly braces.

(More details appear below.)

<indexterm>

<primary>array</primary>

<secondary>constant</secondary>

</indexterm>

<para>

To write an array value as a literal constant, enclose the element

values within curly braces and separate them by commas. (If you

know C, this is not unlike the C syntax for initializing

structures.) You may put double quotes around any element value,

and must do so if it contains commas or curly braces. (More

details appear below.) Thus, the general format of an array

constant is the following:

<synopsis>

'{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'

</synopsis>

where <replaceable>delim</replaceable> is the delimiter character

for the type, as recorded in its <literal>pg_type</literal> entry.

(For all built-in types, this is the comma character

<quote><literal>,</literal></>.) Each

<replaceable>val</replaceable> is either a constant of the array

element type, or a subarray. An example of an array constant is

<programlisting>

'{{1,2,3},{4,5,6},{7,8,9}}'

</programlisting>

This constant is a two-dimensional, 3-by-3 array consisting of

three subarrays of integers.

</para>

<para>

(These kinds of array constants are actually only a special case of

the generic type constants discussed in <xref

linkend="sql-syntax-constants-generic">. The constant is initially

treated as a string and passed to the array input conversion

routine. An explicit type specification might be necessary.)

</para>

<para>

Now we can show some <command>INSERT</command> statements.

<programlisting>

INSERT INTO sal_emp

'{20000, 25000, 25000, 25000}',

'{{"talk", "consult"}, {"meeting"}}');

</programlisting>

</para>

</para>

<para>

A limitation of the present array implementation is that individual

elements of an array cannot be SQL null values. The entire array

can be set to null, but you can't have an array with some elements

null and some not.

</para>

<para>

A limitation of the present array implementation is that individual

elements of an array cannot be SQL null values. The entire array can be set

to null, but you can't have an array with some elements null and some

not.

</para>

<para>

This can lead to surprising results. For example, the result of the

previous two inserts looks like this:

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v 2.30 2003/08/31 17:32:18 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v 2.31 2003/11/04 09:55:38 petere Exp $

-->

<chapter id="backup">

<title>Backup and Restore</title>

<tip>

<para>

Restore performance can be improved by increasing <literal>SORT_MEM</>

(see <xref linkend="runtime-config-resource-memory">).

Restore performance can be improved by increasing the

configuration parameter <varname>sort_mem</varname> (see <xref

linkend="runtime-config-resource-memory">).

</para>

</tip>

</sect2>

going on. For this reason it is also not advisable to trust file

systems that claim to support <quote>consistent

snapshots</quote>. Information about stopping the server can be

found in <xref linkend="postmaster-shutdown">.

</para>

<para>

Needless to say that you also need to shut down the server

before restoring the data.

found in <xref linkend="postmaster-shutdown">. Needless to say

that you also need to shut down the server before restoring the

data.

</para>

</listitem>

</para>

<para>

Also note that the file system backup will not necessarily be

An alternative file-system backup approach is to make a

<quote>consistent snapshot</quote> of the data directory, if the

file system supports that functionality. Such a snapshot will save

the database files in a state where the database server was not

properly shut down; therefore, when you start the database server

on this backed up directory, it will think the server had crashed

and replay the WAL log. This is not a problem, just be aware of

it.

</para>

<para>

Note that the file system backup will not necessarily be

smaller than an SQL dump. On the contrary, it will most likely be

larger. (<application>pg_dump</application> does not need to dump

the contents of indexes for example, just the commands to recreate

</sect1>

<sect1 id="migration">

<title>Migrationbetween releases</title>

<title>MigrationBetween Releases</title>

<indexterm zone="migration">

<primary>upgrading</primary>

<para>

As a general rule, the internal data storage format is subject to

change between releases of <productname>PostgreSQL</>. This does not

apply to different <quote>patch levels</quote>, these always have

compatible storage formats. For example, releases 7.0.1, 7.1.2, and

7.2 are not compatible, whereas 7.1.1 and 7.1.2 are. When you

update between compatible versions, then you can simply reuse the

data area in disk by the new executables. Otherwise you need to

change between major releases of <productname>PostgreSQL</> (where

the number after the first dot changes). This does not apply to

different minor releases under the same major release (where the

number of the second dot changes); these always have compatible

storage formats. For example, releases 7.0.1, 7.1.2, and 7.2 are

not compatible, whereas 7.1.1 and 7.1.2 are. When you update

between compatible versions, then you can simply reuse the data

area in disk by the new executables. Otherwise you need to

<quote>back up</> your data and <quote>restore</> it on the new

server, using <application>pg_dump</>. (There are checks in place

that prevent you from doing the wrong thing, so no harm can be done

by confusing these things.) The precise installation procedure is

not subject of this section; these details are in <xref linkend="installation">.

not subject of this section; these details are in <xref

linkend="installation">.

</para>

<para>

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/charset.sgml,v 2.39 2003/09/11 18:30:38momjian Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/charset.sgml,v 2.40 2003/11/04 09:55:38petere Exp $ -->

<chapter id="charset">

<title>Localization</>

<para>

Client applications that handle server-side errors by parsing the

text of the error message will obviously have problems when the

server's messages are in a different language. If you create such

an application you need to devise a plan to cope with this

situation. The embedded SQL interface (<application>ECPG</>) is

also affected by this problem. It is currently recommended that

servers interfacing with <application>ECPG</> applications be

configured to send messages in English.

server's messages are in a different language. Authors of such

applications are advised to make use of the error code scheme

instead.

</para>

<para>

mistakenly meant ISO 8859-5. From 7.2 on, <literal>LATIN5</>

means ISO 8859-9. If you have a <literal>LATIN5</> database

created on 7.1 or earlier and want to migrate to 7.2 or later,

you should beverycareful about this change.

you should be careful about this change.

</para>

</important>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v 1.59 2003/09/13 16:43:38momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v 1.60 2003/11/04 09:55:38petere Exp $

-->

<chapter id="client-authentication">

<para>

A record may have one of the seven formats

<synopsis>

local <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

host <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

hostssl <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

local<replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

host<replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

hostssl<replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

hostnossl <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable> <replaceable>IP-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

host <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable>/<replaceable>CIDR-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

hostssl <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable>/<replaceable>CIDR-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

hostnossl <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable>/<replaceable>CIDR-mask</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

host<replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable>/<replaceable>IP-masklen</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

hostssl<replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable>/<replaceable>IP-masklen</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

hostnossl <replaceable>database</replaceable> <replaceable>user</replaceable> <replaceable>IP-address</replaceable>/<replaceable>IP-masklen</replaceable> <replaceable>authentication-method</replaceable> <optional><replaceable>authentication-option</replaceable></optional>

</synopsis>

The meaning of the fields is as follows:

</varlistentry>

<varlistentry>

<term><replaceable>CIDR-mask</replaceable></term>

<term><replaceable>IP-masklen</replaceable></term>

<listitem>

<para>

This field may be used as an alternative to the

<replaceable>IP-mask</replaceable> notation. It is an

integerspecifying the number of high-order bits

to setin themask. The number must

be between 0 and 32(in the case of anIPv4 address)or 128

(in the case of an IPv6 address) inclusive. 0will match any

address, while 32/128 willmatch only the exact host specified.

The same matching logicis used as for a dotted notation

<replaceable>IP-Mask</replaceable>.

This field may be used as an alternative to the

<replaceable>IP-mask</replaceable> notation. It is an integer

specifying the number of high-order bitsto set in the mask.

The number must be between 0 and 32 (in thecase of an IPv4

address) or 128(in the case of anIPv6 address)inclusive. 0

will match any address, while 32 (or 128, respectively)will

match only the exact host specified. The same matching logic

is used as for a dotted notation

<replaceable>IP-mask</replaceable>.

</para>

<para>

There must be no white space between the <replaceable>IP-address</replaceable>

and the <literal>/</literal> or the <literal>/</literal> and the

<replaceable>CIDR-mask</replaceable>, or the file will not be parsed

correctly.

There must be no white space between the

<replaceable>IP-address</replaceable> and the

<literal>/</literal> or the <literal>/</literal> and the

<replaceable>IP-masklen</replaceable>, or the file will not be

parsed correctly.

</para>

<para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.128 2003/11/01 01:56:29 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.129 2003/11/04 09:55:38 petere Exp $

-->

<chapter id="datatype">

<para>

In addition, <productname>PostgreSQL</productname> provides the

<type>text</type> type, which stores strings of any

length.Although the type <type>text</type> is not in the

<acronym>SQL</acronym> standard, several other SQL database products

have it as well.

<type>text</type> type, which stores strings of any length.

Although the type <type>text</type> is not in the

<acronym>SQL</acronym> standard, several other SQL database

management systemshave it as well.

</para>

<para>

</row>

<row>

<entry>1999-01-08</entry>

<entry>ISO-8601, January 8 in any mode

<entry>ISO8601, January 8 in any mode

(recommended format)</entry>

</row>

<row>

</row>

<row>

<entry>19990108</entry>

<entry>ISO-8601; January 8, 1999 in any mode</entry>

<entry>ISO8601; January 8, 1999 in any mode</entry>

</row>

<row>

<entry>990108</entry>

<entry>ISO-8601; January 8, 1999 in any mode</entry>

<entry>ISO8601; January 8, 1999 in any mode</entry>

</row>

<row>

<entry>1999.008</entry>

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.21 2003/11/01 01:56:29 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.22 2003/11/04 09:55:38 petere Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

</para>

<para>

Transaction identifiers are also 32-bit quantities. In a long-lived

database it is possible for transaction IDs to wrap around. This

is not a fatal problem given appropriate maintenance procedures;

see <xref linkend="maintenance"> for details.However, it is

unwiseto depend on uniqueness of transaction IDs over the long term

(more than one billion transactions).

Transaction identifiers are also 32-bit quantities. In a

long-liveddatabase it is possible for transaction IDs to wrap

around. Thisis not a fatal problem given appropriate maintenance

procedures;see <xref linkend="maintenance"> for details.It is

unwise, however,to depend ontheuniqueness of transaction IDs

over the long term(more than one billion transactions).

</para>

<para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/features.sgml,v 2.20 2003/09/20 20:12:04 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/features.sgml,v 2.21 2003/11/04 09:55:38 petere Exp $

-->

<appendix id="features">

<para>

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

Entry, Intermediate, and Full. Most databaseproducts claiming

Entry, Intermediate, and Full. Most databasemanagement 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