<!--

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.35 2003/08/3117:32:18petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.36 2003/09/12 22:17:18tgl Exp $

-->

<chapter id="tutorial-advanced">

</programlisting>

<screen>

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

ERROR: insert or update on "weather" violates foreign key constraint "$1"

DETAIL: Key (city)=(Berkeley) is not present in "cities".

</screen>

</para>

<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

$Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.73 2003/06/25 01:14:37 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.74 2003/09/12 22:17:19 tgl Exp $

-->

<chapter id="catalogs">

<row>

<entry><structfield>adnum</structfield></entry>

<entry><type>int2</type></entry>

<entry><literal>pg_attribute.attnum</literal></entry>

<entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</literal></entry>

<entry>The number of the column</entry>

</row>

<row>

<entry><structfield>refclassid</structfield></entry>

<entry><type>oid</type></entry>

<entry><literal>pg_class.oid</literal></entry>

<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>

<entry>The OID of the system catalog the referenced object is in</entry>

</row>

<row>

<entry><structfield>typbasetype</structfield></entry>

<entry><type>oid</type></entry>

<entry><literal>pg_type.oid</literal></entry>

<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>

<entry><para>

If this is a derived type (see <structfield>typtype</structfield>),

then <structfield>typbasetype</structfield> identifies

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v 1.57 2003/09/05 20:31:35 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/client-auth.sgml,v 1.58 2003/09/12 22:17:22 tgl Exp $

-->

<chapter id="client-authentication">

</para>

<para>

Tobe ablemake use of this option the server must be built

To make use of this option the server must be built

with SSL support enabled. Furthermore, SSL must be enabled by

enabling the <varname>ssl</varname> configuration parameter

(see <xref linkend="runtime-config"> for more information).

<para>

<ProgramListing>

Nopg_hba.conf entry for host 123.123.123.123, user andym, database testdb

FATAL: nopg_hba.conf entry for host"123.123.123.123", user"andym", database"testdb"

</ProgramListing>

This is what you are most likely to get if you succeed in contacting

the server, but it does not want to talk to you. As the message

<para>

<ProgramListing>

Password authentication failed for user'andym'

FATAL:Password authentication failed for user"andym"

</ProgramListing>

Messages like this indicate that you contacted the server, and it is

willing to talk to you, but not until you pass the authorization

<para>

<ProgramListing>

FATAL 1: user "andym" does not exist

FATAL: user "andym" does not exist

</ProgramListing>

The indicated user name was not found.

</para>

<para>

<ProgramListing>

FATAL 1:Database "testdb" does not exist in the system catalog.

FATAL:database "testdb" does not exist

</ProgramListing>

The database you are trying to connect to does not exist. Note that

if you do not specify a database name, it defaults to the database

user name, which may or may not be the right thing.

</para>

<tip>

<para>

Note that the server log may contain more information about an

The server log may contain more information about an

authentication failure than is reported to the client. If you are

confused about the reason for a failure, check the log.

</para>

</tip>

</sect1>

</chapter>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.123 2003/08/3117:32:18 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.124 2003/09/12 22:17:22 tgl Exp $

-->

<chapter id="datatype">

INSERT INTO test VALUES (B'101', B'00');

INSERT INTO test VALUES (B'10', B'101');

<computeroutput>

ERROR:Bit string length 2 does not match typeBIT(3)

ERROR:bit string length 2 does not match typebit(3)

</computeroutput>

INSERT INTO test VALUES (B'10'::bit(3), B'101');

SELECT * FROM test;

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.18 2003/08/3117:32:18 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.19 2003/09/12 22:17:22 tgl Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

DROP TABLE products;

NOTICE: constraint $1 on table orders depends on table products

ERROR:Cannot drop table products because other objects depend on it

Use DROP ... CASCADE to drop the dependent objects too

ERROR:cannot drop table products because other objects depend on it

