<!--

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.28 2002/06/11 15:32:32 thomas Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.29 2002/10/20 05:05:46 tgl Exp $

-->

<chapter id="tutorial-advanced">

<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 tothe <citetitle>Reference

Manual</citetitle>for more information. Making correct use of

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

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.

</para>

<productname>PostgreSQL</productname> has many features not

touched upon in this tutorial introduction, which has been

oriented toward newer users of <acronym>SQL</acronym>. These

features are discussed in more detail in both the

<citetitle>User's Guide</citetitle> and the

<citetitle>Programmer's Guide</citetitle>.

features are discussed in more detail in both &cite-user;

and &cite-programmer;.

</para>

<para>

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.5 2002/09/21 18:32:52 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.6 2002/10/20 05:05:46 tgl Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

explaining how tables are created and modified and what features are

available to control what data is stored in the tables.

Subsequently, we discuss how tables can be organized into

namespaces, and how privileges can be assigned to tables. Finally,

schemas, and how privileges can be assigned to tables. Finally,

we will briefly look at other features that affect the data storage,

such as views, functions, and triggers. Detailed information on

these topics is found in &cite-programmer;.

<literal>second_column</literal> and the type <type>integer</type>.

The table and column names follow the identifier syntax explained

in <xref linkend="sql-syntax-identifiers">. The type names are

also identifiers, but there are some exceptions. Note that the

usuallyalso identifiers, but there are some exceptions. Note that the

column list is comma-separated and surrounded by parentheses.

</para>

<tip>

<para>

When you create many interrelated tables it is wise to choose a

consistent namingpatter for the tables and columns. For

consistent namingpattern for the tables and columns. For

instance, there is a choice of using singular or plural nouns for

table names, both of which are favored by some theorist or other.

</para>

</para>

<para>

The default value may be a scalar expression, whichwell be

The default value may be a scalar expression, whichwill be

evaluated whenever the default value is inserted

(<emphasis>not</emphasis> when the table is created).

</para>

<para>

A foreign key constraint specifies that the values in a column (or

a group of columns) must match the values in some other column.

a group of columns) must match the values appearing in some row

of another table.

We say this maintains the <firstterm>referential

integrity</firstterm> between two related tables.

</para>

<para>

Restricting and cascading deletes are the two most common options.

<literal>RESTRICT</literal> can also be written as <literal>NO

ACTON</literal> and it's also the default if you don't specify

ACTION</literal> and it's also the default if you don't specify

anything. There are two other options for what should happen with

the foreign key columns when a primary key is deleted:

<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.

<para>Add columns,</para>

</listitem>

<listitem>

<para>Removea column,</para>

<para>Removecolumns,</para>

</listitem>

<listitem>

<para>Add constraints,</para>

<para>Change default values,</para>

</listitem>

<listitem>

<para>Renamea column,</para>

<para>Renamecolumns,</para>

</listitem>

<listitem>

<para>Renamethe table.</para>

<para>Renametables.</para>

</listitem>

</itemizedlist>

</itemizedlist>

Schemas are analogous to directories at the operating system level,

but schemas cannot be nested.

except that schemas cannot be nested.

</para>

<sect2 id="ddl-schemas-create">

(since this is one of the ways to restrict the activities of your

users to well-defined namespaces). The syntax for that is:

<programlisting>

CREATE SCHEMA <replaceable>schemaname</replaceable>AUTHORIZATON <replaceable>username</replaceable>;

CREATE SCHEMA <replaceable>schemaname</replaceable>AUTHORIZATION <replaceable>username</replaceable>;

</programlisting>

You can even omit the schema name, in which case the schema name

will be the same as the user name. See <xref

<para>

In the previous sections we created tables without specifying any

schema names.Thosetables (and other objects) are automatically

put into a schema named <quote>public</quote>. Every new database

contains such a schema. Thus, the following are equivalent:

schema names.By default, suchtables (and other objects) are

automaticallyput into a schema named <quote>public</quote>. Every new

