PostgreSQL is easy!

A step by step guide to installing and using PostgreSQL database server.

4 Download the database 4 Programming for SQL

4.1 Linux C++
4.2 Delphi ODBC
4.3 Perl CGI

Forward

Why I wrote this
Two reasons, first of all I know a lot of administrators that need a reliable SQL server, but not knowing anything about Linux they are very apprehensive about trying the Linux options available. One thing that we have in common is a lot of headaches from that other OS's SQL server. You know the one that costs an arm and a leg, takes down your network from time to time and does not play nice with other OS's. Well I am here to say that having installed about six of those SQL server and now having installed about eight PostgreSQL server, "Wow! This PostgreSQL server stuff is great!!!" I want every one to take a close look at PosgtreSQL before you through away your money on that other OS's attempt at an SQL server.

Second reason for writing this page. You learn more by teaching! If you read about it, you will forget in five minutes. If you watch some one do it, you forget by tomorrow. If you do it, you may remember for a while. Teach some one and you will understand it for a long time. There is a quote to go with that but I don't remember it. The PostgreSQL documentation is very good for technical people that have been doing SQL for some time, but for a newbie to read it and try to make sense of it would be hard. There are not very many examples in the PostgreSQL docs. And since I like to teach by example and people like to learn that way, here you go.

What is a database
Nag me to finish this.

What is SQL
Nag me to finish this.

What is PostgreSQL
Nag me to finish this.

Installing

Installing from RPM
You can download the RPM version of PostgreSQL from many sites including http://www.postgresql.org and it's many mirror servers. You can also find other servers hosting it and other database software by searching http://freshmeat.net, http://rpmfind.net. Most of the popular, large Linux distributions will have an installable version on the CD's that come with it. This would be the preferred way to install any program because you avoid looking for additional library files, setting up configurations and writing startup scripts. The RPM on you CD has all of that built it to save you work.

After you download the RPM or find it on your CD, the install is easy. We will assume that you have X running. Start an xterm session on your X desktop. Installing RPM's require you to be root so if you are not root then at the command prompt run "su". Us the "cd" command to change to the directory containing the RPM file. Your Linux distribution may have a slightly different name for the RPM but it will show up if you do "ls -1 postgresql*.rmp". For this example I will use the file name from Mandrake 8.0. If you Linux distribution has a graphical RPM installer like Mandrake has rpmdrake, I recommend you use it, but you still need the xterm open. If you don't have a graphical tool you will need to install them like so...

rpm -ivh postgresql-7.0.3-12mdk.i586.rpm
rpm -ivh postgresql-server-7.0.3-12mdk.i586.rpm

There are other postgresql*.rpm's like odbc, perl and python, unless you know that you want these you most likely don't.

Installing from source code
If you are not an experienced programmer or have compiled programs form source before this could be hard, but I will try to make it easy for you. My first install of PostgreSQL was a compile from source and it went very easy, but that was some time ago so if you need this and I don't give you enough information, email me and I will try to add more detail to it.

After downloading the postgersql*.tar.gz files extract them all into one directory.
Change to that directory using "cd" then run

./configure
make
make install

Configure to use
You should still have xtem open from the above install set if you did RPM or source. From the command line as root check to make sure that postgres is running. On a Red Hat based distribution we do this by running "/etc/rc.d/init.d/postgresql status", if you do not have a directory called /etc/rc.d/init.d or you but it is empty then you will need to ask some one using the same distribution as you where your initialization scripts are. They should be in /etc for most distributions but I am not 100% positive of that. If so you could find it by going "find /etc/ -name postgresql" and see if you find it. Once you have found it you must make sure that postgres is running. When you run "/etc/rc.d/init.d/postgresql status" you should get some information back saying that is it running like this "postmaster (pid 1156 790) is running... ", if not then you will need to start it like so "/etc/rc.d/init.d/postgresql start". The first time you do that you will get a lot of information on the screen letting you know that postges is creating your default databases and settings.

Your default settings do not allow users from the network to connect to the database server. Barry Gergel wrote this descriptoin on how to set that up.

In order to use the Postgres database, simply installing the rpms or building from
source will not get it working properly. A number of files need to be configured in
to make the database usuable. The two files that need to be configured are located
in the /var/lib/pgsql/data on Mandrake systems. The two files that required
modification are postgres.conf and pg_hba.conf. Note that these files will require
root priviledges in order to edit the files and restart the service. READ the files
before modifying them (and even keeping backups of the originals is good practice).
Make sure to review the other items in this file as you might need to configure
more options. These basic ones will get Postgres working.

In postgres.conf modify the following:

- tcpip_socket = true <-- this is commented out and set to false by default
- port = 5432 <-- default, but is commented out
- max_connections = 32 <-- it is commented out, set it to the max number of 
  connections you feel you DBMS will require


Now edit pg_hba.conf:

