<!--

-->

<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.

CREATE VIEW myview AS

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

FROM weather, cities

WHERE city = name;

SELECT * FROM myview;

</para>

<para>

<para>

The new declaration of the tables would look like this:

CREATE TABLE cities (

city varchar(80) primary key,

location point

prcp real,

date date

);

Now try inserting an invalid record:

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

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

</para>

<para>

The behavior of foreign keys can be finely tuned to your

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

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

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');

</para>

<para>

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

transaction would actually look like

BEGIN;

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice';

-- etc etc

COMMIT;

</para>

<para>

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

might invent some scheme like this:

CREATE TABLE capitals (

name text,

population real,

SELECT name, population, altitude FROM capitals

UNION

SELECT name, population, altitude FROM non_capitals;

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:

CREATE TABLE cities (

name text,

population real,

CREATE TABLE capitals (

state char(2)

) INHERITS (cities);

</para>

<para>

including state capitals, that are located at an altitude

over 500 ft.:

SELECT name, altitude

FROM cities

WHERE altitude &gt; 500;

which returns:

all the cities that are not state capitals and

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

SELECT name, altitude

FROM ONLY cities

WHERE altitude &gt; 500;

<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>DELETE</command> -- support this <literal>ONLY</literal>

notation.

</para>

<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.

<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

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

<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

</para>

<para>

<tip>

<para>

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

</para>

</tip>

<!--

-->

<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

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>

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

<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

<programlisting>

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

</programlisting>

</para>

</sect2>

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

make chunks of 1 megabyte:

<programlisting>

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

</programlisting>

Reload with

<programlisting>

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

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

</programlisting>

</para>

</formalpara>

restored selectively. The following command dumps a database using the

custom dump format:

<programlisting>

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

</programlisting>

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

<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>

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