Postgre SQL

PostgreSQL is a descendant of Postgres, which was written under the leadership of Michael Stonebraker at the University of California at Berkeley.

Most likely, PostgreSQL version 6.4.2 was installed on your system when you installed Red Hat Linux 6 (see the next section).

As I mentioned earlier, PostgreSQL is not public domain software. The Regents of the University of California own the copyright to PostgreSQL. They have granted general permission to "use, copy, modify, and distribute" PostgreSQL and its documentation at no cost provided their copyright notice and two specific paragraphs of legalese appear in all copies. For more information on the PostgreSQL license, and for information on PostgreSQL in general, visit

http://www.postgresql.org/

It's important to understand that there are many different programming languages that can initiate client access with a PostgreSQL server. This kind of access is called an interface. You can think of it as a programming "hook." If you need a program to talk to a database server, then you should make sure that the database has an interface for the language in which the program is written. Available PostgreSQL interfaces include:

• C

• C++

• Java

• Perl

• ODBC

• Python

• Tcl

For more information on using the programming languages that ship with Red Hat Linux 6, see Chapter 12.

To check that PostgreSQL has been installed:

The postgres user

The PostgreSQL server must be run by a user named postgres. It is extremely important for security reasons that this user not be given root privileges. (If you grant the postgres user root privileges, the security of your entire server is completely compromised.) The postgres user is sometimes referred to as the postgres superuser. In other words, postgres is to PostgreSQL as root is to your Linux system.

The postgres user may have been already added by the Red Hat installation process—in which case, you still need to have a look to make sure, and to change the user's password.

If there is no postgres user, you should add one using the settings that appear in Figure 9.5.

For information on adding and editing users in Gnome, see Chapter 6.

To configure the postgres user:

http://www.postgresql.org/

To initialize your database server:

The Postmaster daemon

The Postmaster daemon is the back-end process that must be running for clients to be able to connect to the database server.

To start Postmaster:

To test a connection:

SELECT datetime 'now';

To delete a database:

• In a terminal window, at the shell prompt, type destroydb databasename
  
  For example, enter bash$ destroydb hobbits to delete the hobbits database.
  
  

Creating a table

Tables, consisting of columns—also called fields—and rows, are the basic building blocks of relational databases.

To create a table:

CREATE TABLE hobbits(
    fname varchar (20)
    lname varchar (20)
    ssn int);

To populate a table:

INSERT INTO hobbits VALUES
('Frodo', 'Baggins', 12345678);

SELECT * FROM hobbits;

To query a table:

SELECT lname FROM hobbits
WHERE ssn < 100000000;

To update a table:

UPDATE hobbits SET fname =
'Samwise' WHERE lname =
'Gamgee';

SELECT * FROM hobbits;

Table inheritance

It is possible to create a table that inherits all columns (fields) from another table. The new table might have additional columns besides the ones inherited from the original table.

PostgreSQL calls table relationships used in this way classes, which makes sense when one is talking about inheritance.

To create a new table that inherits from an existing table:

CREATE TABLE hobbitsplus
(alias varchar(20)) INHERITS hobbit;

To insert the hobbit rows into hobbitsplus:

INSERT INTO hobbitsplus
SELECT * FROM hobbits;

To populate a new field:

• You can populate the new field by typing a SQL command along these lines:
  
  

  UPDATE hobbitsplus SET alias =
  'Underhill' WHERE lname =
  'Baggins';
  

  
  If you then compare the contents of hobbits and hobbitsplus, you will see that the new table has data in the new field ( Figure 9.20 ).
  
  

  Figure 9.20. You can then insert data into the new table's fields.

  

  
  

Adding users

If users need to access the PostgreSQL server, they must do so as the postgres superuser, or as a new user that you create. Creation in this sense has nothing to do with Linux access privileges; it gives a user rights to access the database server.

To add a new user: