books



previous page: 55 Ways to Have Fun With Google | by Philipp Lenssen
  
page up: Computer Booksno next page

Practical PostgreSQL | by John Worsley and Joshua Drake



Our hope is that by introducing this book to the community, we will give better understanding of PostgreSQL and its associated functionality. This book is intended to be a practical guide to PostgreSQL v7.1.x, although most of the book should apply to earlier and future releases of PostgreSQL. The content has been written to provide a means of allowing you to get comfortable with PostgreSQL in the most expedient fashion. Although we will touch on some academic database subjects, the discussion will be kept brief. Our core focus is to provide the reader with enough understanding of PostgreSQL to create a fully operational PostgreSQL database.

TitlePractical PostgreSQL
AuthorJohn Worsley and Joshua Drake
Publisher
Year2002
Copyright 2001-2002 Commandprompt, Inc
LicenseOpen Publication License, v1.0
AmazonPractical PostgreSQL

Copyright (c) 2001 by Command Prompt, Inc. This material may be distributed only subject to the terms and conditions set forth in the Open Publication License, v1.0 or later (the latest version is presently available at http://www.opencontent.org/openpub/).

'Distribution of substantively modified versions of this document is prohibited without the explicit permission of the copyright holder.' to the license reference or copy.

'Distribution of the work or derivative of the work in any standard (paper) book form is prohibited unless prior permission is obtained from the copyright holder.' to the license reference or copy.

Although every reasonable effort has been made to incorporate accurate and useful information into this book, the copyright holders make no representation about the suitability of this book or the information therein for any purpose. It is provided "as is" without expressed or implied warranty.

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









TOP
previous page: 55 Ways to Have Fun With Google | by Philipp Lenssen
  
page up: Computer Booksno next page