databasecontains such a schema. Thus, the following are equivalent:

<programlisting>

CREATE TABLE products ( ... );

</programlisting>

<para>

Schemas can be used to organize your data in many ways. There are

a few usage patterns are recommended and are easily supported by

a few usage patternsthatare recommended and are easily supported by

the default configuration:

<itemizedlist>

<listitem>

<para>

If you do not create any schemas then all users access the

public schema implicitly. This simulates the situation where

schemas are not available at all. This setup is mainly

recommended when there is only a single user or few cooperating

recommended when there is only a single user orafew cooperating

users in a database. This setup also allows smooth transition

from the non-schema-aware world.

</para>

additional functions provided by third parties, etc.), put them

into separate schemas. Remember to grant appropriate

privileges to allow the other users to access them. Users can

then refer to these additionalobject by qualifying the names

then refer to these additionalobjects by qualifying the names

with a schema name, or they can put the additional schemas into

their path, as they choose.

</para>

<screen>

DROP TABLE products CASCADE;

</screen>

and all the dependent objects will be removed.Actually, this

and all the dependent objects will be removed.In this case, it

doesn't remove the orders table, it only removes the foreign key

constraint.

constraint. (If you want to check what DROP ... CASCADE will do,

run DROP without CASCADE and read the NOTICEs.)

</para>

<para>

According to the SQL standard, specifying either

<literal>RESTRICT</literal> or <literal>CASCADE</literal> is

required. No database system actually implements it that way, but

the defaults might be different.

whether the default behavior is <literal>RESTRICT</literal> or

<literal>CASCADE</literal> varies across systems.

</para>

</note>

Foreign key constraint dependencies and serial column dependencies

from <productname>PostgreSQL</productname> versions prior to 7.3

are <emphasis>not</emphasis> maintained or created during the

upgrade process. All other dependency types survive the upgrade.

upgrade process. All other dependency types will be properly

created during an upgrade.

</para>

</note>

</sect1>

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/dml.sgml,v 1.1 2002/08/05 19:44:57 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/dml.sgml,v 1.2 2002/10/20 05:05:46 tgl Exp $ -->

<chapter id="dml">

<title>Data Manipulation</title>

necessarily possible to directly specify which row to update.

Instead, you specify which conditions a row must meet in order to

be updated. Only if you have a primary key in the table (no matter

whether you declared it or not) youcan addressrows individually

by choosing a condition that matches the primary key only.

whether you declared it or not)canyoureliably addressindividual rows,

by choosing a condition that matches the primary key.

Graphical database access tools rely on this fact to allow you to

update rows individually.

</para>

DELETE FROM products WHERE price = 10;

</programlisting>

</para>

<para>

If you simply write

<programlisting>

DELETE FROM products;

</programlisting>

then all rows in the table will be deleted! Caveat programmer.

</para>

</sect1>

</chapter>

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/queries.sgml,v 1.17 2002/09/2018:39:41 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/queries.sgml,v 1.18 2002/10/2005:05:46 tgl Exp $ -->

<chapter id="queries">

<title>Queries</title>

<literal>table1</literal>. (The method of retrieval depends on the

client application. For example, the

<application>psql</application> program will display an ASCII-art

table on the screen, client libraries will offer functions to

table on the screen,whileclient libraries will offer functions to

retrieve individual rows and columns.) The select list

specification <literal>*</literal> means all columns that the table

expression happens to provide. A select list can also select a

subset of the available columns or even make calculations on the

columns before retrieving them; see <xref

linkend="queries-select-lists">. For example, if

subset of the available columns or make calculations using the

columns. For example, if

<literal>table1</literal> has columns named <literal>a</>,

<literal>b</>, and <literal>c</> (and perhaps others) you can make

the following query:

</programlisting>

(assuming that <literal>b</> and <literal>c</> are of a numerical

data type).

See <xref linkend="queries-select-lists"> for more details.

</para>

<para>

SELECT 3 * 4;

