From 60ce9e9279376f6f99b10096370dec1e1e9e7596 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sun, 2 Sep 2001 23:27:50 +0000 Subject: New blood and fresh air for tutorial --- doc/src/sgml/advanced.sgml | 473 ++++++++++++------------ doc/src/sgml/arch.sgml | 88 ----- doc/src/sgml/filelist.sgml | 9 +- doc/src/sgml/info.sgml | 4 +- doc/src/sgml/query.sgml | 899 ++++++++++++++++++++++++++++++--------------- doc/src/sgml/start.sgml | 691 +++++++++++++++++----------------- doc/src/sgml/tutorial.sgml | 36 +- 7 files changed, 1231 insertions(+), 969 deletions(-) delete mode 100644 doc/src/sgml/arch.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index dc3731b989b..de0bf86e8bb 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -1,32 +1,203 @@ - - Advanced <productname>Postgres</productname> <acronym>SQL</acronym> Features - - - Having covered the basics of using - Postgres SQL to - access your data, we will now discuss those features of - Postgres that distinguish it from conventional data - managers. These features include inheritance, time - travel and non-atomic data values (array- and - set-valued attributes). - Examples in this section can also be found in - advance.sql in the tutorial directory. - (Refer to for how to use it.) - - - + + Advanced Features + + + Introduction + + + In the previous chapter we have covered the basics of using + SQL to store and access your data in a + PostgreSQL. We will now discuss some + more advanced features of SQL that simplify the + management and prevent loss or corruption of your data. Finally, + we will look at some PostgreSQL + extensions. + + + + This chapter will on occasion refer to examples found in to change or improve them, so it will be + of advantage if you have read that chapter. Some examples from + this chapter can also be found in + advanced.sql in the tutorial directory. This + file also contains some example data to load, which is not + repeated here. (Refer to for + how to use the file.) + + + + + + Views + + + view + + + + Refer back to the queries in . + Suppose the combined listing of weather records and city location + is of particular interest to your application, but you don't want + to type the query each time you need it. You can create a + view over the query, which gives a name to + the query that you can refer to like an ordinary table. + + +CREATE VIEW myview AS + SELECT city, temp_lo, temp_hi, prcp, date, location + FROM weather, cities + WHERE city = name; + +SELECT * FROM myview; + + + + + Making liberal use of views is a key aspect of good SQL database + design. Views allow you to encapsulate the details of the + structure of your tables, which may change as your application + evolves, behind consistent interfaces. + + + + Views can be used in almost any place a real table can be used. + Building views upon other views is not uncommon. + + + + + + Foreign Keys + + + foreign key + + + + referential integrity + + + + Recall the weather and the + cities tables from . Consider the following problem: You + want to make sure that no one can insert rows in the + weather table that do not have a matching + entry in the cities table. This is called + maintaining the referential integrity of + your data. In simplistic database systems this would be + implemented (if at all) by first looking at the + cities table to check if a matching record + exists, and then inserting or rejecting the new + weather records. This approach has a + number of problems and is very inconvenient, so + PostgreSQL can do this for you. + + + + The new declaration of the tables would look like this: + + +CREATE TABLE cities ( + name varchar(80) primary key, + location point +); + +CREATE TABLE weather ( + city varchar(80) references weather, + temp_lo int, + temp_hi int, + prcp real, + date date +); + + + + Now try inserting an invalid record: + + +INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28'); + + + +ERROR: <unnamed> referential integrity violation - key referenced from weather not found in cities + + + + + + The behavior of foreign keys can be finely tuned to your + application. We will not go beyond this simple example in this + tutorial and refer you to the Reference + Manual 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. + + + + + + Transactions + + This section needs to be written. + + + + + + + + Inheritance + + inheritance + + + + Inheritance is a concept from object-oriented databases. It opens + up interesting new possibilities of database design. + + + + Let's create two tables: A table cities + and a table capitals. Naturally, capitals + are also cities, so you want some way to show the capitals + implicitly when you list all cities. If you're really clever you + might invent some scheme like this: + + +CREATE TABLE capitals ( + name text, + population real, + altitude int, -- (in ft) + state char(2) +); + +CREATE TABLE non_capitals ( + name text, + population real, + altitude int -- (in ft) +); + +CREATE VIEW cities AS + SELECT name, population, altitude FROM capitals + UNION + SELECT name, population, altitude FROM non_capitals; + + + This works OK as far as querying goes, but it gets ugly when you + need to update several rows, to name one thing. + + - Let's create two tables. The capitals table contains - state capitals that are also cities. Naturally, the - capitals table should inherit from cities. + A better solution is this: - + CREATE TABLE cities ( name text, population real, @@ -36,245 +207,93 @@ CREATE TABLE cities ( CREATE TABLE capitals ( state char(2) ) INHERITS (cities); - - - In this case, a row of capitals inherits all - columns (name, population, and altitude) from its - parent, cities. The type of the column name is - text, a native Postgres - type for variable length - ASCII strings. The type of the column population is - real, a type for single precision - floating point numbers. State capitals have an extra - column, state, that shows their state. - In Postgres, - a table can inherit from zero or more other tables, - and a query can reference either all rows of a - table or all rows of a tables plus all of its - descendants. - - - - The inheritance hierarchy is a directed acyclic graph. - - + + + In this case, a row of capitals + inherits all columns (name, + population, and altitude) from its + parent, cities. The + type of the column name is + text, a native Postgres + type for variable length character strings. State capitals have + an extra column, state, that shows their state. In + PostgreSQL, a table can inherit from + zero or more other tables. For example, the following query finds the names of all cities, including state capitals, that are located at an altitude - over 500ft: + over 500 ft.: - + SELECT name, altitude FROM cities WHERE altitude > 500; - + which returns: - -+----------+----------+ -|name | altitude | -+----------+----------+ -|Las Vegas | 2174 | -+----------+----------+ -|Mariposa | 1953 | -+----------+----------+ -|Madison | 845 | -+----------+----------+ - + + name | altitude +-----------+---------- + Las Vegas | 2174 + Mariposa | 1953 + Madison | 845 +(3 rows) + 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 500 ft. or higher: - + SELECT name, altitude FROM ONLY cities WHERE altitude > 500; + -+----------+----------+ -|name | altitude | -+----------+----------+ -|Las Vegas | 2174 | -+----------+----------+ -|Mariposa | 1953 | -+----------+----------+ - + + name | altitude +-----------+---------- + Las Vegas | 2174 + Mariposa | 1953 +(2 rows) + - Here the ONLY before cities indicates that the query should - be run over only the cities table, and not tables below cities in the - inheritance hierarchy. Many of the commands that we - have already discussed -- SELECT, - UPDATE and DELETE -- - support this ONLY notation. + Here the ONLY before cities + indicates that the query should be run over only the + cities table, and not tables below + cities in the inheritance hierarchy. Many + of the commands that we have already discussed -- + SELECT, UPDATE and + DELETE -- support this ONLY + notation. - - - Deprecated - - In previous versions of Postgres, the - default was not to get access to child tables. This was found to - be error prone and is also in violation of SQL99. Under the old - syntax, to get the sub-tables you append "*" to the table name. - For example - -SELECT * from cities*; - - You can still explicitly specify scanning child tables by appending - "*", as well as explicitly specify not scanning child tables by - writing ONLY. But beginning in version 7.1, the default - behavior for an undecorated table name is to scan its child tables - too, whereas before the default was not to do so. To get the old - default behavior, set the configuration option - SQL_Inheritance to off, e.g., - -SET SQL_Inheritance TO OFF; - - or add a line in your postgresql.conf file. - - - - Non-Atomic Values + + Conclusion + - One of the tenets of the relational model is that the - columns of a table are atomic. - Postgres does not - have this restriction; columns can themselves contain - sub-values that can be accessed from the query - language. For example, you can create columns that - are arrays of base types. + PostgreSQL has many features not + touched upon in this tutorial introduction, which has been + oriented toward newer users of SQL. These + features are discussed in more detail in both the + User's Guide and the + Programmer's Guide. - - Arrays - - - Postgres allows columns of a - row to be defined - as fixed-length or variable-length multi-dimensional - arrays. Arrays of any base type or user-defined type - can be created. To illustrate their use, we first create a - table with arrays of base types. - - -CREATE TABLE SAL_EMP ( - name text, - pay_by_quarter integer[], - schedule text[][] -); - - - - - The above query will create a table named SAL_EMP with - a text string (name), a one-dimensional - array of integer - (pay_by_quarter), which represents the employee's - salary by quarter and a two-dimensional array of - text - (schedule), which represents the employee's weekly - schedule. Now we do some INSERTs; - note that when - appending to an array, we enclose the values within - braces and separate them by commas. If you know - C, - this is not unlike the syntax for initializing structures. - - -INSERT INTO SAL_EMP - VALUES ('Bill', - '{10000, 10000, 10000, 10000}', - '{{"meeting", "lunch"}, {}}'); - -INSERT INTO SAL_EMP - VALUES ('Carol', - '{20000, 25000, 25000, 25000}', - '{{"talk", "consult"}, {"meeting"}}'); - - - By default, Postgres uses the - "one-based" numbering - convention for arrays -- that is, an array of n elements - starts with array[1] and ends with array[n]. - Now, we can run some queries on SAL_EMP. First, we - show how to access a single element of an array at a - time. This query retrieves the names of the employees - whose pay changed in the second quarter: - - -SELECT name - FROM SAL_EMP - WHERE SAL_EMP.pay_by_quarter[1] <> - SAL_EMP.pay_by_quarter[2]; - -+------+ -|name | -+------+ -|Carol | -+------+ - - - - - This query retrieves the third quarter pay of all - employees: - - -SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP; - - -+---------------+ -|pay_by_quarter | -+---------------+ -|10000 | -+---------------+ -|25000 | -+---------------+ - - - - - We can also access arbitrary slices of an array (subarrays) - by specifying both lower and upper bounds for - each subscript. This query retrieves the first item on - Bill's schedule for the first two days of the week. - - -SELECT SAL_EMP.schedule[1:2][1:1] - FROM SAL_EMP - WHERE SAL_EMP.name = 'Bill'; - -+-------------------+ -|schedule | -+-------------------+ -|{{"meeting"},{""}} | -+-------------------+ - - - - - - - More Advanced Features - - Postgres has many features not touched - upon in this - tutorial introduction, which has been oriented toward newer users of - SQL. - These are discussed in more detail in both the User's and - Programmer's Guides. + If you feel you need more introductory material, please visit the + PostgreSQL web + site for links to more resources. - diff --git a/doc/src/sgml/arch.sgml b/doc/src/sgml/arch.sgml deleted file mode 100644 index d420fab6dd9..00000000000 --- a/doc/src/sgml/arch.sgml +++ /dev/null @@ -1,88 +0,0 @@ - - Architecture - - -<ProductName>Postgres</ProductName> Architectural Concepts - - - Before we begin, you should understand the basic - Postgres system architecture. Understanding how the - parts of Postgres interact will make the next chapter - somewhat clearer. - In database jargon, Postgres uses a simple "process - per-user" client/server model. A Postgres session - consists of the following cooperating Unix processes (programs): - - - - - - A supervisory daemon process (postmaster), - - - - - the user's frontend application (e.g., the psql program), and - - - - - the one or more backend database servers (the postgres process itself). - - - - - - A single postmaster manages a given collection of - databases on a single host. Such a collection of - databases is called a cluster (of databases). Frontend - applications that wish to access a given database - within a cluster make calls to the library. - The library sends user requests over the network to the - postmaster (), - which in turn starts a new backend server process - -
-How a connection is established - -
- - and connects the - frontend process to the new server. From - that point on, the frontend process and the backend - server communicate without intervention by the - postmaster. Hence, the postmaster is always running, waiting - for requests, whereas frontend and backend processes - come and go. - - - - The libpq library allows a single - frontend to make multiple connections to backend processes. - However, the frontend application is still a - single-threaded process. Multithreaded frontend/backend - connections are not currently supported in libpq. - One implication of this architecture is that the - postmaster and the backend always run on the same - machine (the database server), while the frontend - application may run anywhere. You should keep this - in mind, - because the files that can be accessed on a client - machine may not be accessible (or may only be accessed - using a different filename) on the database server - machine. - - - - You should also be aware that the postmaster and - postgres servers run with the user-id of the Postgres - "superuser." Note that the Postgres superuser does not - have to be a special user (e.g., a user named - "postgres"). Furthermore, the Postgres superuser - should - definitely not be the Unix superuser ("root")! In any - case, all files relating to a database should belong to - this Postgres superuser. - - - diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 4b613db8e5b..7f81f6cdca6 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -1,7 +1,8 @@ - + + @@ -12,12 +13,12 @@ - - - + + + diff --git a/doc/src/sgml/info.sgml b/doc/src/sgml/info.sgml index 195e56e7f36..608611777ad 100644 --- a/doc/src/sgml/info.sgml +++ b/doc/src/sgml/info.sgml @@ -1,5 +1,5 @@ @@ -13,7 +13,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/info.sgml,v 1.11 2001/02/03 19:03:26 petere Tutorial - An introduction for new users. Does not cover advanced features. + An informal introduction for new users diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 82c4ffe697f..04fcce1985d 100644 --- a/doc/src/sgml/query.sgml +++ b/doc/src/sgml/query.sgml @@ -1,102 +1,106 @@ - - The Query Language - - - The Postgres query language is a variant of - the SQL standard. It - has many extensions to SQL such as an - extensible type system, - inheritance, functions and production rules. These are - features carried over from the original - Postgres query - language, PostQuel. - This section provides an overview - of how to use Postgres - SQL to perform simple operations. - This manual is only intended to give you an idea of our - flavor of SQL and is in no way a complete tutorial on - SQL. Numerous books have been written on - SQL92, including - and - . - You should be aware that some language features - are extensions to the standard. - - - - Interactive Monitor - - - In the examples that follow, we assume that you have - created the mydb database as described in the previous - subsection and have started psql. - Examples in this manual can also be found in source distribution - in the directory src/tutorial/. Refer to the - README file in that directory for how to use them. To - start the tutorial, do the following: + + The <acronym>SQL</acronym> Language + + + Introduction + + + This chapter provides an overview of how to use + SQL to perform simple operations. This + tutorial is only intended to give you an introduction and is in no + way a complete tutorial on SQL. Numerous books + have been written on SQL92, including and . You should be aware that some language + features are extensions to the standard. + + + + In the examples that follow, we assume that you have created a + database named mydb, as described in the previous + chapter, and have started psql. + + + + Examples in this manual can also be found in source distribution + in the directory src/tutorial/. Refer to the + README file in that directory for how to use + them. To start the tutorial, do the following: -$ cd .../src/tutorial +$ cd ..../src/tutorial $ psql -s mydb -Welcome to the POSTGRESQL interactive sql monitor: - Please read the file COPYRIGHT for copyright terms of POSTGRESQL - - type \? for help on slash commands - type \q to quit - type \g or terminate with semicolon to execute query - You are currently connected to the database: postgres +... mydb=> \i basics.sql + + The \i command reads in commands from the + specified files. The -s option puts you in + single step mode which pauses before sending a query to the + server. The commands used in this section are in the file + basics.sql. + + + + + Concepts - The \i command read in queries from the specified - files. The -s option puts you in single step mode which - pauses before sending a query to the backend. Queries - in this section are in the file basics.sql. + relational database + hierarchical database + object-oriented database + relation + table + + PostgreSQL is a relational + database management system (RDBMS). + That means it is a system for managing data stored in + relations. Relation is essentially a + mathematical term for table. The notion of + storing data in tables is so commonplace today that it might + seem inherently obvious, but there are a number of other ways of + organizing databases. Files and directories on Unix-like + operating systems form an example of a hierarchical database. A + more modern development is the object-oriented database. - psql - has a variety of \d commands for showing system information. - Consult these commands for more details; - for a listing, type \? at the psql prompt. + row + column + + Each table is a named collection of rows. + Each row has the same set of named columns, + and each column is of a specific data type. Whereas columns have + a fixed order in each row, it is important to remember that SQL + does not guarantee the order of the rows within the table in any + way (unless they are explicitly sorted). - - - - Concepts - The fundamental notion in Postgres is - that of a table, which is a named - collection of rows. Each row has the same - set of named columns, and each column is of - a specific type. Furthermore, each row has a permanent - object identifier (OID) - that is unique throughout the database cluster. Historially, - tables have been called classes in - Postgres, rows are object instances, - and columns are attributes. This makes sense if you consider the - object-relational aspects of the database system, but in this - manual we will use the customary SQL - terminology. As previously discussed, - tables are grouped into databases, and a collection of databases - managed by a single postmaster process - constitutes a database cluster. + cluster + + Tables are grouped into databases, and a collection of databases + managed by a single PostgreSQL server + instance constitutes a database cluster. - + + Creating a New Table + + CREATE TABLE + + You can create a new table by specifying the table name, along with all column names and their types: @@ -110,39 +114,82 @@ CREATE TABLE weather ( date date ); + + You can enter this into psql with the line + breaks. psql will recognize that the command + is not terminated until the semicolon. + + + + White space (i.e., spaces, tabs, and newlines) may be used freely + in SQL commands. That means you can type the command aligned + differently than above, or even all on one line. Two dashes + (--) introduce comments. + Whatever follows them is ignored up to the end of the line. SQL + is also case insensitive about key words and identifiers, except + when identifiers are double-quoted to preserve the case (not done + above). + + + + varchar(80) specifies a data type that can store + arbitrary character strings up to 80 characters in length. + int is the normal integer type. real is + a type for storing single precision floating point numbers. + date should be self-explanatory. (Yes, the column of + type date is also named date. + This may be convenient or confusing -- you choose.) - Note that both keywords and identifiers are case-insensitive; - identifiers can preserve case by surrounding them with - double-quotes as allowed - by SQL92. - Postgres SQL - supports the usual + PostgreSQL supports the usual SQL types int, - float, real, smallint, -char(N), - varchar(N), date, time, - and timestamp, as well as other types of general utility and - a rich set of geometric types. As we will - see later, Postgres can be customized - with an - arbitrary number of - user-defined data types. Consequently, type names are - not syntactical keywords, except where required to support special - cases in the SQL92 standard. - So far, the Postgres - CREATE command - looks exactly like - the command used to create a table in a traditional - relational system. However, we will presently see that - tables have properties that are extensions of the - relational model. + smallint, real, double + precision, char(N), + varchar(N), date, + time, timestamp, and + interval as well as other types of general utility + and a rich set of geometric types. + PostgreSQL can be customized with an + arbitrary number of user-defined data types. Consequently, type + names are not syntactical keywords, except where required to + support special cases in the SQL standard. + + + + The second example will store cities and their associated + geographical location: + +CREATE TABLE cities ( + name varchar(80), + location point +); + + The point type is such a + PostgreSQL-specific data type. + + + + + DROP TABLE + + + Finally, it should be mentioned that if you don't need a table any + longer or want to recreate it differently you can remove it using + the following command: + +DROP TABLE tablename; + - - Populating a Table with Rows + + + Populating a Table With Rows + + + INSERT + The INSERT statement is used to populate a table with @@ -151,129 +198,184 @@ CREATE TABLE weather ( INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); + + Note that all data types use rather obvious input formats. The + date column is actually quite flexible in what it + accepts, but for this tutorial we will stick to the unambiguous + format shown here. - You can also use COPY to load large - amounts of data from flat (ASCII) files. - This is usually faster because the data is read (or written) as a - single atomic - transaction directly to or from the target table. An example would be: + The point type requires a coordinate pair as input, + as shown here: + +INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)'); + + + + The syntax used so far requires you to remember the order of the + columns. An alternative syntax allows you to list the columns + explicitly: -COPY weather FROM '/home/user/weather.txt' USING DELIMITERS '|'; +INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) + VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29'); + + You can also list the columns in a different order if you wish or + even omit some columns, e.g., unknown precipitation: + +INSERT INTO weather (date, city, temp_hi, temp_lo) + VALUES ('1994-11-29', 'Hayward', 54, 37); + + Many developers consider explicitly listing the columns better + style than relying on the order implicitly. + + + + Please enter all the commands shown above so you have some data to + work with in the following sections. + + + + + COPY + + + You could also have used COPY to load large + amounts of data from flat text files. This is usually faster + because the COPY is optimized for this + application while allowing less flexibility than + INSERT. An example would be: + + +COPY weather FROM '/home/user/weather.txt'; where the path name for the source file must be available to the - backend server - machine, not the client, since the backend server reads the file directly. + backend server machine, not the client, since the backend server + reads the file directly. You can read more about the + COPY command in the Reference + Manual. - + + Querying a Table - The weather table can be queried with normal relational - selection and projection queries. A SQL - SELECT - statement is used to do this. The statement is divided into - a target list (the part that lists the columns to be - returned) and a qualification (the part that specifies - any restrictions). For example, to retrieve all the - rows of weather, type: + query + SELECT + + To retrieve data from a table it is + queried. An SQL + SELECT statement is used to do this. The + statement is divided into a select list (the part that lists the + columns to be returned), a table list (the part that lists the + tables from which to retrieve the data), and an optional + qualification (the part that specifies any restrictions). For + example, to retrieve all the rows of + weather, type: SELECT * FROM weather; + (where * means all columns) and + the output should be: + + city | temp_lo | temp_hi | prcp | date +---------------+---------+---------+------+------------ + San Francisco | 46 | 50 | 0.25 | 1994-11-27 + San Francisco | 43 | 57 | 0 | 1994-11-29 + Hayward | 37 | 54 | | 1994-11-29 +(3 rows) + + - and the output should be: - -+--------------+---------+---------+------+------------+ -|city | temp_lo | temp_hi | prcp | date | -+--------------+---------+---------+------+------------+ -|San Francisco | 46 | 50 | 0.25 | 1994-11-27 | -+--------------+---------+---------+------+------------+ -|San Francisco | 43 | 57 | 0 | 1994-11-29 | -+--------------+---------+---------+------+------------+ -|Hayward | 37 | 54 | | 1994-11-29 | -+--------------+---------+---------+------+------------+ - - You may specify any arbitrary expressions in the target list. For + + You may specify any arbitrary expressions in the target list. For example, you can do: SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; + This should give: + + city | temp_avg | date +---------------+----------+------------ + San Francisco | 48 | 1994-11-27 + San Francisco | 50 | 1994-11-29 + Hayward | 45 | 1994-11-29 +(3 rows) + + Notice how the AS clause is used to relabel the + output column. (It is optional.) - Arbitrary Boolean operators - (AND, OR and - NOT) are - allowed in the qualification of any query. For example, + Arbitrary Boolean operators (AND, + OR, and NOT) are allowed in + the qualification of a query. For example, the following + retrieves the weather of San Francisco on rainy days: SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0; -results in: - -+--------------+---------+---------+------+------------+ -|city | temp_lo | temp_hi | prcp | date | -+--------------+---------+---------+------+------------+ -|San Francisco | 46 | 50 | 0.25 | 1994-11-27 | -+--------------+---------+---------+------+------------+ - + Result: + + city | temp_lo | temp_hi | prcp | date +---------------+---------+---------+------+------------ + San Francisco | 46 | 50 | 0.25 | 1994-11-27 +(1 row) + - As a final note, you can specify that the results of a - select can be returned in a sorted order - or with duplicate rows removed. + ORDER BY + DISTINCT + duplicate + + As a final note, you can request that the results of a select can + be returned in sorted order or with duplicate rows removed. (Just + to make sure the following won't confuse you, + DISTINCT and ORDER BY can be + used separately.) SELECT DISTINCT city FROM weather ORDER BY city; - - - - - Redirecting SELECT Queries - - - Any SELECT query can be redirected to a new table - -SELECT * INTO TABLE temp FROM weather; - - - - This forms an implicit CREATE command, creating a new - table temp with the column names and types specified - in the target list of the SELECT INTO command. We can - then, of course, perform any operations on the resulting - table that we can perform on other tables. + + city +--------------- + Hayward + San Francisco +(2 rows) + - + + Joins Between Tables + + join + + - Thus far, our queries have only accessed one table at a - time. Queries can access multiple tables at once, or - access the same table in such a way that multiple - rows of the table are being processed at the same - time. A query that accesses multiple rows of the - same or different tables at one time is called a join - query. - As an example, say we wish to find all the records that - are in the temperature range of other records. In - effect, we need to compare the temp_lo and temp_hi - columns of each WEATHER row to the temp_lo and - temp_hi columns of all other WEATHER columns. + Thus far, our queries have only accessed one table at a time. + Queries can access multiple tables at once, or access the same + table in such a way that multiple rows of the table are being + processed at the same time. A query that accesses multiple rows + of the same or different tables at one time is called a + join query. As an example, say you wish to + list all the weather records together with the location of the + associated city. In effect, we need to compare the city column of + each row of the weather table with the name column of all rows in + the cities table. This is only a conceptual model. The actual join may @@ -281,102 +383,189 @@ SELECT * INTO TABLE temp FROM weather; to the user. - - We can do this with the following query: + This would be accomplished by the following query: -SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, - W2.city, W2.temp_lo AS low, W2.temp_hi AS high - FROM weather W1, weather W2 - WHERE W1.temp_lo < W2.temp_lo - AND W1.temp_hi > W2.temp_hi; +SELECT * + FROM weather, cities + WHERE city = name; + -+--------------+-----+------+---------------+-----+------+ -|city | low | high | city | low | high | -+--------------+-----+------+---------------+-----+------+ -|San Francisco | 43 | 57 | San Francisco | 46 | 50 | -+--------------+-----+------+---------------+-----+------+ -|San Francisco | 37 | 54 | San Francisco | 46 | 50 | -+--------------+-----+------+---------------+-----+------+ - + + city | temp_lo | temp_hi | prcp | date | name | location +---------------+---------+---------+------+------------+---------------+----------- + San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) + San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) +(2 rows) + - - - The semantics of such a join are - that the qualification - is a truth expression defined for the Cartesian product of - the tables indicated in the query. For those rows in - the Cartesian product for which the qualification is true, - Postgres computes and returns the - values specified in the target list. - Postgres SQL - does not assign any meaning to - duplicate values in such expressions. - This means that Postgres - sometimes recomputes the same target list several times; - this frequently happens when Boolean expressions are connected - with an "or". To remove such duplicates, you must use - the SELECT DISTINCT statement. - - - In this case, both W1 and - W2 are surrogates for a - row of the table weather, and both range over all - rows of the table. (In the terminology of most - database systems, W1 and W2 - are known as range variables.) - A query can contain an arbitrary number of - table names and surrogates. + Observe two things about the result set: + + + + There is no result row for the city of Hayward. This is + because there is no matching entry in the + cities table for Hayward, so the join + cannot process the rows in the weather table. We will see + shortly how this can be fixed. + + + + + + There are two columns containing the city name. This is + correct because the lists of columns of the + weather and the + cities tables are concatenated. In + practice this is undesirable, though, so you will probably want + to list the output columns explicitly rather than using + *: + +SELECT city, temp_lo, temp_hi, prcp, date, location + FROM weather, cities + WHERE city = name; + + + + - - - Updates + + Exercise: + + + Attempt to find out the semantics of this query when the + WHERE clause is omitted. + + - You can update existing rows using the - UPDATE command. - Suppose you discover the temperature readings are - all off by 2 degrees as of Nov 28, you may update the - data as follow: + 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: -UPDATE weather - SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 - WHERE date > '1994-11-28'; +SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location + FROM weather, cities + WHERE cities.name = weather.city; - - - - Deletions - Deletions are performed using the DELETE command: + Join queries of the kind seen thus far can also be written in this + alternative form: + -DELETE FROM weather WHERE city = 'Hayward'; +SELECT * + FROM weather INNER JOIN cities ON (weather.city = cities.name); - All weather recording belonging to Hayward are removed. - One should be wary of queries of the form + This syntax is not as commonly used as the one above, but we show + it here to help you understand the following topics. + + + + joinouter + + 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 + weather table and for each row to find the + matching cities row. If no matching row is + found we want some empty values to be substituted + for the cities table's columns. This kind + of query is called an outer join. (The + joins we have seen to far are inner joins.) The command looks + like this: + -DELETE FROM tablename; +SELECT * + FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name); + + city | temp_lo | temp_hi | prcp | date | name | location +---------------+---------+---------+------+------------+---------------+----------- + Hayward | 37 | 54 | | 1994-11-29 | | + San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) + San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) +(3 rows) - Without a qualification, DELETE will simply - remove all rows from the given table, leaving it - empty. The system will not request confirmation before - doing this. + In particular, this query is a left outer + join because the table mentioned on the left of the + join operator will have each of its rows in the output at least + once, whereas the table on the right will only have those rows + output that match some row of the left table, and will have empty + values substituted appropriately. + + + + Exercise: + + + There are also right outer joins and full outer joins. Try to + find out what those do. + + + + + joinself + aliasfor table name in query + + We can also join a table against itself. This is called a + self join. As an example, suppose we wish + to find all the weather records that are in the temperature range + of other weather records. So we need to compare the + temp_lo and temp_hi columns of + each weather row to the + temp_lo and + temp_hi columns of all other + weather rows. We can do this with the + following query: + + +SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, + W2.city, W2.temp_lo AS low, W2.temp_hi AS high + FROM weather W1, weather W2 + WHERE W1.temp_lo < W2.temp_lo + AND W1.temp_hi > W2.temp_hi; + + city | low | high | city | low | high +---------------+-----+------+---------------+-----+------ + San Francisco | 43 | 57 | San Francisco | 46 | 50 + Hayward | 37 | 54 | San Francisco | 46 | 50 +(2 rows) + + + Here we have relabeled the weather table as W1 and + W2 to be able to distinguish the left and right side + of the join. You can also use these kinds of aliases in other + queries to save some typing, e.g.: + +SELECT * + FROM weather w, cities c + WHERE w.city = c.name; + + You will encounter this style of abbreviating quite frequently. - - Using Aggregate Functions + + + Aggregate Functions + + + aggregate + + average + count + max + min + sum + Like most other relational database products, PostgreSQL supports aggregate functions. @@ -387,95 +576,215 @@ DELETE FROM tablename; min (minimum) over a set of rows. - - It is important to understand the interaction between aggregates and - SQL's WHERE and HAVING clauses. - The fundamental difference between WHERE and - HAVING is this: WHERE selects - input rows before groups and aggregates are computed (thus, it controls - which rows go into the aggregate computation), whereas - HAVING selects group rows after groups and - aggregates are computed. Thus, the - WHERE clause may not contain aggregate functions; - it makes no sense to try to use an aggregate to determine which rows - will be inputs to the aggregates. On the other hand, - HAVING clauses always contain aggregate functions. - (Strictly speaking, you are allowed to write a HAVING - clause that doesn't use aggregates, but it's wasteful; the same condition - could be used more efficiently at the WHERE stage.) - - As an example, we can find the highest low-temperature reading anywhere with - + SELECT max(temp_lo) FROM weather; - + + + + max +----- + 46 +(1 row) + + + + + subquery If we want to know what city (or cities) that reading occurred in, we might try - -SELECT city FROM weather WHERE temp_lo = max(temp_lo); - + +SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG + but this will not work since the aggregate - max can't be used in - WHERE. However, as is often the case the query can be - restated to accomplish the intended result; here by using a - subselect: + max cannot be used in the + WHERE clause. However, as is often the case + the query can be restated to accomplish the intended result; here + by using a subquery: - + SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather); - + + + + city +--------------- + San Francisco +(1 row) + - This is OK because the sub-select is an independent computation that - computes its own aggregate separately from what's happening in the outer - select. + This is OK because the sub-select is an independent computation + that computes its own aggregate separately from what is happening + in the outer select. - Aggregates are also very useful in combination with - GROUP BY clauses. For example, we can get the - maximum low temperature observed in each city with + GROUP BY + HAVING + + Aggregates are also very useful in combination with GROUP + BY clauses. For example, we can get the maximum low + temperature observed in each city with - + SELECT city, max(temp_lo) FROM weather GROUP BY city; - + + + + city | max +---------------+----- + Hayward | 37 + San Francisco | 46 +(2 rows) + which gives us one output row per city. We can filter these grouped - rows using HAVING: + rows using HAVING: - + SELECT city, max(temp_lo) FROM weather GROUP BY city - HAVING min(temp_lo) < 0; - + HAVING max(temp_lo) < 40; + + + + city | max +---------+----- + Hayward | 37 +(1 row) + which gives us the same results for only the cities that have some - below-zero readings. Finally, if we only care about cities whose - names begin with "P", we might do + below-forty readings. Finally, if we only care about cities whose + names begin with S, we might do - + SELECT city, max(temp_lo) FROM weather - WHERE city like 'P%' + WHERE city LIKE 'S%' GROUP BY city - HAVING min(temp_lo) < 0; - + HAVING max(temp_lo) < 40; + + - Note that we can apply the city-name restriction in - WHERE, since it needs no aggregate. This is - more efficient than adding the restriction to HAVING, + + It is important to understand the interaction between aggregates and + SQL's WHERE and HAVING clauses. + The fundamental difference between WHERE and + HAVING is this: WHERE selects + input rows before groups and aggregates are computed (thus, it controls + which rows go into the aggregate computation), whereas + HAVING selects group rows after groups and + aggregates are computed. Thus, the + WHERE clause must not contain aggregate functions; + it makes no sense to try to use an aggregate to determine which rows + will be inputs to the aggregates. On the other hand, + HAVING clauses always contain aggregate functions. + (Strictly speaking, you are allowed to write a HAVING + clause that doesn't use aggregates, but it's wasteful; the same condition + could be used more efficiently at the WHERE stage.) + + + + Note that we can apply the city name restriction in + WHERE, since it needs no aggregate. This is + more efficient than adding the restriction to HAVING, because we avoid doing the grouping and aggregate calculations - for all rows that fail the WHERE check. + for all rows that fail the WHERE check. + + + + + + Updates + + + UPDATE + + + + You can update existing rows using the + UPDATE command. + Suppose you discover the temperature readings are + all off by 2 degrees as of November 28, you may update the + data as follow: + + +UPDATE weather + SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 + WHERE date > '1994-11-28'; + + + + + Look at the new state of the data: + +SELECT * FROM weather; + + city | temp_lo | temp_hi | prcp | date +---------------+---------+---------+------+------------ + San Francisco | 46 | 50 | 0.25 | 1994-11-27 + San Francisco | 41 | 55 | 0 | 1994-11-29 + Hayward | 35 | 52 | | 1994-11-29 +(3 rows) + + + + Deletions + + + DELETE + + + + Suppose you are no longer interested in the weather of Hayward, + then you can do the following to delete those rows from the table. + Deletions are performed using the DELETE + command: + +DELETE FROM weather WHERE city = 'Hayward'; + + + All weather recording belonging to Hayward are removed. + + +SELECT * FROM weather; + + + + city | temp_lo | temp_hi | prcp | date +---------------+---------+---------+------+------------ + San Francisco | 46 | 50 | 0.25 | 1994-11-27 + San Francisco | 41 | 55 | 0 | 1994-11-29 +(2 rows) + + + + + One should be wary of queries of the form + +DELETE FROM tablename; + + + Without a qualification, DELETE will simply + remove all rows from the given table, leaving it + empty. The system will not request confirmation before + doing this. + + + - + Getting Started - + + Installation + + + Before you can use PostgreSQL you need + to install it, of course. It is possible that + PostgreSQL is already installed at your + site, either because it was included in your operating system + distribution or because the system administrator already installed + it. If that is the case, you should obtain information from the + operating system documentation or your system administrator about + how to access PostgreSQL. + + - How to begin work with Postgres for a new user. + If you are not sure whether PostgreSQL + is already available or whether you can use it for your + experimentation then you can install it yourself. Doing so is not + hard and it can be a good exercise. + PostgreSQL can be installed by any + unprivileged user, no superuser (root) + access is required. - - - - Some of the steps required to use Postgres - can be performed by any Postgres user, and some must be done by - the site database administrator. This site administrator - is the person who installed the software, created - the database directories and started the - postmaster - process. This person does not have to be the Unix - superuser ("root") - or the computer system administrator; a person can install and use - Postgres without any special accounts or - privileges. - - - - If you are installing Postgres yourself, then - refer to the Administrator's Guide for instructions on - installation, and return - to this guide when the installation is complete. - - - - Throughout this manual, any examples that begin with - the character "%" are commands that should be typed - at the Unix shell prompt. Examples that begin with the - character "*" are commands in the Postgres query - language, Postgres SQL. - - - - Setting Up Your Environment - This section discusses how to set up - your own environment so that you can use frontend - applications. We assume Postgres has - already been - successfully installed and started; refer to the Administrator's Guide - and the installation notes - for how to install Postgres. + If you are installing PostgreSQL + yourself, then refer to the Administrator's + Guide for instructions on installation, and return to + this guide when the installation is complete. Be sure to follow + closely the section about setting up the appropriate environment + variables. - Postgres is a client/server - application. As a user, - you only need access to the client portions of the installation - (an example - of a client application is the interactive monitor - psql). - For simplicity, - we will assume that Postgres has been - installed in the - directory /usr/local/pgsql. Therefore, wherever - you see the directory /usr/local/pgsql you should - substitute the name of the directory where - Postgres is - actually installed. - All Postgres commands are installed in - the directory - /usr/local/pgsql/bin. Therefore, you should add - this directory to your shell command path. If you use - a variant of the Berkeley C shell, such as csh or tcsh, - you would add - - -% set path = ( /usr/local/pgsql/bin path ) - - - in the .login file in your home directory. - If you use - a variant of the Bourne shell, such as sh, ksh, or - bash, then you would add - - -% PATH=/usr/local/pgsql/bin:$PATH -% export PATH - - - to the .profile file in your home directory. - From now on, we will assume that you have added the - Postgres bin directory to your path. - In addition, we - will make frequent reference to setting a shell - variable or setting an environment - variable throughout - this document. If you did not fully understand the - last paragraph on modifying your search path, you - should consult the Unix manual pages that describe your - shell before going any further. + If your site administrator has not set things up in the default + way, you may have some more work to do. For example, if the + database server machine is a remote machine, you will need to set + the PGHOST environment variable to the name of the + database server machine. The environment variable + PGPORT may also have to be set. The bottom line is + this: if you try to start an application program and it complains + that it cannot connect to the database, you should consult your + site administrator or, if that is you, the documentation to make + sure that your environment is properly set up. If you did not + understand the preceding paragraph then read the next section. + + + + + Architectural Fundamentals - If your site administrator has not set things up in the - default way, you may have some more work to do. For example, if - the database - server machine is a remote machine, you - will need to set the PGHOST environment - variable to the name - of the database server machine. The environment variable - PGPORT may also have to be set. - The bottom line is this: if - you try to start an application program and it complains - that it cannot connect to the postmaster, - you should immediately consult your site administrator to make - sure that your - environment is properly set up. + Before we proceed, you should understand the basic + PostgreSQL system architecture. + Understanding how the parts of + PostgreSQL interact will make the next + chapter somewhat clearer. + + In database jargon, PostgreSQL uses a + client/server model. A PostgreSQL + session consists of the following cooperating processes + (programs): + + + + + A server process, which manages the database files, accepts + connections to the database from client applications, and + performs actions on the database on behalf of the clients. The + database server program is called + postmaster. + postmaster + + + + + + The user's client (frontend) application that wants to perform + database operations. Client applications can be very diverse + in nature: They could be a text-oriented tool, a graphical + application, a web server that accesses the database to + display web pages, or a specialized database maintenance tool. + Some client applications are supplied with the + PostgreSQL distribution, most are + developed by users. + + + + + + + + As is typical of client/server applications, the client and the + server can be on different hosts. In that case they communicate + over a TCP/IP network connection. You should keep this in mind, + because the files that can be accessed on a client machine might + not be accessible (or might only be accessed using a different + file name) on the database server machine. + + + + The PostgreSQL server can handle + multiple concurrent connections from clients. For that purpose it + starts (forks) a new process for each connection. + From that point on, the client and the new server process + communicate without intervention by the original + postmaster process. Thus, the + postmaster is always running, waiting for + client connections, whereas client and associated server processes + come and go. (All of this is of course invisible to the user. We + only mention it here for completeness.) + - - Starting the Interactive Monitor (psql) + + + Creating a Database + + + database + creating + + + + createdb + - Assuming that your site administrator has properly - started the postmaster process and - authorized you to - use the database, you (as a user) may begin to start up - applications. As previously mentioned, you should add - /usr/local/pgsql/bin to your shell search path. - In most cases, this is all you should have to do in - terms of preparation. + The first test to see whether you can access the database server + is to try to create a database. A running + PostgreSQL server can manage many + databases. Typically, a separate database is used for each + project or for each user. - Two different styles of connections - are supported. The site administrator will have chosen to allow - TCP/IP network connections - or will have restricted database access to local (same-machine) - socket connections only. - These choices become significant if you encounter problems in - connecting to a database, since you will want to confirm that you - are choosing an allowed connection option. + Possibly, your site administrator has already created a database + for your use. He should have told you what the name of your + database is. In this case you can omit this step and skip ahead + to the next section. - If you get the following error message from a - Postgres - command (such as psql or - createdb): + To create a new database, in this example named + mydb, you use the following command: + +$ createdb mydb + + This should produce as response: + +CREATE DATABASE + + Is so, this step was successful and you can skip over the + remainder of this section. + - -% psql template1 + + If you see a message similar to + +createdb: command not found + + then PostgreSQL was not installed properly. Either it was not + installed at all or the search path was not set correctly. Try + calling the command with an absolute path instead: + +$ /usr/local/pgsql/bin/createdb + + The path at your site might be different. Contact your site + administrator or check back in the installation instructions to + correct the situation. + + + + Another response could be this: + psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? - +createdb: database creation failed + + This means that the server was not started, or it was not started + where createdb expected it. Again, check the + installation instructions or consult the administrator. + - or + + If you do not have the privileges required to create a database, + you will see the following: + +ERROR: CREATE DATABASE: permission denied +createdb: database creation failed + + Not every user has authorization to create new databases. If + PostgreSQL refuses to create databases + for you then the site administrator needs to grant you permission + to create databases. Consult your site administrator if this + occurs. If you installed PostgreSQL + yourself then you should log in for the purposes of this tutorial + under the user account that you started the server as. + + + + As an explanation for why this works: + PostgreSQL user names are separate + from operating system user accounts. If you connect to a + database, you can choose what + PostgreSQL user name to connect as; + if you don't, it will default to the same name as your current + operating system account. As it happens, there will always be a + PostgreSQL user account that has the + same name as the operating system user that started the server, + and it also happens that that user always has permission to + create databases. Instead of logging in as that user you can + also specify the option everywhere to select + a PostgreSQL user name to connect as. + + + + + + You can also create databases with other names. + PostgreSQL allows you to create any + number of databases at a given site. Database names must have an + alphabetic first character and are limited to 32 characters in + length. A convenient choice is to create a database with the same + name as your current user name. Many tools assume that database + name as the default, so it can save you some typing. To create + that database, simply type + +$ createdb + + + + + If you don't want to use your database anymore you can remove it. + For example, if you are the owner (creator) of the database + mydb, you can destroy it using the following + command: + +$ dropdb mydb + + (In this case, the database name does not default to the user + account name. You always need to specify it.) This action + physically removes all files associated with the database and + cannot be undone, so this should only be done with a great deal of + forethought. + + - -% psql -h localhost template1 -psql: could not connect to server: Connection refused - Is the server running on host localhost and accepting - TCP/IP connections on port 5432? - - it is usually because + + Accessing a Database + + + psql + + + + Once you have created a database, you can access it by: + + + + + Running the PostgreSQL interactive + terminal program, called psql, which allows you + to interactively enter, edit, and execute + SQL commands. + + - - the postmaster is not running, - or + Using an existing graphical frontend tool like + PgAccess or + ApplixWare (via + ODBC) to create and manipulate a database. + These possibilities are not covered in this tutorial. - you are attempting to connect to the wrong server host. + Writing a custom application, using one of the several + available language bindings. These possibilities are discussed + further in The PostgreSQL Programmer's + Guide. + + You probably want to start up psql, to try out + the examples in this tutorial. It can be activated for the + mydb database by typing the command: + +$ psql mydb + + If you leave off the database name then it will default to your + user account name. You already discovered this scheme in the + previous section. - If you get the following error message: - - -FATAL 1:Feb 17 23:19:55:process userid (2360) != database owner (268) - - - it means that the site administrator started the - postmaster - as the wrong user. Tell him to restart it as - the Postgres superuser. + In psql, you will be greeted with the following + message: + +Welcome to psql, the PostgreSQL interactive terminal. + +Type: \copyright for distribution terms + \h for help with SQL commands + \? for help on internal slash commands + \g or terminate with semicolon to execute query + \q to quit + +mydb=> + + superuser + The last line could also be + +mydb=# + + That would mean you are a database superuser, which is most likely + the case if you installed PostgreSQL + yourself. Being a superuser means that you are not subject to + access controls. For the purpose of this tutorial this is not of + importance. - - - Managing a Database + + If you have encountered problems starting psql + then go back to the previous section. The diagnostics of + psql and createdb are + similar, and if the latter worked the former should work as well. + - Now that Postgres is up and running we - can create some - databases to experiment with. Here, we describe the - basic commands for managing a database. + The last line printed out by psql is the + prompt, and it indicates that psql is listening + to you and that you can type SQL queries into a + workspace maintained by psql. Try out these + commands: + version + +mydb=> SELECT version(); + version +---------------------------------------------------------------- + PostgreSQL 7.2devel on i586-pc-linux-gnu, compiled by GCC 2.96 +(1 row) + +mydb=> SELECT current_date; + date +------------ + 2001-08-31 +(1 row) + +mydb=> SELECT 2 + 2; + ?column? +---------- + 4 +(1 row) + - Most Postgres - applications assume that the database name, if not specified, is - the same as the name on your computer - account. + The psql program has a number of internal + commands that are not SQL commands. They begin the backslash + character, \. Some of these + commands were already listed in the welcome message. For example, + you can get help on the syntax of various + PostgreSQL SQL + commands by typing: + +mydb=> \h + - If your database administrator has set up your account without - database creation privileges, - then she should have told you what the name of your database is. If - this is the case, then you - can skip the sections on creating and destroying databases. + To get out of psql, type + +mydb=> \q + + and psql will quit and return you to your + command shell. (For more internal commands, type + \? at the psql prompt.) The + full capabilities of psql are documented in the + Reference Manual. If PostgreSQL is + installed correctly you can also type man psql + at the operating system shell prompt to see the documentation. In + this tutorial we will not use these features explicitly, but you + can use them yourself when you see it fit. - - Creating a Database - - - Let's say you want to create a database named - mydb. - You can do this with the following command: - -% createdb mydb - - - - - If you do not have the privileges required to create a database, - you will see - the following: - -% createdb mydb -NOTICE:user "your username" is not allowed to create/destroy databases -createdb: database creation failed on mydb. - - - - - Postgres allows you to create any - number of databases - at a given site and you automatically become the - database administrator of the database you just created. - Database names must have an alphabetic first - character and are limited to 32 characters in length. - Not every user has authorization to become a database - administrator. If Postgres refuses to - create databases - for you, then the site administrator needs to grant you - permission to create databases. Consult your site - administrator if this occurs. - - - - - Accessing a Database - - - Once you have constructed a database, you can access it - by: - - - - - Running the Postgres terminal - monitor programs - (e.g. psql) which allows you to - interactively - enter, edit, and execute SQL commands. - - - - - - Using an existing native frontend tool like - pgaccess or - ApplixWare (via - ODBC) to create and manipulate a - database. - - - - - - Using a language like perl or tcl which has a supported - interface for Postgres. Some of - these languages also have convenient and powerful GUI toolkits - which can help you construct custom - applications. pgaccess, mentioned - above, is one such application written in tk/tcl and can be - used as an example. - - - - - - Writing a C program using - the LIBPQ subroutine - library. This allows you to submit - SQL commands - from C and get answers and - status messages back to - your program. This interface is discussed further - in The PostgreSQL Programmer's Guide. - - - - - You might want to start up psql, - to try out the examples in this manual. - It can be activated for the mydb - database by typing the command: - -% psql mydb - - - You will be greeted with the following message: - -Welcome to the POSTGRESQL interactive sql monitor: - Please read the file COPYRIGHT for copyright terms of POSTGRESQL - - type \? for help on slash commands - type \q to quit - type \g or terminate with semicolon to execute query - You are currently connected to the database: template1 - -mydb=> - - - - - This prompt indicates that the terminal monitor is listening - to you and that you can type SQL queries into a - workspace maintained by the terminal monitor. - The psql program responds to escape - codes that begin - with the backslash character, "\" For example, you - can get help on the syntax of various - Postgres SQL - commands by typing: - -mydb=> \h - - - - - Once you have finished entering your queries into the - workspace, you can pass the contents of the workspace - to the Postgres server by typing: - -mydb=> \g - - - This tells the server to process the query. If you - terminate your query with a semicolon, the "\g" is not - necessary. - psql will automatically process - semicolon terminated queries. - To read queries from a file, say myFile, instead of - entering them interactively, type: - -mydb=> \i fileName - - - To get out of psql and return to Unix, type - -mydb=> \q - - - and psql will quit and return - you to your command - shell. (For more escape codes, type \h at the - monitor prompt.) - White space (i.e., spaces, tabs and newlines) may be - used freely in SQL queries. Single-line - comments are denoted by - "--". Everything after the dashes up to the end of the - line is ignored. Multiple-line comments, and comments within a line, - are denoted by "/* ... */". - - - - - Destroying a Database - - - If you are the database administrator for the database - mydb, you can destroy it using the - following Unix command: - -% dropdb mydb - - This action physically removes all of the Unix files - associated with the database and cannot be undone, so - this should only be done with a great deal of forethought. - - - @@ -10,16 +10,46 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/tutorial.sgml,v 1.14 2001/02/03 19:03 &legal; + + Welcome + + + Welcome to PostgreSQL and the + PostgreSQL Tutorial. The following few + chapters are intended to give a simple introduction to + PostgreSQL, relational database + concepts, and the SQL language to those who are new to any one of + these aspects. We only assume some general knowledge about how to + use computers. No particular Unix or programming experience is + required. + + + + After you have worked through this tutorial you might want to move on + to reading the User's + Guide]]>]]> to + gain a more formal knowledge of the SQL language, or the + Programmer's + Guide]]>]]> + for information about developing applications for + PostgreSQL. + + + + We hope you have a pleasant experience with + PostgreSQL. + + + - &sql; - &arch; &start; &query; &advanced; -- cgit v1.2.3