This book is for anyone interested in utilizing the PostgreSQL object-relational database-management system (ORDBMS). The reader should be familiar with Linux- and Unix-based systems, but is not expected to be a database guru. Although the test operating system for this book is Red Hat Linux, the tasks in this book that apply to Linux should apply to most Unix variants without much modification.

The community version of PostgreSQL in compressed source form. This is the most actively developed PostgreSQL distribution. We do not provide binaries of PostgreSQL, as you may want to compile different features.

The source is available as a single compressed file (postgresql-7.1.3.tar.gz). Its contents are not extracted on the CD, as you must copy and extract the files onto your hard drive before installing PostgreSQL.

An evaluation/developer-use copy of the LXP PostgreSQL application server for Apache 1.3.x. LXP is a good tool for integrating PostgreSQL (and other technologies) with the web. This package is a binary distribution intended for x86-based systems. It must be copied to your hard drive before it can be extracted.

A directory containing the extracted contents of the lxp-eval-0.8.0.tgz file. LXP can be installed directly from the lxp directory on the CD. See Chapter 13 for information on installing LXP.

The PostgreSQL dump of the example booktown database used throughout this book. This file contains both commands to re-create the database schema, as well as some sample data.

To install this database after you have installed PostgreSQL, type from the command line psql -U postgres template1 -f /mnt/cdrom/booktown.sql (where /mnt/cdrom is the path to your mounted CD, and postgres is your PostgreSQL superuser).

Used for filenames, directory names, string-bound constants, and URLs. It is also used for emphasis, and for the first use of a technical term.

Used for keywords, identifiers (such as tables and columns), data types, examples, and to show the contents of files, and the output of commands.

Used in syntax descriptions to indicated user-defined items.

Indicates user input within examples.

Used within syntax descriptions, uppercase usually indicates keywords.

Used within syntax descriptions, lowercase usually indicates user-defined items, such as variables and identifiers.

Used within syntax descriptions, square brackets enclose optional items, which are separated from one another by pipes (|).

Used within syntax descriptions, curly brackets enclose a set of items from which you must choose one.

Used within syntax descriptions, ellipses indicate repeating information. Used within examples, ellipses indicate that a section of unimportant information was removed from the example output to improve readability and conserve space.

O’Reilly & Associates
101 Morris Street
Sebastopol, CA 95472
(800) 998-9938 (in the U.S. or Canada)
(707) 829-0515 (international or local)
(707) 829-0104 (FAX)

• [email protected]

• [email protected]

• http://www.oreilly.com/catalog/ppostgresql/

• http://www.commandprompt.com/ppbook/
• http://www.postgresql.info/

PostgreSQL is an open source project. Open source by definition means that you can obtain the source code, use the program, and modify it freely without the confines of proprietary software. In the database world, open source means that you have honest access to benchmarking numbers and performance statistics, which companies such as Oracle prohibit. Open source also means that you are free to modify PostgreSQL to fit your particular needs.

However, there is a misconception that since open source software is free from distribution restrictions, it is always free of cost to your company. This is not necessarily the case. It is true that you can, without external cost, download and install open source software, but there will always be costs associated with the time and energy your company puts into support and research of the application. As such, if you do not have those resources to spend, there are several commercial vendors and consultants who deal specifically with PostgreSQL.

PostgreSQL approaches data with an object-relational model, and is capable of handling complex routines and rules. Examples of its advanced functionality are declarative SQL queries, multi-version concurrency control, multi-user support, transactions, query optimization, inheritance, and arrays.

PostgreSQL supports user-defined operators, functions, access methods, and data types.

PostgreSQL supports the core SQL99 specification and includes advanced features such as SQL92 joins.

PostgreSQL supports referential integrity, which is used to insure the validity of a database’s data.

The flexibility of the PostgreSQL API has allowed vendors to provide development support easily for the PostgreSQL RDBMS. These interfaces include Object Pascal, Python, Perl, PHP, ODBC, Java/JDBC, Ruby, TCL, C/C++, and Pike.

PostgreSQL has support for internal procedural languages, including a native language called PL/pgSQL. This language is comparable to the Oracle procedural language, PL/SQL. Another advantage to PostgreSQL is its ability to use Perl, Python, or TCL as an embedded procedural language.

MVCC, or Multi-Version Concurrency Control, is the technology that PostgreSQL uses to avoid unnecessary locking. If you have ever used another SQL capable DBMS, such as MySQL or Access, you have probably noticed that there are times when a reader has to wait for access to information in the database. The waiting is caused by people who are writing to the database. In short, the reader is blocked by writers who are updating records.

By using MVCC, PostgreSQL avoids this problem entirely. MVCC is considered better than row-level locking because a reader is never blocked by a writer. Instead, PostgreSQL keeps track of all transactions performed by the database users. PostgreSQL is then able to manage the records without causing people to wait for records to become available.

PostgreSQL uses a process-per-user client/server architecture. This is similar to the Apache 1.3.x method of handling processes. There is a master process that forks to provide additional connections for each client attempting to connect to PostgreSQL.

The PostgreSQL feature known as Write Ahead Logging increases the reliability of the database by logging changes before they are written to the database. This ensures that, in the unlikely occurrence of a database crash, there will be a record of transactions from which to restore. This can be greatly beneficial in the event of a crash, as any changes that were not written to the database can be recovered by using the data that was previously logged. Once the system is restored, a user can then continue to work from the point that they were at before the crash occurred.

• If you would like to install PostgreSQL 7.1.x at this time, then you may continue to Chapter 2.
• If you are new to the SQL language and database queries, then you may continue to Chapter 3. Note that although many of the concepts introduced in Chapter 3 are of a general nature, some of the techniques and concepts presented are specific to PostgreSQL, and should not be overlooked if you want a comprehensive understanding of the software.
• If you are already familiar with the SQL language and statement structure, you may be more interested in Chapter 4, Chapter 5, or Chapter 7.
• If you have a working knowledge of PostgreSQL as an end user and you would like to begin setting up the database server, database users and groups, and authentication, then you may skip to Part III. This part was written to aid system administrators in initializing, configuring, and managing newly installed or existing PostgreSQL databases.
• If you are familiar with PostgreSQL as a database-management system and would like to move directly into technical programming concepts and techniques, read through Part IV. This part of the book contains documentation on PL/pgSQL, the native PostgreSQL procedural language, as well as information and examples on the JDBC interface and the LXP web-based application server.

The installation of PostgreSQL is not difficult. However, there are some software requirements that you will need for the PostgreSQL compilation. All of the requirements—outside of the PostgreSQL source code—are GNU tools. If you are running Linux, there is a good chance that the tools are already installed. If you are running a BSD derivative, such as FreeBSD or MacOS X, you may have to download the tools.

If you find that you are missing any of the required components, first check your vendor’s web site for the packages; otherwise, you may download them from http://www.gnu.org. It is also essential that you have enough disk space available to unpack and compile the source code on the filesystem to which you install. Disk-space requirements are discussed in the section titled Disk Space.

Disk Space

PostgreSQL does not require the extensive use of disk resources. In fact, in comparison to products such as Oracle, PostgreSQL could be considered fat free. However, PostgreSQL is a database, and as with any database, the requirements will grow as you continue to use PostgreSQL.

On an average Linux machine, you will need approximately 50 MB of hard-drive space to unpack the source and another 60 MB of hard drive space to compile the source. If you choose to run the regression tests, you will need an additional 30 MB. Depending on the configuration options you choose, PostgreSQL can take anywhere from 8 to 15 MB of hard drive space once installed.

---

Note

Remember that PostgreSQL’s space requirements will grow as you use the system! Be sure to plan ahead for the amount of data you will be storing.

---

Trying to install on a system lacking in disk space is potentially dangerous! Before installing PostgreSQL, we recommend that you check your filesystem to be sure you have enough disk space in your intended installation partition (e.g., /usr/local). If you have a GNU-based system, the df command should be at your disposal. Example 2-4 checks for free disk space, reported in 1k blocks.

Example 2-4. Verifying disk space

$ df -k
Filesystem          1k-blocks         Used Available Use% Mounted on
/dev/hda1             2355984       932660   1303644  42% /
/dev/hdb1             4142800      2133160   1799192  54% /home
/dev/hda6             1541680       272540   1190828  19% /usr/local

The base package contains the bare minimum of source code required to build and run PostgreSQL.

The docs package contains the PostgreSQL documentation in HTML format. Note that the PostgreSQL man pages are automatically installed with the base package.

The opt package contains several optional extensions to PostgreSQL, such as the interfaces for C++ (libpq++), JDBC, ODBC, Perl, Python, and Tcl. It also contains the source required for multibyte support.

The test package contains the regression test suite. This package is required to run regression tests after compiling PostgreSQL.

Create a Unix user account to own and manage the PostgreSQL database files. Typically, this user is named postgres, but it can be named anything that you choose. For consistency throughout the book, the user postgres is considered the PostgreSQL root or superuser.

You will need to have root privileges to create the PostgreSQL superuser. On a Linux machine, you can use the command shown in Example 2-5 to add the postgres user.

Note

Do not try to use the root user as the PostgreSQL superuser. Doing so presents a large security hole.

Once you have acquired the source for PostgreSQL, you should copy the PostgreSQL source package to a temporary compilation directory. This directory will be the path where you install and configure PostgreSQL. Within this path, you will extract the contents from the tar.gz file and proceed with installation.

Bear in mind that this will not be the location of the installed database files. This is a temporary location for configuration and compilation of the source package itself. If you have downloaded the PostgreSQL package from the Internet, it is probably not saved in your intended compilation directory (unless you explicitly chose to save there). A common convention for building source on Unix and Linux machines is to build within the /usr/local/src path. You will most likely need root privileges to access this path. As such, the remaining examples in this chapter will involve the root user until otherwise specified.

Note

If you are a user of a commercial Linux distribution, we strongly suggest that you verify whether or not you have PostgreSQL already installed. On RPM-based systems, such as SuSe, Mandrake, or RedHat, this can be done by using the following command: rpm -qa | grep -i postgres. If you do have PostgreSQL installed, there is a good chance that it is outdated. You will want to download and install the latest version of PostgreSQL available. An RPM installation of PostgreSQL will sometimes install scripts and programs such as postmaster and psql into globally accessible directories. This can cause conflicts with source-built versions, so before installing a new version, be sure to remove the RPM by using the rpm -e <package name> command.

To unpack PostgreSQL source code on a Linux system, first move (or copy, from the CD) the compressed source file into /usr/local/src (most people move their source files here to keep them separate from their home directories and/or other locations they may keep downloaded files). After moving it to the filesystem location where you wish to unpack it, use tar to unpack the source files. The commands to perform these actions are shown in Example 2-6.

Notice the last command used in Example 2-6. The command is chown -R postgres.postgres postgresql-7.1.3. This command grants the ownership of the PostgreSQL source directory tree to postgres, which in turn enables you to compile PostgreSQL as the postgres user. Once the extraction and ownership change has completed, you can switch to the postgres user to compile PostgreSQL, resulting in all compiled files automatically being owned by postgres.

For reference purposes, the following list is a description of the tar options used to extract the PostgreSQL source distribution:

After you have completed the extraction of the files, switch to the postgres user and change into the newly created directory (e.g., /usr/local/src/postgres-7.1.3). The remaining installation steps will take place in that directory.

Before compilation, you must configure the source, and specify installation options specific to your needs. This is done with the configure script.

The configure script is also used to check for software dependencies that are required to compile PostgreSQL. As configure checks for dependencies, it will create the necessary files for use with the gmake command.

To use the default installation script, issue the following command: ./configure. To specify options that will enable certain non-default features, append the option to the ./configure command. For a list of all the available configuration options, use ./configure - -help

There is a good chance that the default source configuration that configure uses will not be the setup you require. For a well-rounded PostgreSQL installation, we recommend you use at least the following options:

You can also specify anything from the following complete list of configuration options:

If you compile PostgreSQL and find that you are missing a feature, you can return to this step, reconfigure, and continue with the subsequent steps to build and install PostgreSQL. If you choose to come back to this step and reconfigure the PostgreSQL source before installing, be sure to use the gmake clean command from the top-level directory of the source tree (usually, /usr/local/src/postgresql-[version]). This will remove any leftover object files and partially compiled files.

After using the configure command, you may begin compiling the PostgreSQL source by entering the gmake command.

Note

On Linux machines, you should be able to use make instead of gmake. BSD users should use gnumake.

At this point, depending on the speed of your machine, you may want to get some coffee because the PostgreSQL compilation could take 10 minutes, an hour, or even more. After the compilation has finished, the following message should appear:

