<!--

$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.54 2004/12/28 19:08:58 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.55 2005/01/22 22:56:35 momjian Exp $

-->

<chapter id="backup">

<title>Backup and Restore</title>

following command dumps a database using the custom dump format:

<programlisting>

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

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

</programlisting>

A custom-format dump is not a script for <application>psql</>, but

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

<programlisting>

pg_dumpall> backup

pg_dumpall&gt; backup

pg_ctl stop

mv /usr/local/pgsql /usr/local/pgsql.old

cd ~/postgresql-&version;

gmake install

initdb -D /usr/local/pgsql/data

postmaster -D /usr/local/pgsql/data

psql template1< backup

psql template1&lt; backup

</programlisting>

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

<!--

$PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.154 2005/01/17 18:47:15 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.155 2005/01/22 22:56:35 momjian Exp $

-->

<chapter id="datatype">

</row>

<row>

<entry><literal>04:05 PM</literal></entry>

<entry>same as 16:05; input hour must be<= 12</entry>

<entry>same as 16:05; input hour must be&lt;= 12</entry>

</row>

<row>

<entry><literal>04:05:06.789-8</literal></entry>

<entry><type>circle</type></entry>

<entry>24 bytes</entry>

<entry>Circle</entry>

<entry><(x,y),r> (center and radius)</entry>

<entry>&lt;(x,y),r&gt; (center and radius)</entry>

</row>

</tbody>

</tgroup>

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.38 2005/01/17 01:29:02 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.39 2005/01/22 22:56:35 momjian Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

CREATE TABLE products (

product_no integer,

name text,

price numeric <emphasis>CHECK (price> 0)</emphasis>

price numeric <emphasis>CHECK (price&gt; 0)</emphasis>

);

</programlisting>

</para>

CREATE TABLE products (

product_no integer,

name text,

price numeric <emphasis>CONSTRAINT positive_price</emphasis> CHECK (price> 0)

price numeric <emphasis>CONSTRAINT positive_price</emphasis> CHECK (price&gt; 0)

);

</programlisting>

So, to specify a named constraint, use the key word

CREATE TABLE products (

product_no integer,

name text,

price numeric CHECK (price> 0),

discounted_price numeric CHECK (discounted_price> 0),

<emphasis>CHECK (price> discounted_price)</emphasis>

price numeric CHECK (price&gt; 0),

discounted_price numeric CHECK (discounted_price&gt; 0),

<emphasis>CHECK (price&gt; discounted_price)</emphasis>

);

</programlisting>

</para>

product_no integer,

name text,

price numeric,

CHECK (price> 0),

CHECK (price&gt; 0),

discounted_price numeric,

CHECK (discounted_price> 0),

CHECK (price> discounted_price)

CHECK (discounted_price&gt; 0),

CHECK (price&gt; discounted_price)

);

</programlisting>

or even

<programlisting>

CREATE TABLE products (

product_no integer,

name text,

price numeric CHECK (price> 0),

price numeric CHECK (price&gt; 0),

discounted_price numeric,

CHECK (discounted_price> 0 AND price> discounted_price)

CHECK (discounted_price&gt; 0 AND price&gt; discounted_price)

);

</programlisting>

It's a matter of taste.

product_no integer,

name text,

price numeric,

CHECK (price> 0),

CHECK (price&gt; 0),

discounted_price numeric,

CHECK (discounted_price> 0),

<emphasis>CONSTRAINT valid_discount</> CHECK (price> discounted_price)

CHECK (discounted_price&gt; 0),

<emphasis>CONSTRAINT valid_discount</> CHECK (price&gt; discounted_price)

);

</programlisting>

</para>

CREATE TABLE products (

product_no integer NOT NULL,

name text NOT NULL,

price numeric NOT NULL CHECK (price> 0)

price numeric NOT NULL CHECK (price&gt; 0)

);

</programlisting>

The order doesn't matter. It does not necessarily determine in which

<!--

$PostgreSQL: pgsql/doc/src/sgml/dfunc.sgml,v 1.28 2004/11/15 06:32:13 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/dfunc.sgml,v 1.29 2005/01/22 22:56:35 momjian Exp $

-->

<sect2 id="dfunc">

