<!-- $PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.51 2006/09/16 00:30:11 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.52 2006/10/21 23:12:57 tgl Exp $ -->

<chapter id="tutorial-advanced">

<title>Advanced Features</title>

type of the column <structfield>name</structfield> is

<type>text</type>, a native <productname>PostgreSQL</productname>

type for variable length character strings. State capitals have

an extra column, state, that shows their state. In

an extra column,<structfield>state</>, that shows their state. In

<productname>PostgreSQL</productname>, a table can inherit from

zero or more other tables.

</para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/query.sgml,v 1.47 2006/09/16 00:30:15 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/query.sgml,v 1.48 2006/10/21 23:12:57 tgl Exp $ -->

<chapter id="tutorial-sql">

<title>The <acronym>SQL</acronym> Language</title>

<para>

In the examples that follow, we assume that you have created a

database named <literal>mydb</literal>, as described in the previous

chapter, and havestarted <application>psql</application>.

chapter, and havebeen able to start <application>psql</application>.

</para>

<para>

</screen>

This creates the scripts and compiles the C files containing user-defined

functions and types. (You must use GNU make for this &mdash; it may be named

something different on your system, often <application>gmake</>.)

functions and types. (If you installed a pre-packaged version of

<productname>PostgreSQL</productname> rather than building from source,

look for a directory named <filename>tutorial</> within the

<productname>PostgreSQL</productname> documentation. The <quote>make</>

part should already have been done for you.)

Then, to start the tutorial, do the following:

<screen>

<prompt>$</prompt> <userinput>cd <replaceable>....</replaceable>/src/tutorial</userinput>

<prompt>$</prompt> <userinput>cd <replaceable>....</replaceable>/tutorial</userinput>

<prompt>$</prompt> <userinput>psql -s mydb</userinput>

<computeroutput>

...

In some database systems, including older versions of

<productname>PostgreSQL</productname>, the implementation of

<literal>DISTINCT</literal> automatically orders the rows and

so <literal>ORDER BY</literal> isredundant. But this is not

so <literal>ORDER BY</literal> isunnecessary. But this is not

required by the SQL standard, and current

<productname>PostgreSQL</productname> doesn't guarantee that

<literal>DISTINCT</literal> causes the rows to be ordered.

<para>

Since the columns all had different names, the parser

automatically found out which table they belong to, but it is good

style to fully qualify column names in join queries:

automatically found out which table they belong to. If there

were duplicate column names in the two tables you'd need to

<firstterm>qualify</> the column names to show which one you

meant, as in:

<programlisting>

SELECT weather.city, weather.temp_lo, weather.temp_hi,

weather.prcp, weather.date, cities.location

FROM weather, cities

WHERE cities.name = weather.city;

</programlisting>

It is widely considered good style to qualify all column names

in a join query, so that the query won't fail if a duplicate

column name is later added to one of the tables.

</para>

<para>

Now we will figure out how we can get the Hayward records back in.

What we want the query to do is to scan the

<classname>weather</classname> table and for each row to find the

matching <classname>cities</classname> row. If no matching row is

matching <classname>cities</classname> row(s). If no matching row is

found we want some <quote>empty values</quote> to be substituted

for the <classname>cities</classname> table's columns. This kind

of query is called an <firstterm>outer join</firstterm>. (The

but this will not work since the aggregate

<function>max</function> cannot be used in the

<literal>WHERE</literal> clause. (This restriction exists because

the <literal>WHERE</literal> clause determinesthe rowsthatwill

go into theaggregation stage; so it has to be evaluated before

aggregate functions are computed.)

the <literal>WHERE</literal> clause determineswhich rows will be

included in theaggregate calculation; soobviouslyit has to be evaluated

beforeaggregate functions are computed.)

However, as is often the case

the query can be restated to accomplish theintended result, here

the query can be restated to accomplish thedesired result, here

by using a <firstterm>subquery</firstterm>:

<programlisting>

You can update existing rows using the

<command>UPDATE</command> command.

Suppose you discover the temperature readings are

alloffby 2 degreesas ofNovember 28. You mayupdate the

all off by 2 degreesafterNovember 28. You maycorrect the

data as follows:

<programlisting>

<!-- $PostgreSQL: pgsql/doc/src/sgml/start.sgml,v 1.42 2006/09/16 00:30:15 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/start.sgml,v 1.43 2006/10/21 23:12:57 tgl Exp $ -->

<chapter id="tutorial-start">

<title>Getting Started</title>

<para>

Another response could be this:

<screen>

createdb: could not connect to database postgres: could not connect to server:

No such file or directory

createdb: could not connect to database postgres: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

</screen>

<para>

Another response could be this:

<screen>

createdb: could not connect to database postgres: FATAL: user "joe" does not

exist

createdb: could not connect to database postgres: FATAL: role "joe" does not exist

</screen>

where your own login name is mentioned. This will happen if the

administrator has not created a <productname>PostgreSQL</> user account

<para>

As an explanation for why this works:

<productname>PostgreSQL</productname> user names are separate

from operating system user accounts.If you connect to a

from operating system user accounts.When you connect to a

database, you can choose what

<productname>PostgreSQL</productname> user name to connect as;

if you don't, it will default to the same name as your current

That would mean you are a database superuser, which is most likely

the case if you installed <productname>PostgreSQL</productname>

yourself. Being a superuser means that you are not subject to

access controls. For thepurpose of this tutorialthis is not of

access controls. For thepurposes of this tutorialthat is not of

importance.

</para>