<chapter id="tutorial-advanced">

<title>Advanced Features</title>

is of particular interest to your application, but you do not want

to type the query each time you need it. You can create a

<firstterm>view</firstterm> over the query, which gives a name to

<programlisting>

CREATE VIEW myview AS

customer accounts, as well as total deposit balances for branches.

Suppose that we want to record a payment of $100.00 from Alice's account

to Bob's account. Simplifying outrageously, the SQL commands for this

<programlisting>

UPDATE accounts SET balance = balance - 100.00

In <productname>PostgreSQL</>, a transaction is set up by surrounding

the SQL commands of the transaction with

<command>BEGIN</> and <command>COMMIT</> commands. So our banking

<programlisting>

BEGIN;

<para>

For example, the following query finds the names of all cities,

including state capitals, that are located at an altitude

<programlisting>

SELECT name, altitude

<para>

On the other hand, the following query finds

all the cities that are not state capitals and

<programlisting>

SELECT name, altitude

<sect1 id="arrays">

<title>Arrays</title>

<literal>box</> uses a semicolon (<literal>;</>) but all the others

use comma (<literal>,</>). Each <replaceable>val</replaceable> is

either a constant of the array element type, or a subarray. An example

<programlisting>

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

</programlisting>

</para>

<para>

<programlisting>

INSERT INTO sal_emp

for programs. Dimensions can also be retrieved with

<function>array_upper</function> and <function>array_lower</function>,

which return the upper and lower bound of a

<programlisting>

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

<para>

New array values can also be constructed by using the concatenation operator,

<programlisting>

SELECT ARRAY[1,2] || ARRAY[3,4];

?column?

Remember that what you write in an SQL command will first be interpreted

as a string literal, and then as an array. This doubles the number of

backslashes you need. For example, to insert a <type>text</> array

<programlisting>

INSERT ... VALUES (E'{"\\\\","\\""}');

</programlisting>

<chapter id="backup">

<title>Backup and Restore</title>

<title>Use compressed dumps.</title>

<para>

You can use your favorite compression program, for example

<programlisting>

pg_dump <replaceable class="parameter">dbname</replaceable> | gzip &gt; <replaceable class="parameter">filename</replaceable>.gz

</programlisting>

<programlisting>

createdb <replaceable class="parameter">dbname</replaceable>

gunzip -c <replaceable class="parameter">filename</replaceable>.gz | psql <replaceable class="parameter">dbname</replaceable>

</programlisting>

<programlisting>

cat <replaceable class="parameter">filename</replaceable>.gz | gunzip | psql <replaceable class="parameter">dbname</replaceable>

pg_dump <replaceable class="parameter">dbname</replaceable> | split -b 1m - <replaceable class="parameter">filename</replaceable>

</programlisting>

<programlisting>

createdb <replaceable class="parameter">dbname</replaceable>

<xref linkend="creating-cluster"> it is explained where these files

are located, but you have probably found them already if you are

interested in this method. You can use whatever method you prefer

<programlisting>

tar -cf backup.tar /usr/local/pgsql/data

i.e., the cluster's data directory.)

Write <literal>%%</> if you need to embed an actual <literal>%</>

character in the command. The simplest useful command is something

<programlisting>

archive_command = 'cp -i %p /mnt/server/archivedir/%f &lt;/dev/null'

</programlisting>

this correctly on some platforms but not others. If the chosen command

does not itself handle this case correctly, you should add a command

to test for pre-existence of the archive file. For example, something

<programlisting>

archive_command = 'test ! -f .../%f &amp;&amp; cp %p .../%f'

</programlisting>

</listitem>

<listitem>

<para>

<programlisting>

SELECT pg_start_backup('label');

</programlisting>

</listitem>

<listitem>

<para>

<programlisting>

SELECT pg_stop_backup();

</programlisting>

i.e., the cluster's data directory.)

Write <literal>%%</> if you need to embed an actual <literal>%</>

character in the command. The simplest useful command is

<programlisting>

