<!-- $PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.53 2007/01/31 20:56:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.54 2007/02/01 00:28:16 momjian Exp $ -->

<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

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

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

<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

might look like

might look like:

<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

transaction would actually look like

transaction would actually look like:

<programlisting>

BEGIN;

<para>

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

including state capitals, that are located at an altitude

over 500ft.:

over 500feet:

<programlisting>

SELECT name, altitude

<para>

On the other hand, the following query finds

all the cities that are not state capitals and

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

are situated at an altitude of 500feet or higher:

<programlisting>

SELECT name, altitude

<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.55 2007/01/31 20:56:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.56 2007/02/01 00:28:16 momjian Exp $ -->

<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

of an array constant is

of an array constant is:

<programlisting>

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

</programlisting>

</para>

<para>

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

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

<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

specified array dimension, respectively.

specified array dimension, respectively:

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

<literal>||</literal>.

<literal>||</literal>:

<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

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

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

<programlisting>

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

</programlisting>

<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.96 2007/01/31 20:56:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.97 2007/02/01 00:28:16 momjian Exp $ -->

<chapter id="backup">

<title>Backup and Restore</title>

<title>Use compressed dumps.</title>

<para>

You can use your favorite compression program, for example

<application>gzip</application>.

<application>gzip</application>:

<programlisting>

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

</programlisting>

Reload with

Reload with:

<programlisting>

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

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

</programlisting>

or

or:

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

Reload with

Reload with:

<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

for doing usual file system backups, for example

for doing usual file system backups, for example:

<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

like

like:

<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

like

like:

<programlisting>

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

</programlisting>

</listitem>

<listitem>

<para>

Connect to the database as a superuser, and issue the command

Connect to the database as a superuser, and issue the command:

<programlisting>

SELECT pg_start_backup('label');

</programlisting>

</listitem>

<listitem>

<para>

Again connect to the database as a superuser, and issue the command

Again connect to the database as a superuser, and issue the command:

<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

something like

something like:

<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

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

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

<programlisting>

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

<!-- $PostgreSQL: pgsql/doc/src/sgml/bki.sgml,v 1.20 2007/01/31 20:56:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/bki.sgml,v 1.21 2007/02/01 00:28:16 momjian Exp $ -->

<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

two rows into the table.

two rows into the table:

<programlisting>

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

open test_table

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.107 2007/01/31 20:56:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.108 2007/02/01 00:28:16 momjian Exp $ -->

<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

configuration file at this point. Include directives simply look like

configuration file at this point. Include directives simply look like:

<programlisting>

include 'filename'

</programlisting>

<!-- $PostgreSQL: pgsql/doc/src/sgml/cvs.sgml,v 1.40 2007/01/31 20:56:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/cvs.sgml,v 1.41 2007/02/01 00:28:16 momjian Exp $ -->

<appendix id="cvs">

<appendixinfo>

<para>

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

<command>cd</command> into

the <filename>pgsql</filename> subdirectory, and issue

the <filename>pgsql</filename> subdirectory, and issue:

<programlisting>

cvs -z3 update -d -P

</programlisting>

<step>

<para>

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

in your home directory that contains

in your home directory that contains:

<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

to say

to say:

<programlisting>

cvs update

</programlisting>

</para>

<para>

After you've done the initial checkout on a branch

After you've done the initial checkout on a branch:

<programlisting>

cvs checkout -r REL6_4

</programlisting>

anything you do within that directory structure is restricted to that

branch. If you apply a patch to that directory structure and do a

branch. If you apply a patch to that directory structure and do a:

<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

repository in <filename>/home/cvs/</filename>, then put

repository in <filename>/home/cvs/</filename>, then put:

<programlisting>

setenv CVSROOT /home/cvs

<para>

Verify that

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

you can do this by typing

you can do this by typing:

<programlisting>

which cvsup

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.189 2007/01/31 20:56:16 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.190 2007/02/01 00:28:16 momjian Exp $ -->

<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

the syntax

the syntax:

<programlisting>

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

</programlisting>

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

Alternatively,

Alternatively:

<programlisting>

NUMERIC(<replaceable>precision</replaceable>)

</programlisting>

selects a scale of 0. Specifying

selects a scale of 0. Specifying:

<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

implementation, specifying

implementation, specifying:

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

Thus

Thus:

<programlisting>

1999-01-08 04:05:06

</programlisting>

and

and:

<programlisting>

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

</programlisting>

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

standard. In addition, the wide-spread format

standard. In addition, the wide-spread format:

<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

ISO mode the output looks like

ISO mode the output looks like:

<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

<literal>mytable</>, one could write

<literal>mytable</>, one could write:

<programlisting>

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

</programlisting>

rather than

rather than:

<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

syntaxes

syntaxes:

<programlisting><![CDATA[

xml '<foo>bar</foo>'

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