- to use the system locally (which is most common), uncomment the lines
  that refer to localhost. These files are located at the bottom of the file.

        local      all                                          trust
        host       all         127.0.0.1     255.255.255.255    trust
        local   all     ident   sameuser


Finally, restart the Postgres server to complete the configuration. At this time,
start up pgacces and test out your configuration.

Adding databases
In xterm as root run "su postgres" this will make you into the postgres user that is the administrator account for postgres. You can now create a new database, for our examples we will use library as our database name. Run this "createdb library" you should then see information showing that the new database has been made.

Adding user accounts
In xterm you should still be the postgres user. We will now add a user account, for our examples we will use the name librarian but you can use your name if you like. Run this "createuser librarian", you will then be asked if this new user can make databases and more users. For our demos it does not matter how you answer, for security reasons you should say no to these. We are done as the postgres user you may now run "exit" in you xterm and that should drop you back to root. Keep xterm open.

Admin utilities
pgaccess runs on your Linux system and let's you do almost anything you want to the local or network database. It has some nice toys like a Form designer and an SQL graphic designer. From your xterm try to run "pgaccess", if you get a command not found error then try looking for it in an RPM on your Linux CD's, if it is not there go to http://freshmeat.net and see if you can find it. All of the SQL examples bellow have been formatted to be used with pgaccess, you may use a different SQL interface program if you like but I have only tested them with pgaccess.

After installing pgaccess run it and click on "Database" and then "Open". Set host to localhost, port should already be set, change the database to library and user name to librarian then click open.

ODBC connection
It is really slow. Nag me to finish this.

Connecting Win32 clients
Nag me to finish this.

Connecting web cgi scripts
I really want to try this. I have a project coming up in a few months that needs this so I hope to write this part soon. Don't nag me about this, I will do it as soon as I learn it. I will not learn it until I need to.

Simple SQL

Note: All of the SQL examples have been setup so that they can be copied and pasted into pgaccess. Some database engines will not translate all SQL commands properly and may not support all PostgreSQL features. Try the example in pgaccess locally on the database server before trying it on client machines.

All SQL keywords will be in uppercase and all user defined words will be in lower case, this is common and recommended but not necessary as the commands are not case sensitive. Your data may be case sensitive!

Creating tables
Before you create a table you need to put some thought into what data you need to put into the table, you also need to have a good plan for how your going to name the table. Table names are important to maintain a structured database. Make sure that your data fields have enough size to hold the largest entry you would ever need to put into it. Name your data fields so that they are unique across the entire database, having two fields named the same will most certainly cause you problems even it they are in different tables. Indexes are useful when linking tables together. I don't fully explain what is happening or why, paste the examples into pgassess and play with it, you will learn more by playing, changing and testing . Lets create some tables!

CREATE TABLE object_media (media_id INTEGER PRIMARY KEY, category CHAR(8), media_type CHAR(10), object_title CHAR(60), author_name CHAR(30), publish_company CHAR(30), isbn_number CHAR(25), purchase_price money);

CREATE TABLE customer_accounts (cust_id INTEGER PRIMARY KEY, cust_name CHAR(30), cust_phone CHAR(12), cust_address CHAR(100), cust_email CHAR(60));

CREATE TABLE signed_out (media_id INT, cust_id INT, out_date DATE);

CREATE TABLE returned_back (media_id INT, cust_id INT, out_date DATE, returned_date DATE)

Posting data to tables

INSERT INTO object_media VALUES (1, 'Web dev', 'Book','Programming JavaScript for Netscape 2.0','Tim Ritchey','New Riders','ISBN 1-56205-585-2','$47.95');

INSERT INTO object_media VALUES (2, 'Web dev', 'Book','Mastering Perl 5','Eric C. Herrmann','SYBEX','ISBN 0-7821-2200-0','$39.99');

INSERT INTO object_media VALUES (3, 'Delphi', 'Book','Delphi Power Tookkit for Windows','Harold Davis','Ventana','ISBN 1-56604-292-5','$49.95');

INSERT INTO object_media VALUES (4, 'Linux', 'CD-ROM','Mandrake 7.2','Linux Mandrake','Linux Mandrake','','$29.95');

INSERT INTO customer_accounts VALUES (1,'Joe Brown','555-1234','123 Hill Top Road', 'jbrown@coldmail.com');

INSERT INTO customer_accounts VALUES (2,'Bill Smith','555-6789','44 Bottem Drive', 'bsmith@bol.com');

INSERT INTO signed_out VALUES (3,1,'April 4, 2001')

Selecting data from tables

Show all from the media table

SELECT * FROM object_media

Show all books from the media table

SELECT * FROM object_media WHERE media_type = 'Book'

Show all books from the media table that have not been signed out

SELECT * FROM object_media WHERE media_type = 'Book' AND media_id != (SELECT media_id FROM signed_out)

Show all books from the media table that have been signed out

