Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Practical PostgreSQL
A Note Regarding Supplemental Files
Preface
Who Is the Intended Audience?
Structure of This Book
Platform and Version Used
What Is Included on the CD?
Conventions Used in This Book
Acknowledgments
Comments and Questions
I. Introduction & Installation
1. What Is PostgreSQL?
Open Source Free Version
Commercial PostgreSQL Products
Open Source Versus Commercial Products
The Bottom Line
Commercial Support
Community Support
PostgreSQL Feature Set
Where to Proceed from Here
2. Installing PostgreSQL
Preparing for Installation
Required Software Packages
Optional Packages
Disk Space
10 Steps to Installing PostgreSQL
Step 1: Creating the “postgres” User
Step 2: Installing the PostgreSQL Source Package
Step 3: Configuring the Source Tree
Step 4: Compiling the Source
Step 5: Regression Testing
Step 6: Installing Compiled Programs and Libraries
Step 7: Setting Environment Variables
Step 8: Initializing and Starting PostgreSQL
Step 9: Configuring the PostgreSQL SysV Script
Step 10: Creating a Database
II. Using PostgreSQL
3. Understanding SQL
Introduction to SQL
A Brief History of SQL
SQL and Its Predecessors
SQL Standards
Introduction to Relational Databases
Understanding Databases
Understanding Tables
SQL Statements
The Anatomy of a SQL Statement
Token Formatting Considerations
Keywords and Identifiers
Quoted identifiers
When quotes are required
Identifier validity
Constants
String constants
Bit string constants
Integer constants
Floating-point constants
Boolean constants
Special Character Symbols
Punctuation symbols
Operator symbols
Comments
Putting It All Together
Data Types
NULL Values
Boolean Values
Character Types
Numeric Types
The numeric type
The money type
The serial type
Date and Time Types
Backward compatibility
Date conventions
Time conventions
Timestamps
Intervals
Built-in date and time constants
Geometric Types
Arrays
Arrays in tables
Array constants
Type Coercion
Tables in PostgreSQL
System Columns
Object Identifiers
Planning Ahead
4. Using SQL with PostgreSQL
Introduction to psql
Starting psql
Introduction to psql Syntax
Executing Queries
Entering queries at the psql prompt
Editing the query buffer
Using Tables
Creating Tables with CREATE TABLE
CREATE TABLE syntax
Creating an example table
Examining a created table
Altering Tables with ALTER TABLE
Adding columns
Setting and removing default values
Renaming a table
Renaming columns
Adding constraints
Changing ownership
Restructuring Existing Tables
Restructuring with CREATE TABLE AS
Restructuring with CREATE TABLE and INSERT INTO
Destroying Tables with DROP TABLE
Adding Data with INSERT and COPY
Inserting New Values
Inserting Values from Other Tables with SELECT
Copying Values from External Files with COPY
Binary format
The difference between COPY and \copy
COPY TO
Copying WITH OIDS
Retrieving Rows with SELECT
A Simple SELECT
Specifying Target Columns
Expressions, Constants, and Aliases
Selecting Sources with the FROM Clause
Aliasing FROM Sources
Removing Duplicate Rows with DISTINCT
Qualifying with the WHERE Clause
Joining Data Sets with JOIN
Cross joins
Inner and outer join syntax
Inner joins
Outer joins
Intricate joins
Grouping Rows with GROUP BY
Sorting Rows with ORDER BY
Setting Row Range with LIMIT and OFFSET
Comparing Sets with UNION, INTERSECT and EXCEPT
Using Case Expressions
Creating Tables from Other Tables
Modifying Rows with UPDATE
Updating Entire Columns
Updating Several Columns
Updating from Several Sources
Removing Rows with DELETE
Using Sub-Queries
Using Views
Creating a View
Applying Views
Destroying a view
Further SQL Application
5. Operators and Functions
Operators
Using Operators
Character String Operators
Basic comparison
String concatenation
Regular expression matching operators
Numeric Operators
Mathematical operators
Numeric comparison operators
Numeric comparison keywords
Binary operators
Logical Operators
Using Operators with NULL
Operator Precedence
Functions
Using Functions
Mathematical Functions
abs( )
acos( )
asin( )
atan( )
atan2( )
cbrt( )
ceil( )
cos( )
cot( )
degrees( )
exp( )
floor( )
ln( )
log( )
mod( )
pi( )
pow( )
radians( )
random( )
round( )
sin( )
sqrt( )
tan( )
trunc( )
Character String Functions
ascii( )
btrim( )
char_length( )
chr( )
initcap( )
length( )
like( ) and ilike( )
lower( )
lpad( )
ltrim( )
octet_length( )
position( )
repeat( )
rpad( )
rtrim( )
strpos( )
substr( )
substring( )
to_ascii( )
translate( )
trim( )
upper( )
Date and Time Functions
current_date
current_time
current_timestamp
date_part( )
date_trunc( )
extract( )
isfinite( )
now( )
timeofday( )
Type Conversion Functions
bitfromint4( )
bittoint4( )
to_char( ) with numbers
to_char( ) with timestamps
to_date( )
to_number( )
to_timestamp( )
timestamp( )
Aggregate Functions
Aggregate expressions
avg( )
count( )
max( )
min( )
stddev( )
sum( )
variance( )
6. PostgreSQL Clients
The psql Client: Advanced Topics
Command Line Options
Slash Commands
Formatting commands
Information display commands
PostgreSQL and psql informative commands
Input and output commands
System commands
Using External Files to Enter Queries
The Readline and History Libraries
Variable Substitution
About the psql Prompt
Modifying the prompt
Prompt examples
PgAccess: A Graphical Client
Installation and Basic Configuration
Managing Users
Managing Groups
Creating Databases
Creating Tables
Adding fields to a table
Inserting and updating values
Deleting values
Using Queries
Manually designing a query
Using the visual designer
Executing a query
Modifying a query
Creating Functions
7. Advanced Features
Indices
Creating an Index
Unique indices
Index types
Functional indices
Destroying an Index
Advanced Table Techniques
Using Constraints
Column constraints
Table constraints
Adding a constraint
Removing a constraint
Inheritance
Creating a child table
Using inherited tables
Modifying inherited tables
Arrays
Creating an Array Column
Inserting Values into Array Columns
Selecting Values From Array Columns
Array subscripts
Array slices
Array dimensions
Updating Values in Array Columns
Automating Common Routines
Sequences
Creating a sequence
Viewing a sequence
Using a sequence
Destroying a sequence
Triggers
Creating a trigger
Viewing a trigger
Removing a trigger
Transactions and Cursors
Using Transaction Blocks
Using Cursors
Declaring a cursor
Fetching from a cursor
Moving a cursor
Closing a cursor
Extending PostgreSQL
Creating New Functions
Creating SQL functions
Creating C functions
Destroying functions
Creating New Operators
Creating an operator
Overloading an operator
Dropping an operator
III. Administrating PostgreSQL
8. Authentication and Encryption
Client Authentication
Password Authentication
The pg_hba.conf file
Structure of the pg_hba.conf file
Example pg_hba.conf entries
The pg_ident.conf file
Authentication Failure
Encrypting Sessions
Built-in SSL
SSH/OpenSSH
Configuring and Using Stunnel
OpenSSL
Stunnel
Knowing how to start Stunnel
Running Stunnel in daemon mode
Running with inetd
Wrapping up
9. Database Management
Starting and Stopping PostgreSQL
Using pg_ctl
Starting PostgreSQL with pg_ctl
Stopping PostgreSQL with pg_ctl
Restarting PostgreSQL with pg_ctl
Checking status of PostgreSQL with pg_ctl
Using the SysV Script
Calling postmaster Directly
Initializing the Filesystem
Initializing a Database Cluster
Initializing a Secondary Database Location
Creating and Removing a Database
Creating a Database
Using CREATE DATABASE
Using createdb
Removing a Database
Using DROP DATABASE
Using dropdb
Maintaining a Database
Using VACUUM
Using vacuumdb
Documenting a Database
Using COMMENT
Retrieving comments
Backing Up and Restoring Data
Using pg_dump
Using pg_dumpall
Restoring a Database
Using psql for plain text dumps
Using pg_restore for tarred and compressed dumps
When to Backup and Restore Data
When to backup
When to restore
Backing Up the Filesystem
10. User and Group Management
Managing Users
Viewing Users
Creating Users
Creating a user with the CREATE USER SQL command
Creating a user with the createuser script
Altering Users
Removing Users
Removing users with the DROP USER SQL command
Removing users with the dropuser operating system command
Managing Groups
Creating and Removing Groups
Creating a group
Removing a group
Associating Users with Groups
Adding a user to a group
Removing a user from a group
Granting Privileges
Understanding Access Control
Granting Privileges with GRANT
Restricting Rights with REVOKE
Using Views for Access Control
IV. PostgreSQL Programming
11. PL/pgSQL
Adding PL/pgSQL to Your Database
Adding PL/pgSQL to Your Database
Using psql to add PL/pgSQL
Using createlang to add PL/pgSQL
Language Structure
Code Blocks
Comments
Comment syntax
Good commenting style
Statements and Expressions
Statements
Expressions
Using Variables
Data Types
Declaration
Assignment
Argument Variables
Returning Variables
Attributes
The %TYPE attribute
The %ROWTYPE attribute
Concatenation
Controlling Program Flow
Conditional Statements
The IF/THEN statement
The IF/THEN/ELSE statement
The IF/THEN/ELSE/IF statement
Loops
The basic loop
The WHILE loop
The FOR loop
Handling Errors and Exceptions
Calling Functions
PL/pgSQL and Triggers
12. JDBC
Building the PostgreSQL JDBC Driver
Using the PostgreSQL Driver
Using JDBC
Basic JDBC Usage
Using Advanced JDBC Features
CallableStatement
PreparedStatement
ResultSetMetaData
DatabaseMetaData
Issues Specific to PostgreSQL and JDBC
13. LXP
Why Use LXP?
Core Features
Content Inclusion and Management
Direct SQL Methods and PostgreSQL Connectivity
Fingerless
Installing and Configuring LXP
Installing LXP
Using lxpinstall.sh
Manual installation
Nuts and Bolts: Configuring lxp.conf
General settings
Database settings
Understanding LXP Mark-Up
LXP Tags
LXP Regions
LXP Variables and Objects
Naming Conventions
Using Variables and Objects
CGI Arguments
CGI Arrays
Direct SQL objects
Global LXP objects
Using Cookies with LXP
Setting Cookies
Accessing Cookie Values
Tag Parsing
Variable Substitution
Object Variable Value Substitution
Entity substitution
Using <varparser>
Branching Logic
The <if> and <ifnot> Tags
Using <if>
Using <ifnot>
Nesting logic
Using <ifcookie> and <ifnotcookie>
The <else>, <elseif>, and <elseifnot> Tags
Loop Iteration
Content Inclusion
Including LXP Files
Including Flat Files
Including Token-Delimited Files
Including XML, RSS and RDF Files
Including External Content Types
Including SQL Content
Setting the database source
Accessing column values
Accessing SQL meta-data
Setting SQL object variables
Displaying Foreign Tags with <xtag>
V. Command Reference
14. PostgreSQL Command Reference
Abort
Synopsis
Parameters
Results
Examples
Alter Group
Synopsis
Parameters
Results
Description
Examples
Alter Table
Synopsis
Parameters
Results
Description
Examples
Alter User
Synopsis
Parameters & Keywords
Results
Description
Examples
Begin
Synopsis
Parameters
Results
Description
Examples
Close
Synopsis
Parameters
Results
Description
Examples
Cluster
Synopsis
Parameters
Results
Description
Examples
Comment
Synopsis
Parameters
Results
Description
Examples
Commit
Synopsis
Parameters
Results
Description
Examples
Copy
Synopsis
Parameters
Results
Description
Restrictions and limitations
File formatting
Examples
Create Aggregate
Synopsis
Parameters
Results
Description
Examples
Create Database
Synopsis
Parameters
Results
Description
Examples
Create Function
Synopsis
Parameters
Results
Description
Function attributes
Function overloading
Examples
Create Group
Synopsis
Parameters
Results
Description
Examples
Create Index
Synopsis
Parameters
Results
Description
Column index
Functional index
Operators and operator classes
Examples
Create Language
Synopsis
Parameters
Results
Description
Examples
Create Operator
Synopsis
Parameters
Results
Description
Examples
Create Rule
Synopsis
Parameters
Results
Description
Examples
Create Sequence
Synopsis
Parameters
Results
Description
Examples
Create Table
Synopsis
Parameters
Results
Description
Examples
Create Table As
Synopsis
Parameters
Results
Description
Example
Create Trigger
Synopsis
Parameters
Results
Description
Examples
Create Type
Synopsis
Parameters
Results
Description
Example
Create User
Synopsis
Parameters
Results
Description
Example
Create View
Synopsis
Parameters
Results
Description
Example
Current_Date
Synopsis
Parameters
Results
Description
Examples
Current_Time
Synopsis
Parameters
Results
Description
Examples
Current_Timestamp
Synopsis
Parameters
Results
Description
Examples
Current_User
Synopsis
Parameters
Results
Description
Examples
Declare
Synopsis
Parameters
Results
Description
Example
Delete
Synopsis
Parameters
Results
Description
Example
Drop Aggregate
Synopsis
Parameters
Results
Description
Examples
Drop Database
Synopsis
Parameters
Results
Description
Examples
Drop Function
Synopsis
Parameters
Results
Description
Example
Drop Group
Synopsis
Parameters
Results
Description
Example
Drop Index
Synopsis
Parameters
Results
Description
Example
Drop Language
Synopsis
Parameters
Results
Description
Example
Drop Operator
Synopsis
Parameters
Results
Description
Example
Drop Rule
Synopsis
Parameters
Results
Description
Example
Drop Sequence
Synopsis
Parameters
Results
Description
Example
Drop Table
Synopsis
Parameters
Results
Description
Example
Drop Trigger
Synopsis
Parameters
Results
Description
Examples
Drop Type
Synopsis
Parameters
Results
Description
Examples
Drop User
Synopsis
Parameters
Results
Description
Example
DROP VIEW
Synopsis
Parameters
Results
Description
Example
End
Synopsis
Parameters
Results
Description
Example
Explain
Synopsis
Parameters
Results
Description
Example
FETCH
Synopsis
Parameters
Results
Description
Examples
Grant
Synopsis
Parameters
Results
Description
Example
Insert
Synopsis
Parameters
Results
Description
Examples
Listen
Synopsis
Parameters
Results
Description
Example
Load
Synopsis
Parameters
Results
Description
Example
Lock
Synopsis
Parameters
Results
Description
Examples
Move
Synopsis
Parameters
Results
Description
Examples
Notify
Synopsis
Parameters
Results
Description
Transactions
Multiple signals
Example
Reindex
Synopsis
Parameters
Results
Description
Examples
Reset
Synopsis
Parameters
Results
Description
Example
Revoke
Synopsis
Parameters
Results
Description
Example
Rollback
Synopsis
Parameters
Results
Description
Example
Select
Synopsis
Parameters
Results
Description
Examples
Select Into
Synopsis
Parameters
Results
Description
Example
Set
Synopsis
Parameters
Results
Description
Examples
Set Constraints
Synopsis
Parameters
Results
Description
Example
Set Transaction
Synopsis
Parameters
Results
Description
Examples
Show
Synopsis
Parameters
Results
Description
Examples
Truncate
Synopsis
Parameters
Results
Description
Example
Unlisten
Synopsis
Examples
Results
Description
Example
Update
Synopsis
Parameters
Results
Description
Example
Vacuum
Synopsis
Parameters
Results
Description
Example
A. Multibyte Encoding Types
B. Backend Options for postgres
C. Binary COPY Format
The Header
Tuples
Trailer
D. Internal psql Variables
E. About the Authors
Index
Colophon
Copyright
← Prev
Back
Next →
← Prev
Back
Next →