restore_command = 'cp /mnt/server/archivedir/%f %p'

</programlisting>

<para>

The least downtime can be achieved by installing the new server in

a different directory and running both the old and the new servers

<programlisting>

pg_dumpall -p 5432 | psql -d postgres -p 6543

<chapter id="bki">

<title><acronym>BKI</acronym> Backend Interface</title>

table <literal>test_table</literal> with OID 420, having two columns

<literal>cola</literal> and <literal>colb</literal> of type

<type>int4</type> and <type>text</type>, respectively, and insert

<programlisting>

create test_table 420 (cola = int4, colb = text)

open test_table

<chapter Id="runtime-config">

<title>Server Configuration</title>

In addition to parameter settings, the <filename>postgresql.conf</>

file can contain <firstterm>include directives</>, which specify

another file to read and process as if it were inserted into the

<programlisting>

include 'filename'

</programlisting>

<appendix id="cvs">

<appendixinfo>

<para>

Whenever you want to update to the latest <productname>CVS</productname> sources,

<command>cd</command> into

<programlisting>

cvs -z3 update -d -P

</programlisting>

<step>

<para>

You can save yourself some typing by making a file <filename>.cvsrc</filename>

<programlisting>

cvs -z3

This supplies the <option>-z3</option> option to all <command>cvs</> commands, and the

<option>-d</option> and <option>-P</option> options to <command>cvs update</>. Then you just have

<programlisting>

cvs update

</programlisting>

</para>

<para>

<programlisting>

cvs checkout -r REL6_4

</programlisting>

anything you do within that directory structure is restricted to that

<programlisting>

cvs commit

but had formerly kept it under a

<productname>PostgreSQL</productname> development tree in

<filename>/opt/postgres/cvs/</filename>. If you intend to keep your

<programlisting>

setenv CVSROOT /home/cvs

<para>

Verify that

<application>cvsup</application> is in your path; on most systems

<programlisting>

which cvsup

<chapter id="datatype">

<title id="datatype-title">Data Types</title>

Both the maximum precision and the maximum scale of a

<type>numeric</type> column can be

configured. To declare a column of type <type>numeric</type> use

<programlisting>

NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)

</programlisting>

The precision must be positive, the scale zero or positive.

<programlisting>

NUMERIC(<replaceable>precision</replaceable>)

</programlisting>

<programlisting>

NUMERIC

</programlisting>

a notational convenience for setting up unique identifier columns

(similar to the <literal>AUTO_INCREMENT</literal> property

supported by some other databases). In the current

<programlisting>

CREATE TABLE <replaceable class="parameter">tablename</replaceable> (

followed by an optional <literal>AD</literal> or <literal>BC</literal>.

(Alternatively, <literal>AD</literal>/<literal>BC</literal> can appear

before the time zone, but this is not the preferred ordering.)

<programlisting>

1999-01-08 04:05:06

</programlisting>

<programlisting>

1999-01-08 04:05:06 -8:00

</programlisting>

are valid values, which follow the <acronym>ISO</acronym> 8601

<programlisting>

January 8 04:05:06 1999 PST

</programlisting>

that units like <literal>century</literal> or

<literal>week</literal> are converted to years and days and

<literal>ago</literal> is converted to an appropriate sign. In

<programlisting>

<optional> <replaceable>quantity</> <replaceable>unit</> <optional> ... </> </> <optional> <replaceable>days</> </> <optional> <replaceable>hours</>:<replaceable>minutes</>:<replaceable>seconds</> </optional>

the raw numeric value that type <type>oid</> would use. The alias

types allow simplified lookup of OID values for objects. For example,

to examine the <structname>pg_attribute</> rows related to a table

<programlisting>

SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;

</programlisting>

<programlisting>

SELECT * FROM pg_attribute

WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');

]]></programlisting>

While this is the only way to convert character strings into XML

values according to the SQL standard, the PostgreSQL-specific

<programlisting><![CDATA[

xml '<foo>bar</foo>'

'<foo>bar</foo>'::xml