HINT:Use DROP ... CASCADE to drop the dependent objects too.

</screen>

The error message contains a useful hint: if you do not want to

bother deleting all the dependent objects individually, you can run

specifying <literal>CASCADE</literal>. Of course, the nature of

the possible dependencies varies with the type of the object. You

can also write <literal>RESTRICT</literal> instead of

<literal>CASCADE</literal> to get the default behavior which is to

<literal>CASCADE</literal> to get the default behavior, which is to

prevent drops of objects that other objects depend on.

</para>

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/docguide.sgml,v 1.45 2003/09/11 21:42:19 momjian Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/docguide.sgml,v 1.46 2003/09/12 22:17:22 tgl Exp $ -->

<appendix id="docguide">

<title>Documentation</title>

</listitem>

</itemizedlist>

Additionally, a number of plain-text README-type files can be found

throughout the <productname>PostgreSQL</productname> source tree,

Additionally, a number of plain-text<filename>README</filename> files can

be foundthroughout the <productname>PostgreSQL</productname> source tree,

documenting various implementation issues.

</para>

<para>

Reference pages describing SQL commands should contain the

following sections: Name, Synopsis, Description, Parameters,

Usage, Diagnostics, Notes, Examples, Compatibility, History, See

Outputs, Notes, Examples, Compatibility, History, See

Also. The Parameters section is like the Options section, but

there is more freedom about which clauses of the command can be

listed. The Compatibility section should explain to what extent

listed. The Outputs section is only needed if the command returns

something other than a default command-completion tag. The Compatibility

section should explain to what extent

this command conforms to the SQL standard(s), or to which other

database system it is compatible. The See Also section of SQL

commands should list SQL commands before cross-references to

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.172 2003/09/11 18:30:38 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.173 2003/09/12 22:17:22 tgl Exp $

PostgreSQL documentation

-->

</table>

<para>

Warning. <literal><function>to_char</function>(<type>interval</type>, <type>text</type>)</literal>

is deprecated and should not be used in newly-written code.Will be removed in the next version.

Warning: <literal><function>to_char</function>(<type>interval</type>, <type>text</type>)</literal>

is deprecated and should not be used in newly-written code.It will be removed in the next version.

</para>

<para>

2 | two

3 | other

</screen>

</para>

</para>

<para>

A <token>CASE</token> expression does not evaluate any subexpressions

that are not needed to determine the result. For example, this is a

possible way of avoiding a division-by-zero failure:

<programlisting>

SELECT ... WHERE CASE WHEN x &lt;&gt; 0 THEN y/x &gt; 1.5 ELSE false END;

</programlisting>

</para>

</sect2>

<sect2>

<para>

The <function>COALESCE</function> function returns the first of its

arguments that is not null. This is often useful to substitute a

arguments that is not null. Null is returned only if all arguments

are null. This is often useful to substitute a

default value for null values when data is retrieved for display,

for example:

<programlisting>

SELECT COALESCE(description, short_description, '(none)') ...

</programlisting>

</para>

<para>

Like a <token>CASE</token> expression, <function>COALESCE</function> will

not evaluate arguments that are not needed to determine the result;

that is, arguments to the right of the first non-null argument are

not evaluated.

</para>

</sect2>

<sect2>

<tbody>

<row>

<entry>

<literal>

<function>array_cat</function>

(<type>anyarray</type>, <type>anyarray</type>)

</literal>

</entry>

<entry><type>anyarray</type></entry>

<entry>

concatenate two arrays, returning <literal>NULL</literal>

for <literal>NULL</literal> inputs

</entry>

<entry><literal>array_cat(ARRAY[1,2,3], ARRAY[4,5])</literal></entry>

<entry><literal>{1,2,3,4,5}</literal></entry>

</row>

<row>

<entry>

<literal>

<function>array_append</function>

(<type>anyarray</type>, <type>anyelement</type>)

<row>

<entry>

<literal>

<function>array_cat</function>

(<type>anyarray</type>, <type>anyarray</type>)

<function>array_prepend</function>

(<type>anyelement</type>, <type>anyarray</type>)

</literal>

</entry>

<entry><type>anyarray</type></entry>

<entry>

concatenate two arrays, returning <literal>NULL</literal>

for <literal>NULL</literal> inputs

append an element to the beginning of an array, returning

<literal>NULL</literal>for <literal>NULL</literal> inputs

</entry>

<entry><literal>array_cat(ARRAY[1,2,3], ARRAY[4,5,6])</literal></entry>

<entry><literal>{1,2,3,4,5,6}</literal></entry>

<entry><literal>array_prepend(1, ARRAY[2,3])</literal></entry>

<entry><literal>{1,2,3}</literal></entry>

</row>

<row>

<entry>

returns a text representation of array dimension lower and upper bounds,

generating an ERROR for <literal>NULL</literal> inputs

</entry>

<entry><literal>array_dims(array[[1,2,3],[4,5,6]])</literal></entry>

<entry><literal>array_dims(array[[1,2,3],[4,5,6]])</literal></entry>

<entry><literal>[1:2][1:3]</literal></entry>

</row>

<row>

<row>

<entry>

<literal>

<function>array_prepend</function>

(<type>anyelement</type>, <type>anyarray</type>)

<function>array_upper</function>

(<type>anyarray</type>, <type>integer</type>)

</literal>

</entry>

<entry><type>anyarray</type></entry>

<entry><type>integer</type></entry>

<entry>

append an element to thebeginning of anarray, returning

returns upper bound of therequestedarray dimension, returning

<literal>NULL</literal> for <literal>NULL</literal> inputs

</entry>

<entry><literal>array_prepend(1,ARRAY[2,3])</literal></entry>

<entry><literal>{1,2,3}</literal></entry>

<entry><literal>array_upper(ARRAY[1,2,3,4], 1)</literal></entry>

<entry><literal>4</literal></entry>

</row>

<row>

<entry>

concatenates array elements using provided delimiter, returning

<literal>NULL</literal> for <literal>NULL</literal> inputs

</entry>

<entry><literal>array_to_string(array[1.1,2.2,3.3]::numeric(4,2)[],'~^~')</literal></entry>

<entry><literal>1.10~^~2.20~^~3.30</literal></entry>

</row>

<row>

<entry>

<literal>

<function>array_upper</function>

(<type>anyarray</type>, <type>integer</type>)

</literal>

</entry>

<entry><type>integer</type></entry>

<entry>

returns upper bound of the requested array dimension, returning

<literal>NULL</literal> for <literal>NULL</literal> inputs

</entry>

<entry><literal>array_upper(array_append(ARRAY[1,2,3], 4), 1)</literal></entry>

<entry><literal>4</literal></entry>

<entry><literal>array_to_string(array[1, 2, 3], '~^~')</literal></entry>

<entry><literal>1~^~2~^~3</literal></entry>

</row>

<row>

<entry>

splits string into array elements using provided delimiter, returning

<literal>NULL</literal> for <literal>NULL</literal> inputs

</entry>

<entry><literal>string_to_array('1.10~^~2.20~^~3.30','~^~')::float8[]</literal></entry>

<entry><literal>{1.1,2.2,3.3}</literal></entry>

<entry><literal>string_to_array( 'xx~^~yy~^~zz','~^~')</literal></entry>

<entry><literal>{xx,yy,zz}</literal></entry>

</row>

</tbody>

</tgroup>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/info.sgml,v 1.19 2003/09/08 23:02:28 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/info.sgml,v 1.20 2003/09/12 22:17:23 tgl Exp $

-->

<sect1 id="resources">

<term>READMEs</term>

<listitem>

<para>

<filename>README</filename> files are available forsome

<filename>README</filename> files are available formost

contributed packages.

</para>

</listitem>