jburton@scw.org
peter_e@gmx.net
PostgreSQL is the world's most advanced Open Source database server.
PostgreSQL runs on all modern Unix-like operating systems, including the following:
| AIX |
| BeOS |
| BSD/OS (x86, Sparc) |
| Compaq Tru64 UNIX |
| Digital UNIX |
| FreeBSD (x86, Alpha) |
| HP-UX |
| Linux (x86, Alpha, ARM, MIPS, PowerPC, Sparc, S/390) |
| Mac OS X |
| NetBSD (x86, Alpha, ARM, m68k, PowerPC, Sparc, VAX) |
| OpenBSD (x86, Sparc) |
| SCO OpenServer |
| SCO UnixWare |
| SGI IRIX |
| SunOS 4 |
| Sun Solaris (x86, Sparc) |
| Windows 2000/NT |
Uses standard configure and make options for building from source
Binary packages are available for most Linux distributions and other platforms.
Can be installed and operated without root permissions
Included in most popular Linux and BSD distributions
Descended from INGRES and POSTGRES, cutting-edge academic database projects at the University of California at Berkeley
Maintained, improved, and supported by a stable Global Development Group, and a large number of contributors
Source code available for download, and anonymous CVS access is available
Licensed under BSD license, allowing truly free modifications and redistribution of binaries
Foreign keys: Supports standard CREATE TABLE ... FOREIGN KEY syntax for referential integrity. Supports different actions for updating and deleting, including cascading, restricting, and restoring to default or NULL values. These features are critical for data integrity in many applications. Foreign keys can be detected by database modeling tools, such as Computer Associates' ERwin, to ease database design and documentation.
Joins: Implements all SQL99 join types: inner join, left, right, full outer join, natural join. Optimizer has several join algorithms available.
Views: Views are stored SQL SELECT statements that are used to abstract complex views. For example, a common operation that joins four tables, with subselects, a WHERE clause, an ORDER BY clause, etc., can be given the name EMPLOYEE_INFO, and treated as a virtual table--including (if appropriate) INSERTS, UPDATES, and DELETEs. This allow database users to work more easily with the data, and requires less sophistication in query writing for most users.
Triggers: Triggers are procedures that are called by the database on defined actions. For example, a trigger could be used to log every time a record in inserted or deleted to or from a table, or to update a field every time another field has been changed.
Supports triggers before and/or after actions
Trigger functions can be written in C or a procedural language.
Includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, TIMESTAMP.
Additional types for geometric constructs (points, lines, etc.), TCP/IP network addresses, Ethernet card IDs, ISBN/ISSN, and more.
New types can be defined, along with necessary supporting functions and operators.
PostgreSQL supports storage of binary large objects, including pictures, sounds, or video. These objects can be retrieved in whole or part by client applications.
Support for international character sets, multibyte character encodings, Unicode.
Locale-aware for sorting, case-sensitivity, formatting.
Support for standard SQL conditions, such as CASE WHEN THEN, COALESCE, and NULLIF.
Subqueries are nested queries that allow complex questions to be answered entirely through the database. Using subqueries can simplify and speed up database applications.
Support for SELECT DISTINCT and SELECT DISTINCT ON ( column ), to show only unique rows, and matching data for unique values.
Full support for GROUP BY and aggregate (domain) functions, including COUNT(), SUM(), AVG(), MIN(), MAX(), STDDEV() and VARIANCE(). New aggregate functions can easily be created in C or any procedural language PostgreSQL supports.
Sub-selects in FROM clause, such as SELECT COUNT(x), AVG(x), SUM(x) FROM (SELECT SUM(weight) AS x FROM Items GROUP BY manufacturer) AS Items;
Support for UNION and UNION ALL, INTERSECT and EXCEPT.
Extensions for LIMIT and OFFSET to allow for the selection of any arbitrary number of records, e.g., SELECT * FROM Items ORDER BY cost LIMIT 5;
Comprehensive library of functions and operators:
Hundreds of built-in functions, including rich support for mathematical, date/time, and string manipulation.
Support functions for full-text indexing
ODBC functions, Oracle compatibility functions
Standard SQL LIKE matching and case-insensitive LIKE matching and full regular expression matching and case-insensitive regular expression matching
Loadable extension functions: soundex, cryptographic hash functions (including SHA1 and MD5), user-handled long-term cooperative locking
Online library of user functions at http://techdocs.postgresql.org
Users can add new functions and operators. User-defined functions allow database designer to encapsulate business logic in the database, rather than in the front-end.
Server-side user defined functions can be written in several languages:
| C |
| SQL |
| PL/pgSQL (very similar to Oracle's PL/SQL) |
| Tcl |
| Perl |
| Python (alpha) |
| Ruby |
Indexes: B-tree, R-tree, Hash, and Gist indexes. User-definable index methods. Functional indexes. Indexes can be added and removed at any time.
Rules: Rules are an advanced feature of PostgreSQL that allows a database designer to substitute any SQL table operation (SELECT, INSERT, DELETE, etc.) for another operation. Rules can be used to return different sets based on permissions, turn an attempted update into a delete, etc.
Temporary tables (which can be created by CREATE TABLE or SELECT INTO) are automatically dropped at the end of a database connection.
Transactions allow all data modifications to be encapsulated in atomic blocks. This allows the database to rollback and restore all original data if there is an error in the transaction or if the database server crashes. Transactions are a critical feature for ensuring data integrity in many applications.
Support for ROLLBACK
Serializable transaction isolation
Works transparently with transaction features of interface programs, such as Perl's DBI, Zope, JDBC and ODBC transactions, etc.
Multi-Version Concurrency Control (MVCC) for highly scalable concurrent applications:
Readers do not block writers and writers do not block readers.
"Better than row-level locking."
Various row and table level locks are available as well.
Backup and Recovery tools: PostgreSQL includes a tool (pg_dump) to handle backup of all database data. These backups, which can be performed while the database is being used, are platform-independent, human-readable, and can be stored as tar archives or in compressed formats. Database can be restored as a whole, or (using pg_restore) selectively to recover individual database elements.
User/group security model for database objects
Access to the database server itself can be restricted based on host, user name, database.
Supports Kerberos authentication.
Encrypted database connections with SSL or SSH.
Can make use of multiple CPUs.
TCP/IP network connections or local Unix domain sockets.
Virtual hosting capability
Virtually unlimited size for databases, tables, rows. Unlimited number of rows and indexes per table.
Perl (through standard DBI/DBD or native interface)
Python (through Pygres or PoPy)
PHP (support built into PHP)
Tcl
ODBC, and therefore many ODBC client programs, including Microsoft Access, StarOffice, Applix, etc.
JDBC (Java Database Connectivity)
Standard SQL Embedded C
native C and C++ API's
Emacs LISP
R (Open Source statistical package)
Zope (Open Source web application server)
With its support for transactions, defined functions, and views, PostgreSQL offers web developers a safe and structured programming environment. Almost all web development systems that uses Perl, Python, ODBC, PHP, or Tcl can use PostgreSQL.
Zope
OpenACS, an Open Source port of ArsDigita's ACS
ColdFusion
Mason
EmbPerl
mod_perl and DBI
Web groupware packages supporting PostgreSQL: Twig (www.screwdriver.net/twig)
Includes comprehensive manual set, including tutorial and reference, available in HTML or print format
Commercial, full length book available in print form, or online at http://www.postgresql.org/docs/awbook.html
Active mailing lists for support by the developers and user community
Comprehensive installation instructions and FAQ included in distribution.
Online resource listings at http://techdocs.postgresql.org
Commercial contracts are available from several companies, and consulting from hundreds of companies and independent consultants.
Commercially-packaged versions of PostgreSQL are available from Great Bridge LLC, http://www.greatbridge.org.