Regression tests are an optional but recommended step. The regression tests help verify that PostgreSQL will run as expected after you have compiled the source. The tests check tasks such as standard SQL operations, as well as extended capabilities of PostgreSQL. The regression tests can point out possible (but not necessarily probable) problems which may arise when running PostgreSQL.

If you decide you would like to run the regression tests, do so by using the following command: gmake check, as shown in Example 2-8.

The gmake check command will build a test installation of PostgreSQL within the source tree, and display a list of all the checks it is running. As each test completes, the success or failure will be reported. Items that fail the check will have a failed message printed, rather than the successful ok message. If any checks fail, gmake check will display output similar to that found in Example 2-9, though the number of tests failed may be higher on your system than the number in the example.

The files referenced in Example 2-9 (regression.diffs and regression.out) are placed within the source tree at src/test/regress. If the source tree is located in /usr/local/src, the full path to the directory files would be /usr/local/src/postgresql-[version]/src/test/regress.

The regression tests will not always pick up every possible error. This can be due to inconsistencies in locale settings (such as time zone support), or hardware-specific issues (such as floating-point results). As with any application, be sure to perform your own requirements testing while developing with PostgreSQL.

Note

You cannot run the regression tests as the root user. Be sure to run gmake check as the postgres user.

After you have configured and compiled the PostgreSQL source code, it is time to install the compiled libraries, binaries, and data files into a more appropriate home on the system. If you are upgrading from a previous version of PostgreSQL, be sure to back up your database before beginning this step. Information on performing PostgreSQL database backups can be found in Chapter 9.

Installation of the compiled files is accomplished with the commands demonstrated in Example 2-10. When executed in the manner shown in Example 2-10, the su command temporarily logs you in as the root user to execute the required commands. You must have the root password to execute both of the commands shown in Example 2-10.

Note

If you specified a non-default installation directory in Step 3, use the directory you specified instead of /usr/local/pgsql.

The su -c “gmake install” command will install the freshly compiled source either into the directory structure you chose in Step 3 with the - -prefix configuration option, or, if this was left unspecified, into the default directory of /usr/local/pgsql. The use of the su -c “chown -R postgres.postgres /usr/local/pgsql” command will ensure that the postgres user owns the PostgreSQL installation directories. Using the su -c command lets you save a step by only logging you in as the root user for the duration of the command’s execution.

If you chose to configure the PostgreSQL source with the Perl or Python interface, but did not have root access, you can still install the interfaces manually. Use the commands demonstrated in Example 2-11 to install the Perl and Python modules manually.

You may also want to install the header files for PostgreSQL. This is important, because the default installation will only install the header files for client application development. If you are going to be using some of PostgreSQL’s advanced functionality, such as user-defined functions or developing applications in C that use the libpq library, you will need the header files. To install the required header files, perform the commands demonstrated in Example 2-12.

The use of the PostgreSQL environment variables is not required. However, they are helpful when performing tasks within PostgreSQL, including starting and shutting down the postmaster processes. The environment variables that should be set are for the man pages and the bin directory. You can do so by adding the following statements into the /etc/profile file. This should work for any sh-based shell, including bash and ksh.

Note

You must login to the system after the /etc/profile file has had environment variables added to it in order for your shell to utilize them.

Depending on how your system handles shared libraries, you may need to inform the operating system of where your PostgreSQL shared libraries are located. Systems such as Linux, FreeBSD, NetBSD, OpenBSD, Irix, HP/UX, and Solaris will most likely not need to do this.

In a default installation, shared libraries will be located in /usr/local/pgsql/lib (this may be different, depending on whether you changed it with the - -prefix configuration option). One of the most common ways to accomplish this is to set the LD_LIBRARY_PATH environment variable to /usr/local/pgsql/lib. See Example 2-13 for an example of doing this in Bourne-style shells and Example 2-14 for an example of doing this in csh and tcsh.

If you are logged in as the root user, instead of using the su -c command in the previous steps, you will now need to login as the postgres user you added in step 1. Once you are logged in as the postgres user, issue the command shown in Example 2-15.

The -D option in the previous command is the location where the data will be stored. This location can also be set with the PGDATA environment variable. If you have set PGDATA, the -D option is unnecessary. If you would like to use a different directory to hold these data files, make sure the postgres user account can write to that directory. When you execute initdb you will see something similar to what is shown in Example 2-16.

Note

You can indicate that PostgreSQL should use a different data directory by specifying the directory location with the -D option. This path must be initialized through initdb.

When the initdb command has completed, it will provide you with information on starting the PostgreSQL server. The first command displayed will start postmaster in the foreground. After entering the command as it is shown in Example 2-17, the prompt will be inaccessible until you press CTRL-C on the keyboard to shut down the postmaster process.

Starting PostgreSQL in the foreground is not normally required. We suggest the use of the second command displayed. The second command will start postmaster in the background. It uses pg_ctl to start the postmaster service, as shown in Example 2-18.

The major difference between the first command and the second command is that the second runs postmaster in the background, as well as redirects any debugging information to /tmp/pgsql.log. For normal operation, it is generally better to run postmaster in the background, with logging enabled.

Note

The pg_ctl application can be used to start and stop the PostgreSQL server. See Chapter 9 for more on this command.

The SysV script will allow the graceful control of the PostgreSQL database through the use of the SysV runlevel system. The SysV script can be used for starting, stopping, and status-checking of PostgreSQL. It is known to work with most Red Hat based versions of Linux, including Mandrake; however, it should work with other SysV systems (e.g., UnixWare, Solaris, etc.) with little modification. The script is named linux. To use it, you will first need to copy the linux script to your init.d directory. You may require root access to do this.

First, change to the directory where you unpacked the PostgreSQL source. In our case, the path to that directory is /usr/local/src/postgresql-7.1.3/. Then, issue a cp command to copy the script from contrib/start-scripts into the init.d directory. Example 2-19 demonstrates how to do this on a Red Hat Linux system.

Example 2-19 arbitrarily re-names the new copy to postgresql; you may call it whatever you prefer, though it is typically named either postgresql, or postgres.

You will need to make the script file executable after copying it. To do so, use the command shown in Example 2-20.

