MyITBlog.com - IT Professionals Online Journal
 
  Home  |   Site News  |   About Us  |   Privacy  |   FAQ  |   Contact   Add Blog Entry  |  Login  |  Register   
   HOME  >  Programming  >  Database

PostgreSQL Tutorial
by exforsys on 28 Dec 2005, 10:51
Total Hits: 1393    Comments: 0   Votes: 0
Category: Database     

These are the PostgreSQL Notes - pretty much everything you find here is in the HTML documentation, Bruce's book or on the mailing lists. You might find this an easy access-point to the above documentation though.

NOTE - please don't link to individual pages in this document yet, things are still changing rapidly.

If you've used these notes a couple of times, you can help improve it by taking 30 seconds to tell me what you think.

There's a downloadable tar.gz archive of this document available here (approx 40kB download).


Table of Contents
1. Introduction
How to use this document
Other sources of information
Reaching the author
About the examples
Links to the manuals
Acknowledgements
2. Basic queries
Case insensitive searches
Escaping LIKE wildcards
Records without a corresponding key in another table
Returning the first N results
Returning the next N results
Returning the last N results
Selecting part of a date
Converting a date to unix timestamp (epoch seconds)
Combining two sets of results
3. Summarising results
Totalling a field
Finding the maximum/minimum value
Counting doesn't give me the total number of records in a table
I want to know how many types of X are in the system
Defining your own aggregate function
4. Advanced Queries
Having a dynamic field value without a lookup table
Handling subqueries with NULL
Joining across databases
Altering table definitions
5. Controlling Access to your data
Restricting users to certain records or certain fields with views
Preventing users from updating data with views
Letting users update views
Grant privileges on all tables in one go
6. Constraining your data
Making sure a related record exists
Making sure each record has a unique value
Allowing only certain values in a field
Preventing changes to a field
7. Automating processes
Enabling plpgsql
Recording a log of changes to a table
Updating a "last changed" field
8. Using PHP
Building PHP with PostgreSQL libraries
Things to be aware of
A small example
Building practical PHP apps
9. Using Perl
Building Perl's PostgreSQL libraries
Using the Pg module
TMTOWTDI: The DBI/DBD:Pg modules
10. Dealing with the system tables
Seeing what tables exist
Viewing triggers and their functions
Viewing function bodies
Viewing rules
11. Administrative Tasks
Reading/writing queries to/from a text-file
Importing data from a text-file
Exporting data to a text-file
Understanding built-in backslash commands
Automatic data-dumps with passwords
12. Performance Tuning, Debugging and Testing
Keeping index statistics up to date
Analysing a query-plan
Forcing use of an index
Setting logging
Debugging a complex query/function
Tweaking for performance
13. The example database
The tables
Data Generator