</programlisting>

This is more useful if the expressions in the select list return

varying results. For example, you could call a function this way.

varying results. For example, you could call a function this way:

<programlisting>

SELECT random();

</programlisting>

<para>

First, an inner join is performed. Then, for each row in

T1 that does not satisfy the join condition with any row in

T2, a joined row isreturned with null values in columns of

T2, a joined row isadded with null values in columns of

T2. Thus, the joined table unconditionally has at least

one row for each row in T1.

</para>

<para>

First, an inner join is performed. Then, for each row in

T2 that does not satisfy the join condition with any row in

T1, a joined row isreturned with null values in columns of

T1, a joined row isadded with null values in columns of

T1. This is the converse of a left join: the result table

will unconditionally have a row for each row in T2.

</para>

<para>

First, an inner join is performed. Then, for each row in

T1 that does not satisfy the join condition with any row in

T2, a joined row isreturned with null values in columns of

T2, a joined row isadded with null values in columns of

T2. Also, for each row of T2 that does not satisfy the

join condition with any row in T1, a joined row with null

values in the columns of T1 isreturned.

values in the columns of T1 isadded.

</para>

</listitem>

</varlistentry>

<literal>JOIN</> syntax in the <literal>FROM</> clause is

probably not as portable to other SQL database products. For

outer joins there is no choice in any case: they must be done in

the <literal>FROM</> clause.A <literal>ON</>/<literal>USING</>

the <literal>FROM</> clause.An <literal>ON</>/<literal>USING</>

clause of an outer join is <emphasis>not</> equivalent to a

<literal>WHERE</> condition, because it determines the addition

of rows (for unmatched input rows) as well as the removal of rows

<para>

In the second query, we could not have written <literal>SELECT *

FROM test1 GROUP BY x;</literal>, because there is no single value

FROM test1 GROUP BY x</literal>, because there is no single value

for the column <literal>y</> that could be associated with each

group. In general, if a table is grouped, columns that are not

used in the grouping cannot be referenced except in aggregate

expressions, forexample:

expressions. Anexample with aggregate expressions is:

<screen>

<prompt>=></> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x;</>

x | sum

they have a known constant value per group.

</para>

<note>

<tip>

<para>

Grouping without aggregate expressions effectively calculates the

set of distinct values in a column. This can also be achieved

using the <literal>DISTINCT</> clause (see <xref

linkend="queries-distinct">).

</para>

</note>

</tip>

<para>

Here is another example:A <function>sum(sales)</function> on a

Here is another example: <function>sum(sales)</function> on a

table grouped by product code gives the total sales for each

product, not the total sales on all products.

<programlisting>

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales

FROM products p LEFT JOIN sales s USING (product_id)

GROUP BYpid, p.name, p.price;

GROUP BYproduct_id, p.name, p.price;

</programlisting>

In this example, the columns <literal>pid</literal>,

In this example, the columns <literal>product_id</literal>,

<literal>p.name</literal>, and <literal>p.price</literal> must be

in the <literal>GROUP BY</> clause since they are referenced in

the query select list. (Depending on how exactly the products

SELECT <replaceable>select_list</replaceable> FROM ... <optional>WHERE ...</optional> GROUP BY ... HAVING <replaceable>boolean_expression</replaceable>

</synopsis>

Expressions in the <literal>HAVING</> clause can refer both to

grouped expressions and to ungroupedexpression (which necessarily

grouped expressions and to ungroupedexpressions (which necessarily

involve an aggregate function).

</para>

Again, a more realistic example:

<programlisting>

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit

FROM products p LEFT JOIN sales s USING (pid)

FROM products p LEFT JOIN sales s USING (product_id)

WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'

GROUP BY product_id, p.name, p.price, p.cost

HAVING sum(p.price * s.units) > 5000;

<para>

If more than one sort column is specified, the later entries are

used to sort rows that are equal under the order imposed by the

earlier sortspecifications.

earlier sortcolumns.

</para>

</sect1>