SELECT * FROM object_media WHERE media_type = 'Book' AND media_id = (SELECT media_id FROM signed_out)

Deleting data from table

For this example Joe will return the book. We want to keep track of when he brought it back so we remove it from the signed out table and put it into the returned table. First we post to the returned_back table, then we will do the delete from the signed_out table.

INSERT INTO returned_back VALUES (3,1,'April 4, 2001','April 12, 2001');

DELETE FROM signed_out WHERE media_id = '3' AND cust_id = '1'

Copying a table

Use the existing tabe called object_media to make a new table called copy_object_media.

CREATE TABLE copy_object_media AS SELECT * FROM object_media

Renaming a table

Rename the table copy_object_media to delete_object_media.

ALTER TABLE copy_object_media RENAME TO delete_object_media

Deleting tables

Delete the table called delete_object_media.

DROP TABLE delete_object_media

Advanced SQL

Joins
Joins let you take data from multiple tables and display them in a way that they relate to each other. We have a table of books, a table of clients and a table of what is signed out. Let's look at a list of books that are signed out and the name of the persons that have them.

INSERT INTO signed_out VALUES (2,1,'April 14, 2001');
INSERT INTO signed_out VALUES (3,2,'April 15, 2001')

Do this as two steps so you can see the Joined table.

SELECT m.object_title, c.cust_name FROM object_media m, customer_accounts c, signed_out s WHERE m.media_id = s.media_id AND c.cust_id = s.cust_id

Notice how we used m to represent the table object_media also with the other tables. At the start of the SQL command we ask for resolution in the media table by using the ".", it tells the SQL engine that in m use this field. Also see how when we set the tables to get the data from we tell the SQL engine what the m means, we say "FROM object_media m". So from this example we have taken data in three tables and joined them together to show who has what signed out. Our SQL command works good but pgaccess does things a little differently. Make a new query and paste this into the code then click on the visual designer to see a graphical design.

select t0."object_title", t1."cust_name" from "object_media" t0, "customer_accounts" t1, "signed_out" t2 where (t1."cust_id"=t2."cust_id") and (t0."media_id"=t2."media_id")

Screenshot

PostgreSQL 7.1 supports "LEFT OUTER JOINS", the PostgreSQL I have does not. I should upgrade but if you have an old one then you may like this little work around. "LEFT OUTER JOIN" is the one that I seem to use a lot but there are many JOINs that are sort of like it. This kind of join is used to show the data from multiple tables without removing rows from one or more of the tables. I can not test this so I don't know if it works but this kind of join would look like...

SELECT m.object_title, s.out_date FROM object_media m LEFT OUTER JOIN signed_out s ON m.media_id = s.media_id

For now you can see from this example what it should do.

index	title	type
1	Programming JavaScript	Book
2	Mastering Perl 5	Book
3	Delphi Power Toolkit	Book

index	name
1	Joe
2	Bill

book_index	clinet_index	date_out
2	1	4/16/01

Results from a normal join to show books out

title	name	date
Mastering Perl 5	Joe	4/16/01

Results from a "LEFT OUTER JOIN" to show all books including one that are out

title	name	Date
Programming JavaScript
Mastering Perl 5	Joe	4/16/01
Delphi Power Toolkit

Using a temp table we can get something that looks like this.

title	name	Date
Mastering Perl 5	Joe	4/16/01
Programming JavaScript
Delphi Power Toolkit

Temp table are automatically deleted when you disconnect from the SQL server but we can use them just like a normal table until then. Lets try it. Create the temp table first.

CREATE TEMP TABLE list_media (object_title CHAR(60), cust_name CHAR(30), out_date DATE)

After you create the temp table, post data to it.

INSERT INTO list_media SELECT m.object_title, c.cust_name, s.out_date FROM object_media m, customer_accounts c, signed_out s WHERE m.media_id = s.media_id AND c.cust_id = s.cust_id;

INSERT INTO list_media (object_title) SELECT m.object_title FROM object_media m WHERE m.media_id NOT IN (SELECT media_id FROM signed_out)

Now let's view our table.

SELECT * FROM list_media

Variables

I don't think PostgreSQL supports variables. Delphi lets you set SQL variables and I think Oracle does also but this is not an SQL standard. There is of course and easy way around this, a temporary table. First let's create the temp table.

CREATE TEMP TABLE my_vars (search_name char (30))

Now let's store a value into it.

INSERT INTO my_vars VALUES ('Delphi')

Now let's use the value to search for some thing.

SELECT * FROM object_media WHERE category IN (SELECT search_name FROM my_vars)

Download the database

Download the database here.
It was created by using pg_dump like so. First use su to become root.

su postgres -c "pg_dump -Ft library|gzip > library.gz"

To restore the database do this

gzip -d library.gz
su postgres -c "createdb library"
su postgres -c "createuser librarian"
su postgres -c "pg_restore library -d library"