You must then create a symbol \*(lqexports\*(rq file for the object

file:

.nf

mkldexport foo.o `pwd`> foo.exp

mkldexport foo.o `pwd`&gt; foo.exp

.fi

Finally, you can create the shared library:

.nf

<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.9 2004/12/23 05:37:39 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.10 2005/01/22 22:56:35 momjian Exp $ -->

<chapter id="dml">

<title>Data Manipulation</title>

<literal>UPDATE</literal> command by listing more than one

assignment in the <literal>SET</literal> clause. For example:

<programlisting>

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a> 0;

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a&gt; 0;

</programlisting>

</para>

</sect1>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.62 2005/01/08 22:13:25 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.63 2005/01/22 22:56:35 momjian Exp $

-->

<chapter id="ecpg">

<literal>INTO</literal> clause:

<programlisting>

EXEC SQL BEGIN DECLARE SECTION;

const char *stmt = "SELECT a, b, c FROM test1 WHERE a> ?";

const char *stmt = "SELECT a, b, c FROM test1 WHERE a&gt; ?";

int v1, v2;

VARCHAR v3;

EXEC SQL END DECLARE SECTION;

<!--

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.234 2005/01/09 20:08:50 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.235 2005/01/22 22:56:35 momjian Exp $

PostgreSQL documentation

-->

<row>

<entry> <literal>&gt;^</literal> </entry>

<entry>Is above?</entry>

<entry><literal>circle '((0,5),1)'>^ circle '((0,0),1)'</literal></entry>

<entry><literal>circle '((0,5),1)'&gt;^ circle '((0,0),1)'</literal></entry>

</row>

<row>

<entry> <literal>?#</literal> </entry>

<!--

$PostgreSQL: pgsql/doc/src/sgml/geqo.sgml,v 1.27 2005/01/05 23:42:03 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/geqo.sgml,v 1.28 2005/01/22 22:56:36 momjian Exp $

Genetic Optimizer

-->

<literallayout class="monospaced">

+=========================================+

|>>>>>>>>>>> Algorithm GA<<<<<<<<<<<<<<|

|&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; Algorithm GA&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;|

+=========================================+

| INITIALIZE t := 0 |

+=========================================+

<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.49 2005/01/08 22:13:25 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.50 2005/01/22 22:56:36 momjian Exp $ -->

<chapter id="indexes">

<title id="indexes-title">Indexes</title>

such as this:

<programlisting>

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)

WHERE NOT (client_ip> inet '192.168.100.0' AND client_ip< inet '192.168.100.255');

WHERE NOT (client_ip&gt; inet '192.168.100.0' AND client_ip&lt; inet '192.168.100.255');

</programlisting>

</para>

<para>

A possible query to use this index would be

<programlisting>

SELECT * FROM orders WHERE billed is not true AND order_nr< 10000;

SELECT * FROM orders WHERE billed is not true AND order_nr&lt; 10000;

</programlisting>

However, the index can also be used in queries that do not involve

<structfield>order_nr</> at all, e.g.,

<programlisting>

SELECT * FROM orders WHERE billed is not true AND amount> 5000.00;

SELECT * FROM orders WHERE billed is not true AND amount&gt; 5000.00;

</programlisting>

This is not as efficient as a partial index on the

<structfield>amount</> column would be, since the system has to

<!--

$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.57 2005/01/15 03:38:44 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.58 2005/01/22 22:56:36 momjian Exp $

-->

<chapter id="plpgsql">

</programlisting>

<programlisting>

IF v_count> 0 THEN

IF v_count&gt; 0 THEN

INSERT INTO users_count (count) VALUES (v_count);

RETURN 't';

ELSE

SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

IF a_running_job_count> 0 THEN

IF a_running_job_count&gt; 0 THEN

COMMIT; -- free lock<co id="co.plpgsql-porting-commit">

raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');

END IF;

SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

IF a_running_job_count> 0 THEN

IF a_running_job_count&gt; 0 THEN

RAISE EXCEPTION 'Unable to create a new job: a job is currently running';<co id="co.plpgsql-porting-raise">

END IF;

length integer;

ss_length integer;

BEGIN

IF beg_index> 0 THEN

IF beg_index&gt; 0 THEN

temp_str := substring(string FROM beg_index);

pos := position(string_to_search IN temp_str);

length := char_length(string);

beg := length + beg_index - ss_length + 2;

WHILE beg> 0 LOOP

WHILE beg&gt; 0 LOOP

temp_str := substring(string FROM beg FOR ss_length);

pos := position(string_to_search IN temp_str);

IF pos> 0 THEN

IF pos&gt; 0 THEN

RETURN beg;

END IF;

length integer;

ss_length integer;

BEGIN

IF beg_index> 0 THEN

IF beg_index&gt; 0 THEN

beg := beg_index;

temp_str := substring(string FROM beg_index);

length := char_length(string);

beg := length + beg_index - ss_length + 2;

WHILE beg> 0 LOOP

WHILE beg&gt; 0 LOOP

temp_str := substring(string FROM beg FOR ss_length);

pos := position(string_to_search IN temp_str);

IF pos> 0 THEN

IF pos&gt; 0 THEN

occur_number := occur_number + 1;

IF occur_number = occur_index THEN