<!--

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.30 2002/10/24 17:48:54 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.31 2002/11/11 20:14:02 petere Exp $

-->

<chapter id="tutorial-advanced">

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

the query that you can refer to like an ordinary table.

<programlisting>

<programlisting>

CREATE VIEW myview AS

SELECT city, temp_lo, temp_hi, prcp, date, location

FROM weather, cities

WHERE city = name;

SELECT * FROM myview;

</programlisting>

</programlisting>

</para>

<para>

<para>

The new declaration of the tables would look like this:

<programlisting>

<programlisting>

CREATE TABLE cities (

city varchar(80) primary key,

location point

prcp real,

date date

);

</programlisting>

</programlisting>

Now try inserting an invalid record:

<programlisting>

<programlisting>

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

</programlisting>

</programlisting>

<screen>

<screen>

ERROR: &lt;unnamed&gt; referential integrity violation - key referenced from weather not found in cities

</screen>

</screen>

</para>

<para>

The behavior of foreign keys can be finely tuned to your

application. We will not go beyond this simple example in this

tutorial, but just refer you to the &cite-reference;

tutorial, but just refer you to the &cite-user;

for more information. Making correct use of

foreign keys will definitely improve the quality of your database

applications, so you are strongly encouraged to learn about them.

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

might look like

<programlisting>

<programlisting>

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice';

UPDATE branches SET balance = balance - 100.00

WHERE name = 'Bob';

UPDATE branches SET balance = balance + 100.00

WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

</programlisting>

</programlisting>

</para>

<para>

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

transaction would actually look like

<programlisting>

<programlisting>

BEGIN;

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice';

-- etc etc

COMMIT;

</programlisting>

</programlisting>

</para>

<para>

implicitly when you list all cities. If you're really clever you

might invent some scheme like this:

<programlisting>

<programlisting>

CREATE TABLE capitals (

name text,

population real,

SELECT name, population, altitude FROM capitals

UNION

SELECT name, population, altitude FROM non_capitals;

</programlisting>

</programlisting>

This works OK as far as querying goes, but it gets ugly when you

need to update several rows, to name one thing.

<para>

A better solution is this:

<programlisting>

<programlisting>

CREATE TABLE cities (

name text,

population real,

CREATE TABLE capitals (

state char(2)

) INHERITS (cities);

</programlisting>

</programlisting>

</para>

<para>

including state capitals, that are located at an altitude

over 500 ft.:

<programlisting>

<programlisting>

SELECT name, altitude

FROM cities

WHERE altitude &gt; 500;

</programlisting>

</programlisting>

which returns:

all the cities that are not state capitals and

are situated at an altitude of 500 ft. or higher:

<programlisting>

<programlisting>

SELECT name, altitude

FROM ONLY cities

WHERE altitude &gt; 500;

</programlisting>

</programlisting>

<screen>

name | altitude

<classname>cities</classname> table, and not tables below

<classname>cities</classname> in the inheritance hierarchy. Many

of the commands that we have already discussed --

<command>SELECT</command>, <command>UPDATE</command> and

<command>SELECT</command>, <command>UPDATE</command>, and

<command>DELETE</command> -- support this <literal>ONLY</literal>

notation.

</para>

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.23 2002/11/10 00:32:16 momjian Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.24 2002/11/11 20:14:02 petere Exp $ -->

<sect1 id="arrays">

<title>Arrays</title>

</programlisting>

As shown, an array data type is named by appending square brackets

(<literal>[]</>) to the data type name of the array elements.

The abovequery will create a table named

The abovecommand will create a table named

<structname>sal_emp</structname> with columns including

a <type>text</type> string (<structfield>name</structfield>),

a one-dimensional array of type

The array subscript numbers are written within square brackets.

By default <productname>PostgreSQL</productname> uses the

<quote>one-based</quote> numbering convention for arrays, that is,

one-based numbering convention for arrays, that is,

an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and

ends with <literal>array[<replaceable>n</>]</literal>.

</para>

<para>

We can also access arbitrary rectangular slices of an array, or

subarrays. An array slice is denoted by writing

<literal><replaceable>lower subscript</replaceable> :

<replaceable>upper subscript</replaceable></literal> for one or more

array dimensions. This query retrieves the first item on Bill's

schedule for the first two days of the week:

<literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>

for one or more array dimensions. This query retrieves the first

item on Bill's schedule for the first two days of the week:

<programlisting>

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

with the same result. An array subscripting operation is taken to

represent an array slice if any of the subscripts are written in the

form <replaceable>lower</replaceable> <literal>:</literal>

<replaceable>upper</replaceable>. A lower bound of 1 is assumed for

any subscript where only one value is specified.

form

<literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.

A lower bound of 1 is assumed for any subscript where only one value

is specified.

</para>

<para>

<tip>

<para>

Remember that what you write in an SQLquery will first be interpreted

Remember that what you write in an SQLcommand 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

value containing a backslash and a double quote, you'd need to write

become <literal>\</> and <literal>"</> respectively. (If we were working

with a data type whose input routine also treated backslashes specially,

<type>bytea</> for example, we might need as many as eight backslashes

in thequery to get one backslash into the stored array element.)

in thecommand to get one backslash into the stored array element.)

