Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 18, 2003

Getting Started With PostgreSQL

By James Koopmann

Looking for a database that won't cost you an arm and a leg? I am. Join me as I take a look at what PostgreSQL has to offer.

What is It

PostreSQL is an object-relational database management system. Nothing really more to say here except that, as you read on, it is a free database system that is taking stabs at proprietary database systems like Oracle, SQLServer, and DB2. It's ability to find and fill niches from as low as the hacker to the need of a start-up company and to the corporate data centers is proof of it's viability in the open-source community and robustness of a true database engine.

Is it Really Free

PostgreSQL is released under the BSD license which states that you can use, copy, modify, and distribute the software and its' documentation for any purpose, without fee, and without a written agreement'. Let me state that again, there is NO cost, NO fees, No licensing, and that means NO PROBLEMS. Wow! Just the blanket statement I need to forge ahead and not be concerned that I am doing something illegal. Of course, with this statement you also have to accept that there are no warranties for any damages what so ever that may happen when using the product. But then again what do you expect from a true open-source license?

You can start the process by going to the PostgreSQL website at http://www.postgreSQL.org. What this really relates to for you and me is that we can play, develop and go to market with no cost to database software. Yes, if I need more options or more robustness I can then port to another database. But hold on, we haven't gotten there yet.

What is the Platform

When looking for a database to "use for awhile" or in the case of free software I always need to ask myself "Do I have a platform to run it." PostgreSQL has a variety of Unix platforms and also runs on Windows with the help of Cygwin framework ( http://www.cygwin.com/ ). This is great for me since I will typically spend a bit of time playing with a database on my Win2K laptop while at work, or on the road while keeping my true installation for work at home on my Linux Servers.

How Do I Get the Software

For the windows environment it is quite easy and if you have installed Cygwin in the past, you might already have a copy of PostgreSQL, since it downloads with Cygwin. To install PostgreSQL, simply follow the procedures in http://www.postgresql.org/docs/faqs/text/FAQ_MSWIN. For getting the software for Linux, you really need to go to one of the many download mirror sites. Start by reading a little on how the distribution is organized on the site by opening the file ftp://ftp10.us.postgresql.org/pub/postgresql/README.dist-split. Then proceed to download the five tar balls at ftp://ftp10.us.postgresql.org/pub/postgresql/latest.

Ok, Does it have any Features

Of course, the database we choose has to have some features or else it won't offer us the ability to store and retrieve the information. Look at Listing 1 that I cut off the PostreSQL web site. You can also take a look at the press release (http://advocacy.postgresql.org/news/2002112801/ ) on the latest and greatest PostreSQL version (7.3.2). Surely, you can see that this database has enough to offer the casual user and is ready for prime time. Stay with me for future articles on how things really work. For now I am satisfied.

Listing 1:

Some of PostgreSQL features

  • Fully ACID compliant
  • ANSI SQL compliant
  • Referential Integrity
  • Replication (non-commercial and commercial solutions) allowing the duplication of the master database to multiple slave machines
  • Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG, Python, and Ruby
  • Rules
  • Views
  • Triggers
  • Unicode
  • Sequences
  • Inheritance
  • Outer Joins
  • Sub-selects
  • An open API
  • Stored Procedures
  • Native SSL support
  • Procedural languages
  • Hot stand-by (commercial solutions)
  • Better than row-level locking
  • Functional and Partial indexes
  • Native Kerberos authentication
  • Support for UNION, UNION ALL and EXCEPT queries
  • Loadable extensions offering SHA1, MD5, XML, and other functionality
  • Tools for generating portable SQL to share with other SQL-compliant systems
  • Extensible data type system providing for custom, user-defined datatypes and rapid development of new datatypes
  • Cross-database compatibility functions for easing the transition from other, less SQL-compliant RDBMS

What is the Support System

One of the things that impressed me the most when browsing the PostreSQL web site was the depth of community. By this I mean the amount of people that are sold on this open-source database and are contributing to its success. There is an actual set of manuals, published books, mailing lists to join at (http://archives.postgresql.org/ ), independent projects at (http://gborg.postgresql.org/ ), real technical documentation (http://techdocs.postgresql.org/ ), and consultancies that will help you out if need be. You can also get general "PR" information at http://advocacy.postgresql.org/ to learn about who PostreSQL is, the advantages to using open-source software, read a few case studies, and get involved in its success. Without the community of developers and contributors of this database, I surely wouldn't be interested in using it. After all, you don't want to hit a brick wall and not have a support system behind you.

Where to Go Now

After going through a deep dive into the PostreSQL web site, looking at the documentation available, seeing the support system behind it, and personally looking forward to an open-source database solution that is cost effective, capable, ready for prime time, and free, free, free, lets go download and install. I'll keep you posted.

» See All Articles by Columnist James Koopmann

PostgreSQL Archives

Latest Forum Threads
Postgresql Forum
Topic By Replies Updated
Creating a Table Where Date has to be Now OR Future Shadowayara 1 June 6th, 03:56 PM
Streaming Replication on a Single Database lbergeson 0 January 30th, 03:19 PM
Error - SQL state: 22P02 raj_db 1 December 20th, 08:30 AM
Searching in multi-dimensional array fields hdany 0 December 1st, 06:46 AM