There are no additional requirements to use the SysV script with Red Hat, if you do not intend on using it to start PostgreSQL automatically (i.e., if you wish to use the script manually). However, if you do wish for the script to startup PostgreSQL automatically when the machine boots up (or changes runlevels), you will need to have the chkconfig program installed. If chkconfig is installed, you will also need to add the following two lines, including the hash (#) symbol, at the beginning of the /etc/rc.d/init.d/postgresql file:

These example numbers should work on your system; however, it is good to know what role they perform. The first group of numbers (345) represent which runlevels PostgreSQL should be started at. The example shown would start PostgreSQL at runlevels 3, 4, and 5. The second group of numbers (85) represent the order in which PostgreSQL should be started within that runlevel, relative to other programs. You should probably keep the second number high, to indicate that it should be started later in the runlevel. The third number (15) represents the order in which PostgreSQL should be shutdown. It is a good idea to keep this number low, representing a shutdown order that is inverse from the startup order. As previously mentioned, the script should work on your system with the numbers provided, but you can change them if it is necessary.

Once these two lines have been added to the script, you can use the commands shown in Example 2-21 on Red Hat and Mandrake Linux distributions to start the PostgreSQL database. Be sure to execute these as the root user.

Note

The SysV script logs redirects all PostgreSQL debugging output to /usr/local/pgsql/data/serverlog, by default.

Now that the PostgreSQL database system is running, you have the option of using the default database, template1. If you create a new database, and you would like all of your consecutive databases to have the same system-wide options, then you should first configure the template1 database to have those options enabled. For instance, if you plan to use the PL/pgSQL language to program, then you should install the PL/pgSQL language into template1 before using createdb. Then when you use the createdb command, the database created will inherit template1's objects, and thus, inherit the PL/pgSQL language. For more information on installing the PL/pgSQL language into a database, refer to Chapter 11.

The next step will be to create a new database. This will be a simple test database. We do not recommend using the default template1 database for testing purposes. As you have not created any users with database-creation rights, you will want to make sure that you are logged in as the postgres user when adding a new database. You can also create users that are allowed to add databases, which is discussed later in Chapter 10. To create a new database named testdb, enter the command shown in Example 2-22.

You should receive a message that says CREATE DATABASE, indicating that creation of the database was successful. You can now use PostgreSQL’s command line interface, psql, to access the newly created database. To do so, enter the command shown in Example 2-23.

You can now start entering SQL commands (e.g., such as SELECT) at the psql prompt. If you are unfamiliar with psql, please see Chapter 4 for an introduction.

To verify that the database is working correctly, you can issue the command shown in Example 2-24, which should give you a listing of the languages installed in the database.

SQL, the Structured Query Language, is a mature, powerful, and versatile relational query language. The history of SQL extends back to IBM research begun in 1970. The next few sections discuss the history of SQL, its predecessors, and the various SQL standards that have developed over the years.

A Brief History of SQL

The relational model, from which SQL draws much of its conceptual core, was formally defined in 1970 by Dr. E. F. Codd, a researcher for IBM, in a paper entitled A Relational Model of Data for Large Shared Data Banks. This article generated a great deal of interest in both the feasibility and practical commercial application of such a system.

In 1974 IBM began the System/R project and with the work of Donald Chamberlin and others, developed SEQUEL, or Structured English Query Language. System/R was implemented on an IBM prototype called SEQUEL-XRM in 1974–75. It was then completely rewritten in 1976–1977 to include multi-table and multiuser features. When the system was revised it was briefly called “SEQUEL/2,” and then renamed “SQL” for legal reasons.

In 1978, methodical testing commenced at customer test sites. Demonstrating both the usefulness and practicality of the system, this testing proved to be a success for IBM. As a result, IBM began to develop commercial products that implemented SQL based on their System R prototype, including SQL/DS (introduced in 1981), and DB2 (in 1983).

Several other software vendors accepted the rise of the relational model and announced SQL-based products. These included Oracle (who actually beat IBM to market by two years by releasing their first commercial RDBMS, in 1979), Sybase, and Ingres (based on the University of California’s Berkeley Ingres project).

---

Note

PostgreSQL’s name is, as you might have guessed, a play on the name Ingres. Both PostgreSQL and Ingres trace their roots back to the UC Berkeley’s Ingres RDBMS system.

---

While PostgreSQL is commonly considered an RDBMS, or a “database,” it may not be commonly understood what is meant specifically by the word database. A database within PostgreSQL is an object-relational implementation of what is formally called a schema in SQL99.

Put simply, a database is a stored set of data that is logically interrelated. Typically, this data can be accessed in a multiuser environment. This is the case with PostgreSQL, though there are well-defined rights and restrictions enforced with that access.

It may not be commonly understood that PostgreSQL can have several databases concurrently available, each with their own owner, and each with their own unique tables, views, indices, sequences, and functions.

In order to create a table, function, or any other database object, you must connect to a specific database via a PostgreSQL client. Once connected, you can create an object, which is then owned by the connected database, and therefore is inaccessible from any other database (though a client may have several connections open to different databases).

By keeping fundamental data objects segregated into their own databases in this fashion, you run a smaller risk of running into a naming collision by choosing a table name already chosen for another purpose (e.g., if two users each wanted to have a table called products for two separate applications). This is because neither database has any knowledge of the other database’s components, and will not attempt to make any kind of logical relationship between them. Furthermore, as the same rule applies to object-relational data objects, users may even create functions and language definitions within their database that are inaccessible to other users connected to other databases running within PostgreSQL.

By default, PostgreSQL installs only one functional database, which is called template1 to represent the template nature of the database. Any database created after template1 is essentially a clone, inheriting any of its database objects, including table structure, functions, languages, etc. It is not uncommon to create a default database for new PostgreSQL users with the same name as their PostgreSQL user-name, as PostgreSQL will attempt to connect to a database with the same name as the connecting user if a database name is not specified.

Tables are quite possibly the most important aspect of SQL to understand inside and out, as all of your data will reside within them. In order to be able to correctly plan and design your SQL data structures, and any programmatic routines toward accessing and applying that data, a thorough understanding of tables is an absolute pre-requisite.

A table is composed of columns and rows, and their intersections are fields. If you have ever used spreadsheet software before (such as Excel), these two terms are visually represented in the same manner, and the fields within a table are equivalent to the cells within a spreadsheet. From a general perspective, columns within a table describe the name and type of data that will be found (and can be entered) by row for that column’s fields. Rows within a table represent records composed of fields that are described from left to right by their corresponding column’s name and type. Each field in a row is implicitly correlated with each other field in that row. In this sense, columns can be thought of as descriptors for the discrete, sequential elements of a row, and each row can be thought of as a stored record matching that description.

Table 3-1 illustrates a simple table called books, used by our imaginary bookstore, Book Town. We will frequently refer to this table in later examples. Each of its stored records describes a book by a numeric identifier, title, author identifier, and subject identifier. These characteristics, from left to right, are described by its columns (id, title, author_id, and subject_id).

As you can see, this describes a table with four columns, in a fixed, left-to-right order, currently populated by six rows (also known as tuples, or records). It is essential to understand that in a relational database, while a table has a fixed column order, rows themselves are inherently unordered. You will see later, as the SQL’s query structure is explained in Chapter 4, that there are ways within SQL to order selected rows. However, the rows in the database itself are not automatically ordered in any consistently predictable way. When order is meaningful for a SQL query, you must carefully consider and explicitly order records.

Every table must have at least one column, but tables may at times contain no rows, because each vertical column corresponds to a relatively fixed attribute of the data represented in that table (such as the title column in the previous example’s books table). Without a column, a row’s contents would be ambiguous; without a row, a table is merely lacking recorded data. As of PostgreSQL 7.1, there is a maximum of 1600 columns to a table, and an unlimited number of rows (i.e., you are limited only by hardware limitations, such as disk space).

In Table 3-1, the column names fairly clearly indicate the significance of each column. The decision of how to name columns is fairly arbitrary, though, and care must be taken in planning table names and conventions to avoid ambiguity.

Though it may not be immediately obvious, each of the columns of a table have an associated data type. While a column’s data type helps to further describe the sort of information it contains, it constrains the kind of data that may be inserted into the column. For example, the author_id column is of type integer; this signifies that any insertion attempts not consisting of pure a integer (e.g., 110a) will fail. These types are described in more detail in the section titled Data Types.

This section introduced the general concepts of how data is logically arranged in a relational database and within tables. The next section explains why statements are the basis for all interactions with the database.

SQL statements always begin with a command (a word, or group of words, that describes what action the statement will initiate). The command can be called the verb of the SQL statement, as it always describes an action to be taken. Statements typically contain one or more clauses, which are formal modifiers that further describe the function of the SQL statement.

Table 3-2 contains a list of some of the most commonly used PostgreSQL commands.

While obviously code-like in nature, SQL was designed with ease of use and readability in mind. As a result, SQL statements often bear a strong resemblance to simple, instructional English sentences. A strong feature of SQL is that its statements are designed to instruct the server what data to find, not literally how to find it, as you would be forced to do in an ordinary programming language. Reading a well-designed SQL query should be nearly as easy as reading an ordinary sentence.

Note

In SQL texts, the word query is frequently used interchangeably with statement. In order to be clear, within this book the term query is used only to refer to statements which return data (e.g., SELECT statements), rather than general SQL statements, which may instead create, add, or modify data.

Internally, PostgreSQL interprets structured SQL statements as a sequence of tokens, usually delimited by whitespace (spaces or newlines, outside of quotes), though some tokens may be placed adjacently if there is no chance of ambiguity (such as when operators are placed directly next to identifiers). A token in this context is a word or character that can be identified meaningfully by the server when the SQL statement is parsed, or interpreted.

Technically, each token can either be considered a keyword, an identifier, a quoted identifier, a constant (also called a literal), or one of several special character symbols. Keywords are words PostgreSQL recognizes as words with predefined SQL or PostgreSQL-specific meanings; these include SQL commands, clauses, function names, and special noise terms, which are often accompanied optionally with SQL commands (e.g., the noise term WORK in the COMMIT command). In contrast, identifiers represent variable names for tables, columns, and any other database object.

Both keywords and identifiers reference internally defined functions, values, or records, as far as PostgreSQL is concerned. Constants, on the other hand, describe pieces of data that are interpreted literally, such as a number or character string.

Finally, a SQL statement contains special character symbols. These are reserved characters (such as parentheses, the semicolon, and square brackets) that logically affect the meaning and arrangement of your keywords, identifiers, and literals. You can think of these characters as the punctuation for your SQL statements.

Operators fall under the category of special character symbols; they can be used to imply logical operations or evaluations between data values (either literals, or represented by identifiers), and are generally between one and four characters in length.

The following sections explain and expand upon the nature of these elementary components of SQL.

As described in the preceding section, each sequential element of a SQL statement is considered a token. What may not be immediately clear, however, is that tokens may be kept all on the same line, or they may be split across several lines, as extra whitespace is ignored by PostgreSQL’s parser.

Consider the SQL statement in Example 3-1, which is executed first on a single line, and then executed again, split across two separate lines. Both SELECT statements instruct the database to display the entire contents of the my_list table:

In Example 3-1 there are several newlines and spaces between the second statement’s tokens. As you can see by the identical output, PostgreSQL ignores the extra newlines and spaces, making both statements semantically equivalent. You can take advantage of this behavior by splitting a long string of tokens across numerous lines for improved readability of your SQL statement. This probably isn’t necessary for statements as simple as those in Example 3-1, but it can be quite helpful when dealing with complex SQL statements with numerous clauses, expressions, and conditions. Throughout this book we will periodically split some statements over several lines to help show what each part of the statement is intended to accomplish.

Keywords are any reserved SQL terms which have a reserved syntactic meaning to the server. Some common keywords are INSERT, UPDATE, SELECT, and DELETE.

All SQL commands are keywords, though many keywords themselves are not complete commands. For instance, the command INSERT INTO is a valid SQL command, and the word INTO is a reserved keyword. As you might guess, however, the word INTO has no particular significance when used out of context.

Identifiers, as described earlier, are variable names that reference database objects. These names are arbitrarily designated by the creator of the database object upon creation. The objects which can be referred to by identifiers in PostgreSQL may be databases, tables, columns, indices, views, sequences, rules, triggers, or functions.

Example 3-2 adds three pieces of information about Oregon into a simple table called states.

In Example 3-2, the INSERT INTO SQL command makes use of the SQL keywords INSERT, INTO, and VALUES.

The INSERT INTO command modifies the table referenced by the states identifier. The modification in this case is the insertion of a new record.

Quoted identifiers

While not normally required, quotes can be used around identifiers, meaning they should be interpreted literally. For example, if we want to view each of the columns from a table called states, a simple statement to achieve this would ordinarily read:

booktown=# SELECT * FROM states;
 id |    name    | abbreviation
----+------------+--------------
 33 | Oregon     | OR
 42 | Washington | WA
(2 rows)

The keywords in this statement are SELECT and FROM, while the identifiers are the asterisk * (indicating all columns), and states (the table name). With this command, we are selecting all columns from a table named states and thereby viewing its contents.

You can accomplish the same thing by putting quotes around the identifier, with the following statement:

booktown=# SELECT * FROM "states";
 id |    name    | abbreviation
----+------------+--------------
 33 | Oregon     | OR
 42 | Washington | WA
(2 rows)

As you can see, the output is identical when applying quotes to a lowercase identifier. However, the following statement, which uses quotes around the stAtes identifier, will fail:

booktown=# SELECT * FROM "stAtEs";
ERROR: Relation 'stAtEs' does not exist

This statement fails because it instructs PostgreSQL to look for a table called, literally, stAtEs (rather than states). In other words, with the use of quotes, the statement has explicitly requested that PostgreSQL interpret the identifier name literally.

All non-quoted identifiers are folded, or converted, to lowercase. When specifying stAtEs, or STATES (i.e., any combination of uppercase or lowercase letters) without quotes, PostgreSQL automatically converts the identifier to lowercase (states) before processing the statement.

---

Note

The folding of unquoted identifiers to lowercase names is a PostgreSQL-specific convention. The SQL92 standard specifies that unquoted identifiers always be converted to uppercase. For both legacy and readability reasons, PostgreSQL does not intend to move to this part of the SQL92 standard.

This should be of special note to database administrators familiar with other SQL products, such as Oracle, who expect case to automatically change to uppercase. If you are a developer, and you are interested in writing easily portable applications, be sure to consider this case issue to avoid conflicts over this convention.

---

Since the parser can still read and understand mixed-case statements (provided that they are formed with the correct syntax), you should use uppercase and lowercase terminology carefully. Your use of case can both help and hinder your efficiency when working with a large amount of SQL.

We recommend that, for readability, you try typing identifiers in lowercase and keywords in uppercase, the convention used throughout this book. By visually separating the fixed, systematic terminology from the user-defined data objects, you make it a great deal easier to quickly read and understand complex SQL statements.

While much of the data in working with a database is stored on the disk and referred to via identifiers (e.g., table names, column names, and functions), there are obviously times when new data must be introduced to the system. This may be observed when inserting new records, when forming clauses to specify criteria to delete or modify, or even when performing calculations on existing records. This data is input through constants, which are sometimes called literals because they literally represent a value in a SQL statement (rather than referencing an existing value by identifier).

An implicitly typed constant is one whose type is recognized automatically by PostgreSQL’s parser merely by its syntax. PostgreSQL supports five types of implicitly typed constants:

String constants

A string constant is an arbitrary sequence of characters bound by single quotes (apostrophes). These are typically used when inserting character data into a table or passing character data to any other database object. A practical example of the necessity of string constants is updating the first and last names of authors in Book Town’s authors table:

booktown=# SELECT * FROM authors;
  id   | last_name |    first_name
-------+-----------+------------------
  1809 | Geisel    | Theodor Seuss
  1111 | Denham    | Ariel
 15990 | Bourgeois | Paulette
 25041 | Bianco    | Margery Williams
    16 | Alcott    | Luoisa May
   115 | Poe       | Edgar Allen
(6 rows)

Looking at this table’s contents, it might stand out to you that the first_name with id 16, Louisa May has been misspelled as Luoisa May. To correct this, an UPDATE statement can be made with a string constant, as shown in Example 3-4.

Example 3-4. Using string constants

booktown=# UPDATE authors
booktown-#        SET first_name = 'Louisa May'
booktown-#        WHERE first_name = 'Luoisa May';
UPDATE 1
booktown=# SELECT * FROM authors;
  id   | last_name |    first_name
-------+-----------+------------------
  1809 | Geisel    | Theodor Seuss
  1111 | Denham    | Ariel
 15990 | Bourgeois | Paulette
 25041 | Bianco    | Margery Williams
    15 | Poe       | Edgar Allen
    16 | Alcott    | Louisa May
(6 rows)

The UPDATE statement made in Example 3-4 uses the string constants Louisa May and Luoisa May in conjunction with the SET and WHERE keywords. This statement updates the contents of the table referenced by the authors identifier and, as shown, corrects the misspelling.

The fact that string constants are bound by single quotes presents an obvious semantic problem, however, in that if the sequence itself contains a single quote, the literal bounds of the constant are made ambiguous. To escape (make literal) a single quote within the string, you may type two adjacent single quotes. The parser will interpret the two adjacent single quotes within the string constant as a single, literal quote. PostgreSQL will also allow single quotes to be embedded by using a C-style backslash:

testdb=# SELECT 'PostgreSQL''s great!' AS example;
       example
---------------------
 PostgreSQL's great!
(1 row)

booktown=# SELECT 'PostgreSQL\'s C-style slashes are great!' AS example;
                 example
-----------------------------------------
 PostgreSQL's C-style slashes are great!
(1 row)

PostgreSQL also supports the C-style “backslash escape” sequences, which are listed in Table 3-3.

Table 3-3. PostgreSQL supported C-style escape sequences

Escape sequence	Description
\\	Literal backslash
\'	Literal apostrophe
\b	Backspace
\f	Form feed
\n	Newline
\r	Carriage return
\t	Tab
\xxx	ASCII character with the corresponding octal number xxx

---

Note

As a result of the backslashes’ special meaning described in Table 3-3, in order to include a backslash in the string you must escape it using a another backslash (e.g., 'A single backslash is: \\' will transform the pair of backslashes into a single backslash).

---

When entering two quoted character strings to PostgreSQL that are separated by some amount of whitespace, and where that whitespace includes at least one newline, the strings are concatenated and viewed as if they had been typed as one constant. This is illustrated in Example 3-5.

Example 3-5. Multiline string constants

booktown=# SELECT 'book'
booktown-#
booktown-# 'end' AS example;
 example
---------
 bookend
(1 row)

booktown=# SELECT 'bookend' AS example;
 example
---------
 bookend
(1 row)

As you can see, the semantics of the two statements is equivalent. However, at least one newline is required for this interpretation to be possible, as spaces alone would result in the following error:

booktown=# SELECT 'book' 'end' AS mistake;
ERROR:  parser: parse error at or near "'"

This error occurs because without a newline, PostgreSQL will assume that you are referring to two separate constants. If you wish to concatenate two string constants this way on a single line, PostgreSQL supports the || operator for text concatenation (see Chapter 5, for more details on this operator).

booktown=# SELECT 'book' || 'end' AS example;
 example
---------
 bookend
(1 row)

Special character symbols are characters with a pre-defined syntactic meaning in PostgreSQL. They are typically disallowed from being used in identifier names for this reason, though as mentioned in the section on quoted identifiers, this restriction can usually be worked around with quotes if need be.

Comments are blocks of text that, through special character sequences, can embed non-SQL text within SQL code. These can be used within blocks of code, because PostgreSQL removes the commented areas from the input stream and treats them as whitespace. There are two styles of comments available: single-line comments, and multiline comments.

Single-line comments are preceded by two dashes (--) and may either be on a line by themselves, or they may follow valid SQL tokens. (The comments themselves are not considered tokens to PostgreSQL’s parser, as any character data following the -- sequence, up to the end of the line, is treated as whitespace.) This is demonstrated in Example 3-11.

Multiline comments begin with a sequential slash-asterisk (/*) sequence, and terminate with a sequential asterisk-slash (*/) sequence. This style of commenting may already be familiar to C programmers, but there is one key difference between PostgreSQL’s interpreter and the C language interpreter: PostgreSQL comments may be nested. Therefore, when you create a multiline comment within another multiline comment, the */ used to close the inner comment does not also close the outer comment. Example 3-12 provides a comment explanation.

Nesting comments can be useful if you have a file containing SQL syntax of which you wish to comment a large portion before sending to PostgreSQL for interpreting and execution. If you have already used multiline comments within that document and you wish to comment a large section which includes those comments, PostgreSQL is intelligent enough to recognize that a closing comment sequence (*/) closes only the most recently opened comment, not the entire commented region.

Note

The asterisk character by itself (without an adjacent slash character) has no special meaning within a comment. The extra asterisks in Example 3-12 on multiline comments are provided only for aesthetic purposes and readability.

In summary, a SQL statement is comprised of tokens, where each token can represent either a keyword, identifier, quoted identifier, constant, or special character symbol. Table 3-7 uses a simple SELECT statement to illustrate a basic, but complete, SQL statement and its components.

As shown in the table, the SELECT statement contains the keywords SELECT and FROM. Together, the FROM keyword and states token compose a clause, as they modify and further describe the SELECT command.

The id, name, and states tokens are the identifiers of the statement. The id and name identifiers specify the selected columns, while the states identifier specifies the table name to select from. Therefore, with the preceding SQL query, you are instructing PostgreSQL to display the columns named id and name for each row from the states table. Example 3-13 shows the output this query generates within the booktown database.

Getting more complicated, Table 3-8 and Table 3-9 break down another example statement. This statement uses the UPDATE command, along with SET and WHERE clauses, which respectively specify with what to update the records, and how to find the records to update.

When executed, this statement examines each record’s name column to find matches for the WHERE clause’s stated condition (equivalence to the string constant ‘Oregon’). Then, for each row which matches that condition, it updates the id column with the value 51.

Breaking it down, this UPDATE statement has three keywords, three identifiers, two operators, and two constants. The keywords are UPDATE (the SQL command), SET (specifies the updates to make), and WHERE (identifies the rows to update). The identifiers are the states table name, the id column name, and the name column name.

The operators are both represented by the = operator. When used with the SET clause, this operator is used for assignment (to assign a new value to an existing record’s identified column); this is a special use which is unique to the SET clause. In contrast, when used with the WHERE clause, the = operator is used to check equivalence between values. In this case, this means that the equivalence operator will check the value of a record’s name column against a string constant with the value of Oregon.

Finally, the constants in this statement are the integer constant 51 (the new value for the id column), and the string constant Oregon (compared to the name column through the WHERE clause).

Example 3-14 therefore updates the states table by setting the id column to 51 whenever the name column matches the value Oregon. It then checks the results of that UPDATE statement with another SELECT statement.

Table 3-10. PostgreSQL supported data types

Despite the previously discussed rule that a column can have only one data type and logically accept only that type, there is a value that all columns can be defined as, no matter what their data type. This is the value a column is set to when you use the SQL keyword NULL. Essentially, NULL has no data value, so it is not considered a type; it is a system value that indicates to the database that the field it is located within contains no value. The only exception to the rule that any column can contain a NULL is when the NOT NULL constraint is specified for a column.

NULL is often used in places where a value is optional. It can be a convenient way of omitting data without having to resort to strange or arbitrary conventions, such as storing negative values in an integer field to represent omitted data. While your system requirements may change over time, the connotation of NULL is always NULL.

NULL can be thought of as a meta-value: a value that represents a lack of a value, which will never be equivalent to a non-NULL value. One problem often encountered when working with NULL values is that they are easily confused with empty character strings, which return a blank value to the client when selected. The reason this can be confusing is that NULL values also return a blank value when selected; however, they are completely different than empty character strings and this must be understood in order to avoid creating faulty queries or code. A character string column that contains a blank value still contains a string of characters, though the characters that compose the string are blank; thus, there is still a value in the column. A NULL value represents the complete absence of value within the column, not that it is merely blank.

This is an important distinction, as the rules for SQL operations involving the NULL value are quite different than the rules for operations involving empty string values. This internal distinction is especially important in reference to joins, which are discussed in Chapter 4.

The return of both NULL and empty values is shown in Example 3-15, which retrieves a set of five books from the books table. The first SELECT query shows that there appear to be two books which have been inserted without titles. Upon successive querying, however, it becomes clear that while neither have visible titles, one of the books has an empty value for its title (id 100), while the other has a NULL value.

Example 3-16 demonstrates a more practical (and likely) use of NULL in a table called editions, which relates a book’s ISBN number to its publication date.

NULL might be used in this manner in order to represent books with editions that are not yet published, or for books whose publication date was unknown when entered into the database. It could be misleading to supply some arbitrarily illogical date for a book fitting either of these criteria, and in both cases, NULL makes sense as a solution.

A Boolean value is a simple data structure which can only represent values of true or false. PostgreSQL supports the SQL99-defined boolean data type, with a PostgreSQL-specific alias of bool.

Like all other data types, Boolean values can also be set to NULL. If a Boolean is set to NULL, it will never be interpreted as either true or false; it will be interpreted as NULL. This may seem obvious, but it is significant in situations where you may think to check for NULL Booleans by checking for false values (which won’t work). You must use IS NULL to check for NULL Booleans. The ability to be true, false, or NULL (and its related rules regarding the designation of NULL as not being true or false) is known as three-valued logic.

Table 3-11 shows the valid constant values for a true or false state that are recognized by PostgreSQL. Which convention you choose to employ is dependent solely on your own preference. All variations of true, as well as all variations of false, are interpreted identically by the server.

Note

If you decide to use the constants listed in Table 3-11, every value (except for true and false) must be enclosed within single quotes. Failure to do so will result in a server error.

Example 3-17 creates a table named daily_inventory that logs what books are stock and which are not, correlating an ISBN number with a Boolean value. Once created, the table is populated with data via a series of INSERT statements involving a string constant (the ISBN number), and a variety of valid Boolean constants.

Now that the table has been populated with records, a SELECT query may be issued to easily check which books are in stock, as shown in Example 3-18.

With a Boolean column you have the ability to imply a true value by referencing the column name without any kind of operator or modifying keyword. This can lead to more intuitive looking queries for well-designed tables, as shown in Example 3-19.

Although the second query does not specify ‘true’ or ‘false’, it implicitly looks for a value of ‘true’ by omitting a comparison operator.

Similarly, if you want to search for false values, you may either compare the named column’s value against any of the valid boolean constants in Table 3-11, or you may use the SQL keyword NOT just before the column name. Each method is demonstrated in Example 3-20.

In this way, you can see how SQL was designed with human readability in mind. By naming your tables and columns in well-designed terms, a SQL query can read almost as plainly as an English sentence.

For the more programming-oriented readers, it may be of interest that you can use the inequality (!=) operator to compare the value of a boolean field against any of the values in Table 3-11 (e.g., WHERE in_stock != 't'). As such, the following three syntactic variations are each equivalent:

You may have noticed that while seven rows were inserted into the table in Example 3-17, only six rows were returned between the books found in stock, and those found out of stock. This is due to the last insertion in Example 3-17 not supplying a value at all for the in_stock column, leaving the record for the book with ISBN 0451160916 with a NULL value in the in_stock column.

As stated previously, NULL will not register as either true or false. As such, you may use the SQL phrase IS NULL to check for rows with NULL values. Alternatively, you may use != but you will risk portability issues with other databases. The following syntax demonstrates a SQL query which uses the IS NULL phrase:

Since IS NULL is a general SQL phrase, you can use the same WHERE clause in an UPDATE statement to correct any accidental NULL values.

Character types are required any time that you wish to reference character data, such as blocks of ASCII text. They are commonly used for storing names, addresses, and so on.

SQL provides two character types called character, and character varying. In addition to these, a general text type is supported by PostgreSQL, which does not require an explicitly declared upper limit on the size of the field. Columns of type text are automatically re-sized according to the data you put in them, and they may re-size without boundaries (discounting, of course, the 1GB limit for a single field). Table 3-12 shows the available character data types within PostgreSQL.

The n in Table 3-12 represents an arbitrarily specified number of characters. This number is specified for a column when a table is created.

Note

Although the text data type is not part of the ANSI/ISO SQL standards, many other Relational Database Management Systems (RDBMS) provide this functionality, including Sybase and MS SQL Server.

PostgreSQL’s numeric types are used to represent both integers and decimal floating-point values. From a general perspective, PostgreSQL’s supported numeric types consist of:

PostgreSQL has support for special types which fall under the family of numeric types, including the deprecated money type, and the special serial construct.

As shown in Table 3-13, several of PostgreSQL’s data types have aliases that are equivalent to their associated data types. This was done for ease of use, but at times it can be confusing, due to the fact that some of the aliases sound familiar. If you are not careful to understand what data type an alias you are using is associated with, you may accidentally reference the wrong data type. For example, in PostgreSQL the real and double precision data types represent values that in many other languages are referred to as float values; however, they both have aliases that contain the word “float” (float and float8 link to double precision; float4 links to real). Problems may result if if you attempt to use the float alias, thinking it is linked to real, when in fact it is associated with double precision.

The numeric type

The numeric (also known as decimal) type is a specially designed numeric data type that can represent arbitrarily large and precise values within a fixed length that is given by the user. When you create a table with a column of type numeric, you may specify in parentheses two values: the precision and the scale.

The precision is the maximum number of digits that the numeric value may hold (including digits to the right of the decimal point), while the scale describes how many of those digits of precision are to be to the right of the decimal point. If left unspecified, the precision will default to 30 digits, and scale to 6 digits. The maximum precision (and, hence, the maximum scale) you can set this to is 1,000. Setting the precision to 1,000 would allow a maximum 1,000 digits, which should be fairly adequate for most needs.

---

Note

PostgreSQL will not always return an error if you violate the precision and scale of a numeric column.

---

Unlike the floating-point data types, you will receive an overflow error if you attempt to insert a number that is larger than the allotted precision range. Beside this limitation, you should be able to insert any number that fits within the provided precision and scale of the numeric type column.

For example, in a numeric(11,6) column, you may safely insert the value 9.999999 with two digits too many to the right of the decimal point (though the value is rounded up to 10.000000). However, an attempt to insert the value 99999.99999999 will fail, as shown in Example 3-22.

Problems that arise from trying to insert values that are two large can be avoided by using the trunc() numeric truncating function within an INSERT command to make sure a number is truncated to a size suitable for the column it is being inserted into. You must provide the length it should be truncated to, which means you’ll have to be aware of the precisions you’ve previously specified. The use of trunc() is also illustrated within Example 3-22.

Example 3-22. Avoiding overflow errors

booktown=# INSERT INTO numbers VALUES (9.99999999);
INSERT 3390697 1
booktown=# SELECT * FROM numbers;
    number
--------------
    10.000000
(1 row)

booktown=# INSERT INTO numbers VALUES (99999.99999999);
ERROR:  overflow on numeric ABS(value) >= 10^5 for field with precision 11 scale 6
booktown=# INSERT INTO numbers VALUES (trunc(99999.99999999, 6));
INSERT 3390698 1
booktown=# SELECT * FROM numbers;
    number
--------------
    10.000000
 99999.999999
(2 rows)

booktown=# INSERT INTO numbers VALUES (trunc(9.99999999, 6));
INSERT 3390699 1
booktown=# SELECT * FROM numbers;
    number
--------------
    10.000000
 99999.999999
     9.999999
(3 rows)