Part I: Introduction and Installation

Preface

Table of Contents Who is the Intended Audience? What is Included in the CD? PostgreSQL is one of the most successful Open Source projects available. It is ...

Who is the Intended Audience?

This book is intended to be used by any person who is interested in utilizing the PostgreSQL database management system. The reader should be familiar with ...

Chapter 1. What is PostgreSQL?

Table of Contents Open Source Free Version PostgreSQL Feature Set Where to Proceed from Here PostgreSQL is an Object-Relational Database Management System ( ...

Open Source Free Version

PostgreSQL is an Open Source project. Open Source by definition means that you can obtain the source code, use the program, and/or modify it freely without the ...

Commercial PostgreSQL Products

There are two primary companies that provide commercial PostgreSQL products. These companies have re-branded PostgreSQL to fit into their own product lines.

Open Source versus Commercial Products

The Open Source version of PostgreSQL only includes the database management system and the associated programming interfaces. The Great Bridge and Red Hat ...

The Bottom Line

Marketing would suggest that the commercial versions of PostgreSQL are somehow objectively superior to the Open Source versions of PostgreSQL. In reality, this ...

Commercial Support

Outside of Great Bridge and Red Hat, there are many companies that provide consulting services to the PostgreSQL community. The following is a small list of ...

Community Support

The PostgreSQL community provides active support to users of PostgreSQL via a number of mailing lists. There are several user mailing lists that you can ...

PostgreSQL Feature Set

As stated previously in this chapter, PostgreSQL is widely considered the most advanced Open Source database in the world. PostgreSQL provides a wealth of ...

Where to Proceed from Here

Now that your introduction to PostgreSQL is complete, there are several places that you can proceed to. We have provided the following list as a synopsis of ...

Chapter 2. Installing PostgreSQL

Table of Contents Preparing for Installation 10 Steps to PostgreSQL Installation This chapter focuses on the requirements and steps involved with installing ...

Preparing for Installation

The installation of PostgreSQL is not difficult. However, there are some software requirements that you will need for the PostgreSQL compilation. All of the ...

Installation Requirements

GNU make GNU make is commonly known as gmake but is normally referred to as make on GNU based systems such as Linux. Note For consistency we will refer to the ...

Optional Packages

The following are some optional packages that you may want to have installed: GNU Readline Library The GNU Readline library greatly increases the usability of ...

10 Steps to PostgreSQL Installation

PostgreSQL is included on the CD but you may want to visit the PostgreSQL site at http://www.postgresql.org to see if there is a newer version available. The ...

Step 1: Installing the PostgreSQL Source Package.

When you have acquired the source for PostgreSQL you are going to compile, you will wish to copy the PostgreSQL source package to a temporary folder. This ...

Step 2: Configuring the Source Tree

This is step where you will configure the source tree and specify installation options specific to your needs. To use the default installation script, use the ...

Step 3: Compiling the Source

After using the configure command, you may begin compiling the PostgreSQL source by entering the gmake command. Example 2-6. Compiling the Source with GNU Make ...

Step 4: Regression Testing

Regression tests are an optional but recommended group of tests. The regression tests help verify that PostgreSQL will run as expected after you have compiled ...

Step 5: Installing Compiled Programs and Libraries

Upgrading If you are going to upgrade PostgreSQL, instead of installing from scratch. Make sure you back up your old database. Information on performing ...

Step 6: Creating the "postgres" user

A Unix user account needs to be created to manage the PostgreSQL database. Typically this user is called postgres, but can be anything that you choose. For ...

Step 7: Setting Environment Variables.

The use of the environment variables is not required. However, it is helpful when performing tasks within PostgreSQL including starting/shutting down the ...

Step 8: Initializing and Starting PostgreSQL

If have a SysV style Unix system (such as RedHat, Linux, Mandrake Linux, Solaris, or UnixWare, then you can skip this step and go on to step 9. Otherwise, to ...

Step 9: Configuring the PostgreSQL SysV Script

The SysV script will allow the graceful control of the PostgreSQL database through the use of the SysV runlevel system. This includes, starting, stopping and ...

Step 10: Creating a Database

Now that the PostgreSQL database is running, you have the option of using the default database named template1. If you create a new database and you would like ...

Part II: Using PostgreSQL

Chapter 3. Understanding SQL

Table of Contents Introduction to SQL Introduction to Relational Databases SQL Statements Data Types Tables in PostgreSQL This chapter discusses the history ...

A Brief History of SQL

The relational model, which SQL draws much of its conceptual core from, was first formally defined in 1970 by Dr. E. F. Codd, a researcher for IBM, in a paper ...

SQL and its Predecessors

SQL is based largely on tuple relational calculus and relational algebra. Relational algebra, introduced by E. F. Codd in 1972, provided the basic concepts ...

SQL Standards

SQL has existed as an official standard since the adoption of the ANSI ( American National Standards Institute ) standard (X3.135) in 1986, and the ISO ( ...

Introduction to Relational Databases

PostgreSQL is a sophisticated object-relational database management system (ORDBMS). An ORDBMS is an extension of the more traditional relational database ...

Understanding Databases

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 ...

Understanding Tables

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 ...

SQL Statements

Conceptual information on relational databases and tables is of course entirely moot if you don't have any concept of how to directly interact with your data.

The Anatomy of a SQL Statement

SQL statements always begin with a command , (a word, or group of words, which describes what action the statement will initiate). The command can be called ...

Formatting Considerations

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 ...

Keywords and Identifiers

Keywords are any reserved SQL terms which have a fixed, systematic meaning to the server. Some common examples of keywords are INSERT , UPDATE , SELECT , and ...

Quoted Identifiers

While not normally required, identifiers can be written inside of quotes, which causes their case to be interpretted literally. For example, if we want to view ...

When quotes are required

The only instances where you are required to use quotes are either when a database object's identifier is identical to a keyword, or when the identifier has at ...

Identifier validity

Both keywords and identifier names in PostgreSQL have a maximum length limit of 31 characters. Parsed keywords or identifiers over that length limit are ...

Constants

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 ...

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 ...

Bit string constants

Bit string constants provide a way to directly represent a binary value with an arbitrary sequence of ones and zeroes. Similarly to string constants, they are ...

Integer constants

Far more frequently used than bit string constants are integer constants. PostgreSQL identifies an integer constant as any token which consists solely of a ...

Floating point constants

A floating point constant is similar to an integer constant, but it is used to represent decimal values as well as whole integers. These are required whenever ...

Boolean constants

A boolean constant is much simpler than any other constant values recognized by PostgreSQL, as it may only consist of one of two possible values: true , or ...

Punctuation symbols

Some special character symbols help to make up the punctuation of a SQL statement, much like parentheses, periods and commas do in the English language. Table ...

Operator symbols

An operator is another type of special character symbol which is used to perform an operation on identifiers, or constants, returning a resultant value. These ...

Comments

Comments are blocks of text which, through special character sequences, can be used to embed non-SQL text within SQL code. These can be used within blocks of ...

Putting it All Together

In summary, a SQL statement is comprised of tokens, where each token can represent either a keyword, an identifier, a quoted identifier, a constant, or a ...

Data Types

SQL is considered a strongly typed language . This means that any piece of data represented by PostgreSQL has an associated data type, even if it is not ...

Type Coercion

PostgreSQL supports three separate conventions for type coercion (also called type casting , or explicit type casting ). Type coercion is a somewhat ugly ...

NULL Values

The SQL keyword NULL is a very special exception to the rules that we have introduced regarding columns and discrete data types. While a column may only have ...

Boolean Values

A boolean value is a simple data type, as it can only represent values of true , or false . PostgreSQL supports the SQL99-defined boolean type, with a ...

Character Types

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, ...

Numeric Types

PostgreSQL's numeric types are used to represent both whole integers and decimal floating point values. From a general perspective, PostgreSQL's supported ...

The numeric type

The numeric (also known as decimal ) type is a specially designed numeric data type which can represent arbitrarily large and precise values, within a fixed ...

The monetary type

The money type is an automatically translated numeric type that formats to and from US-style currency notation, and plain numeric values. As of the writing of ...

The serial type

The serial type is non-standard, but useful, shortcut which allows you to easily create unique identifier columns within tables. It literally combines the ...

Date and Time Types

Date and time types are a convenient way to store date and time related data in a uniform SQL data structure, without having to worry about the conventions ...

Backward Compatibility

To ensure compatibility with earlier versions of PostgreSQL, the developers have continued to provide the older datetime and timespan data types. The datetime ...

Date Conventions

Date input can be accepted by PostgreSQL in many common formats, including the ISO-8601 format, the traditional SQL format, the original PostgreSQL format, and ...

Time Conventions

Time values, like date values, may be entered in to a table in a number of ways. Commonly used formats are listed in Table 3-20 . These apply to values of type ...

Timestamps

The PostgreSQL timestamp combines the functionality of the PostgreSQL date and time types into a single data type. The syntax of a timestamp value consists of ...

Intervals

The SQL92 standard specifies a data typed called an interval , which represents a fixed span of time. By itself, an interval represents only a quantity of time ...

Built-in date and time constants

PostgreSQL provides three built-in functions for retreiving the current time, date, and timestamp. These are aptly named current_date , current_time , and ...

Geometric Types

Geometric types in PostgreSQL represent two dimensional spatial objects. These types are not standard SQL data types, and will not be discussed in depth in ...

Arrays

The original relational model specifies that the values represented by columns within a table be an atomic piece of data, object-relational database systems ...

Arrays in Tables

When defining an array, the syntax allows for the array to be defined either as fixed-length or variable-length, however as of PostgreSQL 7.1.2, the fixed- ...

Array Constants

In order to actually insert array values into a table column, you of course need a way to refer to several values as an array in a SQL statement. The formal ...

Tables in PostgreSQL

If you are already familiar with SQL or other RDBMS packages, you probably already have a solid understanding of many of the relational database concepts put ...

System Columns

PostgreSQL defines a series of system columns in all tables, which are normally invisible to the user (e.g., they will not be shown by queries unless ...

Object Identifiers

As described in the section called Understanding Tables , each database consists of tables, and each table consists of at least one named column. These tables ...

Planning Ahead

Before you start creating any tables, we suggest that you take some extra time to plan out your intended database objects, deciding the names and types for ...

Chapter 4. Applying SQL with PostgreSQL

Table of Contents Introduction to psql Using Tables Managing Data with SQL Using Views Using Operators Using Functions Debugging SQL Queries Included Clients ...

Starting psql

To start psql , you may want to first be sure that you have either copied the psql binary into a path which is in your system PATH variable (e.g., /usr/bin ), ...

Introduction to psql Syntax

When you first start psql , you will be greeted with a brief synopsis of four essential psql slash commands: \h for SQL help, \? for help on psql -specific ...

Executing Queries

Entering and executing queries within psql can be done one of a few different ways. When using the client in interactive mode you will probably be directly ...

Entering queries at the psql prompt

To enter queries directly into the prompt, open psql and make sure you are connected to the correct database (and logged in as the correct user). You should be ...

Editing the query buffer

Use the \e command to edit the current query buffer. Doing so can be extremely useful when entering queries and statements in psql , as you can easily go view ...

Using Tables

As described in Chapter 3 , tables are the fundamental building blocks with which to store data within your database. Before you can begin to add, retrieve, or ...

Creating Tables with CREATE TABLE

The SQL command to create a table is CREATE TABLE . This command requires, at minimum, a name for the new table, as well as a grouped description of each ...

CREATE TABLE syntax

The following is the syntax for CREATE TABLE , which is described in detail in Table 4-1 . CREATE [ TEMPORARY | TEMP ] TABLE table_name ( { column_name type [ ...

Creating an example table

As an example, the syntax to create Book Town's books table is demonstrated in Example 4-6 . Example 4-6. Creating the books Table booktown=# CREATE TABLE ...

Examining a created table

Once created, you may use the \d describe command (followed by the table name) within psql to display the structure of the table, and its constraints (if any).

Altering Tables with ALTER TABLE

Most mature RDBMSs offer methods by which you may alter the properties of existing tables via the ALTER keyword. The PostgreSQL implementation of ALTER TABLE ...

ALTER TABLE syntax

The various forms of syntax for ALTER TABLE are summarized in Table 4-3 , and described in detail in the following sections. Table 4-3. ALTER TABLE Syntax ...

Adding columns

The columns of a table may be extended by passing the ADD COLUMN clause to the ALTER TABLE command. The following syntax describes adding a column where ...

Setting and removing default values

The most flexible table modification pertains to the default values of columns. These values may be both set and removed from a column with relative ease via ...

Re-naming the table

A table may be safely re-named by passing the RENAME clause to the ALTER TABLE command. The following is the syntax to re-name a table where table_name is the ...

Re-naming columns

A table's columns may be safely re-named in PostgreSQL without modifying the data which is represented in that table. Re-naming a column can obviously be a ...

Adding constraints

Constraints may be added in a limited fashion after a table has been created. As of PostgreSQL 7.1.x, only foreign key constraints may be added to an existing ...

Changing ownership

By default, the creator of a table is automatically its owner . The owner has all rights that can be associated with a table, in addition to the ability to ...

Re-structuring Existing Tables

While you have the ability to arbitrarily add new columns to existing tables, remember that (as of PostgreSQL 7.1.x) you cannot drop columns from existing ...

Re-structuring with CREATE TABLE AS

One common technique to re-structure a table is to use the CREATE TABLE command in conjunction with the AS clause, and a valid SQL query, in order to re- ...

Re-structuring with CREATE TABLE and INSERT INTO

If you require a more specifically defined table than that created by the CREATE TABLE AS (e.g., one with column constraints), you can replicate the effect of ...

Destroying Tables with DROP TABLE

The SQL command to permanently destroy a table is DROP TABLE . The following is the syntax for DROP TABLE , where table is the table that you wish to destroy: ...

Managing Data with SQL

Adding Data with INSERT and COPY

Once you have created your table with the necessary specifications, the next logical step is of course to fill the table with data. There are generally three ...

Inserting new values

The following is the syntax of the INSERT INTO command, when used to insert new values, which is described in detail in Table 4-4 : INSERT INTO table_name [ ( ...

Inserting values from other tables with SELECT

If you already have values within one table (or across several other tables) that you wish to insert into a separate table, this can also be achieved with the ...

Creating tables from other tables

If you have an existing database table and you want to copy the complete table (both the column structure, and the row data), you can use the SELECT INTO ...

Copying values from external files

You can also use the COPY command to populate rows with values. This command copies from an ASCII file and inserts the values into the table rows. The path ...

Retrieving Rows with SELECT

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] expression [ AS output_name ] [, ...] [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] [ FROM ...

Qualifying with the WHERE clause

A SQL SELECT statement consists of a target list and a qualification. The target list is a list of columns to be returned and the qualification is the part ...

Joining tables with JOIN

As shown by the syntactical structure above, you can also view columns from different tables by performing a join. A join requires that both tables have an ...

Grouping rows with GROUP BY

When a Group By is performed, the parser logically collects rows that have the same value for a specified column and then it groups them together. The ...

Hiding and sorting with DISTINCT and ORDER BY

You can revise the way a table is displayed by using DISTINCT or ORDER BY . DISTINCT returns only one instance of a duplicated value (usually it is the first ...

Setting row range with LIMIT and OFFSET

LIMIT and OFFSET provide the capability to retrieve only a portion of the generated result from the query. If a limit count is specified, the number of rows ...

Using conditional expressions

The syntax for the case statement is as follows: CASE WHEN condition THEN action [WHEN ...] [ELSE otheraction] END The CASE statement is similar to the if/then/ ...

Using sub-queries

Sub-queries are most often used to test the search condition given in a where clause. We will refer to the book and author tables for the examples below. The ...

Modifying Rows with UPDATE

You can also change the value of data within existing rows using the UPDATE command. Suppose the values for the suggested price column from the publish table ...

Removing Rows with DELETE

To remove a row, or all rows from a table, use the DELETE command. The syntax is: DELETE FROM tablename WHERE condition ; For instance, if the Book Town store ...

Using Views

When working with SQL, there are times when you would like to keep your statements re-usable. This is especially the case when working with large, or intricate ...

Creating Views

The following is the syntax for creating a view: CREATE VIEW view AS query Table 4-7. CREATE VIEW Syntax Element Description CREATE VIEW SQL command (keyword) ...

Destroying Views

Destroying Views Use DROP VIEW to destroy a view. Operators are tokens which are used to perform operations on values (e.g., constants, or identifiers), and ...

Using Operators

Each operator has its own associated functionality, and can only operate on specific data types (see the section called Data Types in Chapter 3 , for more ...

Character and Text Operators

Concatentation Pattern matching operators Pattern matching keywords Numeric Operators Mathematical operators Table 4-8. Mathematical operators Operator ...

Comparison operators

Comparison operators are used to compare data types like integer or text, but they will always return a boolean value. Table 4-9 shows the available comparison ...

Comparison keywords

The BETWEEN keyword provides the ability to compare a value to a range of values. For instance, Example 4-41 shows a SELECT statement which looks for author ...

Binary operators

Binary operators also work on bit strings, as shown in the table below. Table 4-10. Bit String Operators Example Result B'10001' B'01101' 00001 B'10001' | B' ...

Logical Operators

Table 4-11 and Table 4-12 illustrate the boolean values returned for AND, OR, and NOT. Table 4-11. The AND and OR Operators a b a AND b a OR b TRUE TRUE TRUE ...

Operator Precedence

Table 4-13. Operator Precedence Operator / Element Associativity Description :: left PostgreSQL style typecast [] left array element selection . left table or ...

Using Functions

A function is an identifier which instructs PostgreSQL to perform a programmatic operation within a SQL statement, and then return a value from that operation.

Mathematical Functions

Table 4-14. Mathematical Functions in PostgreSQL Function Return Type Description abs (x) (same as argument type) absolute value cbrt (double precision) double ...

Built-in Aggregate Functions

Table 4-16. Aggregate Functions Function Description Support Type Return Type AVG ( expression ) the average of all input values (arithmetic mean) small int, ...

Aggregate Expressions

An aggregate expression represents the application of an aggregate function across the rows selected by a query. An aggregate function takes in multiple inputs ...

Positional Parameters

A positional parameter reference is typically used in a SQL function definition statement to indicate a parameter in a SQL function. The syntax to specify a ...

Debugging SQL Queries

The EXPLAIN command can be used to see the estimated time and execution plan the query optimizer uses to execute a sequel statement. The parameters for the ...

Included Clients

The two most accessible clients usable with PostgreSQL databases (both of which can be installed easily during compilation of the PostgreSQL source code) are ...

psql: Advanced Topics

Basic information about the psql client is included earlier in this chapter, but there are many more powerful features for an adept user to become accustomed ...

Command explanations

Formatting commands There are several meta commands you can use to format output. These include pset , \a , \C , \f , \H , \t , \T , and \x . Most of these ...

Entering queries using external files

As it is possible to use psql to enter queries from the prompt, it is also possible to create queries and statements within files, then let psql read the files ...

The readline and history libraries

The psql client supports some of the same command-tracking features that the bash shell supports; namely, the reverse-i-search, tab completion, and command ...

Variable substitution

The psql client allows you to modify and create variables using the \set slash command and delete them with the \unset slash commands. Variables within psql ...

About the prompt

The psql client supports the modification of its prompt. This can be helpful for displaying various pieces of information in an obvious way (what could be more ...

Modifying the prompt

To modify the prompt, use \set to change the strings held by the three prompt variables. When defining your prompt strings, use % to designate that you wish ...

Prompt examples

Combine the different substitution characters to form whatever prompt you would like. Example 4-61. Setting the Prompt to Include Database Host, the Username, ...

PgAccess: A Graphical Client

PgAccess is a graphical administration application for PostgreSQL. It is designed to be similar to PC database software like Microsoft Access. The interface ...

Installation and basic configuration

There are relatively few things you will need to do to configure PgAccess for use with PostgreSQL. Most importantly, you will need to make sure that Tcl/Tk is ...

Managing users

Not only does PgAccess allow you to modify and view elements of your database, it also provides the ability to graphically modify and manage user accounts ...

Managing groups

As of version 0.98.7, PgAccess does not support the management of groups. You can use the command line interface psql to make and manage PostgreSQL user groups.

Creating Databases

To create a database using PgAccess, click the Database menu option at the top of the screen, then click New and type in the name you wish to give the database.

Creating Tables

It is relatively easy to create and modify tables from within PgAccess due to the simple, graphical interface. First click the Tables tab, and then click the ...

The tables tab

Table name The name of the table you wish to create within PostgreSQL. Inherits You can set what table(s) this table will inherit attributes from. A list of ...

Adding fields

To add a field to the table, set its attributes with the field name , type , size , and Default value fields, and set its options with the field cannot be null ...

Inserting values

It is possible to insert values into a table using PgAccess; in fact, the process to do so is fairly simple. Click on the Table tab to view the list of tables, ...

Deleting values

To delete values from a table, open it in the same manner you would when attempting to insert values: click on the Tables tab, then click on the table you wish ...

Performing queries

As should be expected, you are also able to design, edit, and run queries through PgAccess. Click on the Queries tab to view a list of the defined queries ...

Creating functions

Creating functions within PgAccess is also fairly simple. Click on the Functions tab, then click New . You should now be presented with the Function windows.

Chapter 5. Advanced PostgreSQL Features

Table of Contents Extending PostgreSQL Indices Inheritance Arrays Constraints Triggers Sequences Replication This part includes subjects such as using indices, ...

Extending PostgreSQL

Creating New Data Types

Creating New Operators

The maximum length of an operator string is technically 31, though in practice you will rarely see an operator string longer than two or three characters. Some ...

Creating New Functions

Indices

Indices contribute to faster query output retrievals. This sect1 will explain the different types of indices available, as well as when you should use one type ...

Using an Index

A common database has tables which often need to be queried based on a certain column. For example, the booktown database has an invoice table with the ...

Index Types

There are three main index types. Each type is used for a specific query because they rely upon different algorithm calculations. The three types of indices ...

Multi Column Indices

An index is not limited to one per table. It can be defined for more than one column in a table. For instance, the shipped orders table follows the ...

Unique Indices

Similar to keys, indices can be specified as unique. This means that the specified column does not contain rows with a repeating value. Multi-columns can also ...

Functional Indices

A functional index is defined as an index that points to the result of a function. The function is a calculation that was applied to one or more columns of a ...

Primary Keys

A primary key is a field used to identify a row. Unique specifies that there cannot be any repeating values. A table can contains columns which have the ...

Inheritance

With PostgreSQL, a table can inherit from one or more other tables. When performing a query on an inherited table, the query can refer to either all rows of a ...

Arrays Creation and Insertion

A base type array uses the pre-defined data types to create the array. While a user-defined array uses types created by the user. Here are a few rules to ...

Modifications and Updates

To help visualize this array, the following table shows the values and the index number of the array index location. Table 5-8. Fixed Week Array Index Value [1] ...

Constraints

Constraints are used to restrict allowable data values in table column(s). Constraints are useful for error checking by enforcing rules on the data values. The ...

Column Constraints

If you perform a slash h (\h) on create table, you may have noticed that there are additional notes on how to create a table or column constraint. The portion ...

Table Constraints

Unlike column constraints, a table constraint can be defined on one or more columns of a table. There are five variations of a table constraint. They are: ...

Destroying a Constraint

When you destroy a table, you implicitly destroy the constraints on that table. Therefore, if you want to remove a table constraint from the database, use the ...

Triggers

In a given database, there are many possible events. These events which causes a change to the contents of a table sometimes should have another associated ...

Creating a Trigger

A trigger returns the type opaque. Opaque data type is used only by internal functions that call other functions. Functions defined to return type opaque ...

Advanced Trigger Functions

The example shown in the last section is a round about way of tracking user modifications and deletions from/to the table. A better way to implement the idea ...

Destroying a Trigger

Use the DROP TRIGGER command to remove a trigger from the database. Similar to the CREATE TRIGGER command, using this command requires you to be the owner of ...

Sequences

Creating a Sequence

A sequence is a database object which generates a consecutive order of numbers. Sequences are listed under the list of database relations (this list appears ...

Destroying a Sequence

To destroy a sequence generator, use the drop sequence command. You can list several sequences to be dropped at a time. The syntax for drop sequence is: DROP ...

Replication

Replication is used by many businesses, especially financial institutions. For instance, many banks use complex replication systems to link their security ...

What is Replication?

Replication is the server process of copying data modifications from one location to another. The main characteristics of replication which make it appealing ...

Obtaining the eR Server

Also known as Rserv The eR server is the enterprise Replication Server for PostgreSQL. To obtain the eR server, go to the web site at www.pgsql.com. Or check ...

Installing the Pg module

You can use the Perl utility called cpan to install the Pg module. We will not cover cpan in its entirety. Details on configuring and using cpan is beyond the ...

Configuring the eR Server

Step 1: Dumping the database Go to the master machine that stores the database. Use the pg_dump command to dump the database to a file. For more information, ...

Replication Testing

If you want to perform replication testing, then you need to install PostgreSQL with tcl. If you have not done so yet, then you can go through the installation ...

Part III: Administrating PostgreSQL

Chapter 6. Authentication and Encryption

Table of Contents Client Authentication Encrypting sessions The following chapter is designed to give you the basic information needed to correctly configure ...

Client Authentication

Client authentication is a central feature to using PostgreSQL, without it, anyone could connect to your database and retrieve your data. PostgreSQL has the ...

Password Authentication

PostgreSQL user passwords are stored in the pg_shadow table. The use of a password allows defined users a way to identify themselves and access a database that ...

The pg_hba.conf File

We mentioned previously that the pg_hba.conf file enables client authentication to occur between the PostgreSQL server and the client application. Upon the ...

Structure of pg_hba.conf

The pg_hba.conf file contains records that defines the authentication methods and options PostgreSQL should use during client authentication process. The ...

Example pg_hba.conf entries.

The following is a series of examples that can be used within the pg_hba.conf . The first allows a single IP to connect to all databases, without the use of a ...

The pg_ident.conf file

Uses the pg_ident.conf file to match the connecting user with the PostgreSQL database user name. These users are on the local network (your network structure ...

The ident keyword

This key word is used in the pg_hba.conf file for users who have an ident map defined. An ident map is a record that identifies the operating system users with ...

Authentication Failures

When authentication failures and related problems occur, similar messages like the following examples will appear. Here are some common error messages that you ...

Encrypting sessions

In the current day, privacy and data integrity have become one of the most talked about areas of computing. It seems like almost everyday somebody has been ...

Encryption options

--with-ssl PostgreSQL has the capability to support SSL with the --with-ssl configuration option. This option is a good option if you are going to be doing the ...

SSH/OpenSSH

OpenSSH is an excellent option for using encryption between your client and server. OpenSSH is considered a standard amongst security professionals and system ...

Configuring and Using Stunnel

Many users of PostgreSQL will be interested to know that there is a dependable encryption method available for use with the software. If you wish to encrypt ...

Chapter 7. Database Management

Table of Contents Creating a Database Accessing the Database Removing a Database Maintaining a Database Backing up and Restoring Data

Creating a Database

To create a database requires you to have the CREATE DATABASE privilege. If you are a normal user, you don't need to have these permissions. You can use an ...

Accessing the Database

There are several ways to access the database besides through the PostgreSQL terminal monitor program. A few of these are: Native frontend tool Using an ...

Removing a Database

To destroy an existing database, use this command at the Unix shell: dropdb databasename The alternative way is to use psql to remove this database: DROP ...

Maintaining a Database

One of the ways to maintain a database and the tables in it are by using the VACUUM or vacuumdb command. VACUUM is a PostgreSQL command that can be accessed ...

Using Vacuum

Vacuum cleans a Postgres database by cleaning records from rolled back transactions. It also updates statistics stored in the system catalogs. These statistics ...

Using Vacuumdb

Similar to VACUUM , vacuumdb cleans and analyzes a PostgreSQL database. What makes vacuumdb different is that it allows the user to specify a database name or ...

Backing up and Restoring Data

Similar to your networking system, your database system also needs to be backed up regularly. Before performing backups, you should have a basic understanding ...

PostgreSQL Dump

The PostgreSQL dump generates a text file that contains SQL commands which regenerates the database into the previous state it was in. The command to ...

Pg_dumpall

The difference between pg_dump and pg_dumpall is that pg_dump is not as suitable for backing up database clusters. A database cluster is a group of databases ...

Large Objects

PostgreSQL is capable of handling files larger than the allowed maximum file size on your system. (Of course, this also depends on your type system and ...

Restoring the Dump

The text file created by pg_dump or pg_dumpall needs to be restored into an existing database. If a database does not exist to hold the data from this ...

Backing up the File System

The file system are the files used by PostgreSQL to store data from the database. These files can be directly copied and made into a backup. You can choose the ...

Migration

New versions of PostgreSQL are periodically released. Most likely, the storage format for internal data change between major releases. If you decide to update ...

Chapter 8. User Management

Table of Contents Adding users Users User Attributes Groups Privileges Database users are separate from the operating system users. The database users are able ...

Adding users

From the command line There are two methods to create this user. With either method, access to postgres is required (we're assuming that no other users with ...

From the psql Application

The second method is to start a psql session and go into the template1 database. This involves the command: psql template1 This message should appear: Type: \ ...

Users

To create a user, use the CREATE USER command: CREATE USER username Username follows the same syntactical rules as SQL identifiers. When specifying a username , ...

User Attributes

Privileges are the actions a user is limited to perform on a database object. These privileges are defined by attributes and interactions with the client ...

Groups

The Book Town company is divided into three main departments where employees who need access to the same types of programs are grouped together. The same ...

Privileges

When a user creates a database, they automatically become the owner of that database. The owner and the database superuser are the only users allowed to modify, ...

Part IV: Programming with PostgreSQL

Chapter 9. PL/pgSQL

Table of Contents Adding PL/pgSQL to your Database Creating Functions Handling RECORDS PL/pgSQL and Triggers PL/pgSQL is a loadable procedural language for the ...

Adding PL/pgSQL to your Database

Before you can use the PL/pgSQL language, you will need to add it to your database. Use these steps to add the PL/pgSQL language to any existing database: ...

Creating Functions

There are several ways to create function with PL/pgSQL. You can use the editor of your choice to develop the code for the function. Once you have written the ...

Code Blocks

PL/pgSQL is a structured language designed around blocks. This means that variables declared in a block can not be used after the closing of the code block. In ...

PL/pgSQL Comments

Commenting in PL/pgSQL is done through two methods. The first method is a single line comment. These comments begin with two dashes (--) to express the line is ...

Declarations

Variables are used to store and manipulate data. Variables are always defined in the declarations section of a block before it is used elsewhere in the block ...

Variables

Variables are the names given to objects which have a defined data type. The possible variable data types are any valid SQL data type, such as integer, date, ...

Assignment

Assignment statements are important to any programming language. They allow the programmer to manipulate the data. Assignment statements can be used to store ...

Returning from a Function

The return statement terminates the execution of the current function and returns to the routine that called the function. You are allowed to use the return ...

Concatenating

Concatenating means to combine two strings together. This simple example joins the first and second string of words supplied to become a compound word. Example ...

Renaming

The RENAME keyword allows you to change the name of a variable, record, or row. This is helpful if the NEW or OLD keyword is referenced by another name inside ...

Aliases

An alias provides a way to identify the user input. Aliases allow the programmer to refer to a name when referring to a variable passed by a function. A ...

Exception Handling

The RAISE command is provided by PL/pgSQL to give the user the ability to output messages to the PostgreSQL elog mechanism. The elog() function is used to send ...

Expressions

Expressions are calculations which return a resulting value. They can be used to enhance data manipulation. In some cases, expressions are used to limit the ...

Attributes

An attribute is a characteristic that can be used to further define a variable. There are two attributes provided to declare variables with the same structure ...

Control Structures

Control structures are ways to regulate the data. Control structures are important because they: Are very useful in writing code that gives flexibility to the ...

IF statements

IF statements allow the programmer to specify an action to be taken when a condition occurs. There are three types of IF statements. They are: IF THEN This ...

Iteration

The second type of control structure is through iteration. It controls the execution of your programs. This is done through the commands: LOOP , WHILE , FOR , ...

Handling RECORDS

A record is a special data type that holds one database row from the SELECT statement. Records are similar to row types. The only difference between row types ...

Iterating Through Records

A special FOR loop is provided to iterate through records. It is defined to iterate through the results of a SELECT statement and give the ability to ...

PL/pgSQL and Triggers

Triggers Functions can be defined to automatically start running when an action such as INSERT / UPDATE / DELETE occurs. This is performed through a trigger.

Chapter 10. JDBC

Table of Contents Getting Set Up Basic JDBC Syntax JDBC is the Java DataBase Connection. It is a simple, generic, and portable way of interacting with ...

Getting Set Up

This section assumes that you already have a PostgreSQL database set up and ready to go. Make sure that you have it set to accept incoming IP connections. This ...

Using the built driver

First, add postgresql.jar to your CLASSPATH. This can be done either by setting your CLASSPATH environment variable, or by passing it as an argument on the ...

Basic JDBC Syntax

This section will be a brief intro to JDBC. It will talk about the basics of JDBC, issues, caveats, and so forth, but won't go into detail about the specifics ...

Basic JDBC

At the end of the last example, we made a Connection object, representing our physical connection to the database. Now we can use this Connection object to ...

Advanced JDBC

As mentioned above, there are two additional types of statements available in JDBC- PreparedStatement and CallableStatement. CallableStatment is a proprietary ...

Issues specific to PostgreSQL and JDBC

This section will detail three common issues with JDBC, just to save you the trouble of puzzling through them yourself. The first is fairly simple. ResultSets ...