</para>

</tip>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v 2.23 2002/10/21 02:11:37 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v 2.24 2002/11/11 20:14:02 petere Exp $

-->

<chapter id="backup">

<title>Backup and Restore</title>

<para>

As any other <productname>PostgreSQL</> client application,

<application>pg_dump</> will by default connect with the database

user name that is equal to the currentUnix user name. To override

user name that is equal to the currentoperating system user name. To override

this, either specify the <option>-U</option> option or set the

environment variable <envar>PGUSER</envar>. Remember that

<application>pg_dump</> connections are subject to the normal

</synopsis>

where <replaceable class="parameter">infile</replaceable> is what

you used as <replaceable class="parameter">outfile</replaceable>

for the pg_dump command. The database <replaceable

for the<command>pg_dump</> command. The database <replaceable

class="parameter">dbname</replaceable> will not be created by this

command, you must create it yourself from template0 before executing

command, you must create it yourself from<literal>template0</> before executing

<application>psql</> (e.g., with <literal>createdb -T template0

<replaceable class="parameter">dbname</></literal>).

<application>psql</> supports similar options to <application>pg_dump</>

The ability of <application>pg_dump</> and <application>psql</> to

write to or read from pipes makes it possible to dump a database

directly from one server to another, for example

<informalexample>

<programlisting>

pg_dump -h <replaceable>host1</> <replaceable>dbname</> | psql -h <replaceable>host2</> <replaceable>dbname</>

</programlisting>

</informalexample>

</para>

<important>

<para>

The dumps produced by pg_dump are relative to template0. This means

that any languages, procedures, etc. added to template1 will also be

dumped by <application>pg_dump</>. As a result, when restoring, if

you are using a customized template1, you must create the empty

database from template0, as in the example above.

</para>

</important>

<important>

<para>

The dumps produced by <application>pg_dump</> are relative to

<literal>template0</>. This means that any languages, procedures,

etc. added to <literal>template1</> will also be dumped by

<application>pg_dump</>. As a result, when restoring, if you are

using a customized <literal>template1</>, you must create the

empty database from <literal>template0</>, as in the example

above.

</para>

</important>

</sect2>

acceptable in size to the underlying file system. For example, to

make chunks of 1 megabyte:

<informalexample>

<programlisting>

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

</programlisting>

</informalexample>

Reload with

<informalexample>

<programlisting>

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

cat <replaceable class="parameter">filename</replaceable>* | psql <replaceable class="parameter">dbname</replaceable>

</programlisting>

</informalexample>

</para>

</formalpara>

restored selectively. The following command dumps a database using the

custom dump format:

<informalexample>

<programlisting>

pg_dump -Fc <replaceable class="parameter">dbname</replaceable> > <replaceable class="parameter">filename</replaceable>

</programlisting>

</informalexample>

See the <application>pg_dump</> and <application>pg_restore</> reference pages for details.

</para>

</formalpara>

<para>

For reasons of backward compatibility, <application>pg_dump</> does

not dump large objects by default. To dump large objects you must use

either the custom or the TAR output format, and use the-b option in

either the custom or the TAR output format, and use the<option>-b</> option in

<application>pg_dump</>. See the reference pages for details.

The directory <filename>contrib/pg_dumplo</> of the

<productname>PostgreSQL</> source tree also contains a program that can

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

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

for doing usual file system backups, for example

<programlisting>

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

</programlisting>

</informalexample>

</para>

<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

in parallel, on different ports. Then you can use something like

<programlisting>

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

</programlisting>

to transfer your data, or use an intermediate file if you want.

Then you can shut down the old server and start the new server at

the port the old one was running at. You should make sure that the

do the back up step before installing the new version, bring down

the server, move the old version out of the way, install the new

version, start the new server, restore the data. For example:

<programlisting>

pg_dumpall > backup

pg_ctl stop

postmaster -D /usr/local/pgsql/data

psql template1 < backup

</programlisting>

See <xref linkend="runtime"> about ways to start and stop the

server and other details. The installation instructions will advise

you of strategic places to perform these steps.