PostgreSQL Introduction

Intro

Possibly the most robust freeware DB on earth. Some argue better than MySQL even though MySQL has a much larger following.

From the makers

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

Quick Tricks

Dumping The DB To Disc

A quick backup method:

# pg_dumpall --username=postgres > postgres-backup-all

Removing Duplicate Records

This is a fast way to find duplicate records in a table and delete them.

Note: Replace the various table and field names with yours.

Example:

--
-- Create a function which will return the max OID from a table
-- based on the unique id. In this case, it is hash
--
create  function maxoidhash( int8)
        returns int as
        'select max(oid) from codes where hash = $1'
        language 'sql';
--
-- Now delete
--
delete from codes where oid < maxoidhash(hash) ; 

--
-- Drop the function
--
drop function maxoidhash;

Listing Databases

Enter l from the command line prompt.

root@host [/home/modelloc/public_html/cgi-install]# psql -U modellocate -d template1
Password:
Welcome to psql 7.4.17, the PostgreSQL interactive terminal.

Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help on internal slash commands
       g or terminate with semicolon to execute query
       q to quit

template1=> l
              List of databases
        Name        |    Owner    | Encoding
--------------------+-------------+-----------
 alutask_webcollab  | alutask     | SQL_ASCII
 cyberspr_webcollab | cyberspr    | SQL_ASCII
 fvideo             | fvideo      | LATIN1
 fvideo_application | fvideo      | SQL_ASCII
 lance_emailendar   | lance       | SQL_ASCII
 phpbb              | modellocate | SQL_ASCII
 storeml            | modellocate | SQL_ASCII
 template0          | postgres    | SQL_ASCII
 template1          | postgres    | SQL_ASCII
(9 rows)

template1=>

Windows and PostgreSQL

What versions of Windows does PostgreSQL run on?

PostgreSQL is supported on Windows 2000, XP and 2003. At the time of writing, it has only been tested on 32 bit systems.

The most recent version of this document can be viewed at http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html.

The FAQ for compiling PostgreSQL on native Windows is at http://www.postgresql.org/files/documentation/faqs/FAQ_MINGW.html.

.Net Driver: http://pgfoundry.org/projects/npgsql

Notable Changes

A list of differences between versions that should merit special attention.

Since v7.3

Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type.[1]