|Chazin Home | Ca-binding Protein DB | Vanderbilt Home|
|Research Description | Publications | Wisdom | Search|
|How to contribute | About this page|
Written by: Melanie Nelson, fall 1998
See also: Information on how to use DBI and DBD:Pg, Perl modules for accessing postgres databases.
This document was written for postgresql 6.3.2, running under IRIX 6.2. However, most of the procedures described in this document are very basic, and are unlikely to be operating system dependent.
This document does not attempt to be a complete reference about PostgreSQL, relational databases, or SQL! For more information about PostgreSQL, see the postgresql website (http://www.postgresql.org/index.html). For more information about relational databases in general, see "An Introduction to Database Systems," by C.J. Date. This book will be referred to in the text below. Chapter and page numbers will refer to the 6th edition. For more information about SQL, see "Understanding the New SQL: A Complete Guide," by Jim Melton and Alan R. Simon. This book will also be referred to in the text below. Chapter and page numbers refer to the first edition, published in 1993.
Please note: For clarity, SQL commands are in capital letters. However, PostgreSQL does not require this to interpret the commands. Also, no attempt has been made to exclusively use the correct relational database terms such as relation, attribute, etc. Briefly: relation = table, attribute = column, tuple = row. Also, since PostgreSQL is an object-relational database management system, you will often find things referred to by names used in object-oriented systems. For instance, in the PostgreSQL documentation, class = table and instance = row.
Procedures are in rough alphabetical order, by keyword. You can skip to a specific procedure using the links below:
|Altering a table
||Backing up a database
||Defining a foreign key
(referential integrity) for a table
|Granting privileges to a user
||Inserting a string containing a single quote
into an attribute
||Inserting a NULL value into a
|Defining a primary key for a table
||Vacuuming a database
PostgreSQL follows the SQL92 standard for altering an existing table. For instance, the following statement is used to add a new column called "comment" to the table "subfamily":
ALTER TABLE subfamily
ADD COLUMN comment text;
See Melton and Simon, section 4.3.5 for more information.
ALTER TABLE/DROP COLUMN is not yet implemented in PostgreSQL. To drop a column from a table, you either have to delete and recreate the table or create a dumpfile (see to backup a database) and edit the dumpfile before recreating the database. I recommend the second method if you have triggers defined on the table, because if you drop the table you will have to redefine the triggers as well. When editing the dumpfile, be sure to edit both the CREATE TABLE statement and the COPY statement that imports the data into the table.
The PostgreSQL FAQ recommends the follwing method for dropping a table:
SELECT ... -- select all columns but the one you want to remove
INTO TABLE new_table
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;
I believe that you will still have to redfine any triggers that were defined on this table.
The PostgreSQL database will NOT be correctly backed up by any automatic system-wide backup system. To ensure proper backups, use the pg_dump or pg_dumpall commands to create a text file containing the commands to recreate the database. This file can then be backed up like any other text file.
Some aspects of the database are not saved in the dump file. However, all tables, indices and triggers will be saved. For more information about how to use these commands and about the limitations, see the man pages for the commands.
Briefly, the command to dump all databases is:
pg_dumpall -o > dumpfile
The -o option causes the unique object identifiers to be dumped as well as the user-defined database objects.
If you have issued GRANT and REVOKE privileges commands, you must
include the -z flag in the dump command to have these stored:
pg_dumpall -oz > dumpfile
The command to reload the database is:
psql -e database < dumpfile
PostgreSQL only supports referential integrity (foreign keys) by way of triggers that call two additional functions, which you may need to compile separately (after having compiled the main PostgreSQL program). These triggers are in postgresql-6.3.2/contrib/spi. The refint files are what are needed for referential integrity. However, all files in the directory are compiled by the included Makefile. To compile the files, you'll need gmake, and you'll need to rebuild the file fmgr.h. Instructions for this can be found in one of the original make files.
Once everything is compiled and installed, you need to create the two functions check_foreign_key and check_primary_key, using the SQL in refint.sql
Once this is done, you can use CREATE TRIGGER to define the constraints on your table. Examples for how to do this are in refint.example. If you want different actions ( the possible actions are: 'cascade', 'restrict', and 'setnull') for UPDATEs and DELETEs, you will need to define two triggers. See Date, section 5.5 for a discussion on what these actions mean.
Here is an example of two triggers, defined to restrict DELETions and cascade UPDATEs:
CREATE TRIGGER sample_del_fk BEFORE DELETE ON parent FOR EACH ROW
check_foreign_key(1, 'restrict', 'parent_pk', 'child', 'child_fk');
CREATE TRIGGER sample_up_fk BEFORE UPDATE ON protein FOR EACH ROW EXECUTE PROCEDURE
check_foreign_key(1, 'cascade', 'parent_pk', 'child', 'child_fk');
The triggers defined above will ensure that no inappropriate changes are made to the parent table. However, referential integrity also requires that any row inserted into the child table must have a matching row in the parent table (i.e., each value of the foreign key in the child table must refer to a value of the primary (or candidate) key in the parent table). See Date, chapter 5 for more information about these concepts. The following trigger will ensure that no row inserted into the child table violates this aspect of referential integrity:
CREATE TRIGGER sample_pk BEFORE INSERT OR UPDATE ON
child FOR EACH ROW EXECUTE PROCEDURE
check_primary_key ('child_fk', 'parent', 'parent_pk');
The check_primary_key trigger seems to allow NULL values for the child's foreign key, hence allowing optional, non-identifying relationships.
To get rid of a trigger, you can use the drop trigger
DROP TRIGGER trigger_name ON table_name;
Information about triggers is stored in the pg_trigger table. So,
to get rid of a trigger, you could delete the relevant row from this table:
DELETE FROM pg_trigger WHERE tgname = "your_trigger_name";
INSERT INTO table VALUES ('xxx', 'yyy', NULL, 'zzz')
If the NULL is to go into the last field, it can be omitted, and will be inserted by default (unless you specified otherwise at the time you created the table)
note:The single quotes around non-null values are only required for character type fields
If you are using the COPY command, NULL fields should be indicated by \N in the file from which the data is being copied.
Postgres supports the SQL GRANT and REVOKE statements. These
allow control at the table level of what the user can and cannot see
and/or modify. For instance, to grant user johndoe the
ability to read from a table table1 (but not to modify
You will now need to use the -z flag in your dump statements to include these privileges in the dumpfile. See the section on backing up a database for more information.
SQL requires string literals to be encased in single quotes ('). However, sometimes the value of an attribute should have a single quote in it. To insert such a value in the interactive psql session, type two consecutive single quotes in place of the one desired single quote. For instance, to insert the value O'Neil into the lastname attribute of auth_name, a table with two columns refnum and i lastname, type:INSERT INTO auth_name
However, if you are inserting the values using a COPY statement, there is no need to replace the single quote with two single quotes.
It is best to name all your constraints (see Melton and Simon
section 10.2.4 for a discussion of why). To created a named primary key
constraint, do the following:
CREATE TABLE table (
CONSTRAINT table_pk PRIMARY KEY (column1) )
table_pk is the constraint name. col1 is the primary key. The column1, column2, etc lines are where the columns are defined (type is the data type for that column), as specified in the pgsql documentation. More than one column can be included in the primary key. Multiple columns are separated by commas.
If you don't want to name the constraint, and the primary key is composed of
only one column, the key can also be defined as a column constraint:
CREATE TABLE table (
column1 type PRIMARY KEY,
Yet another way to define a primary key is as follows:
CREATE TABLE table (
PRIMARY KEY (column1) )
It is a good idea to vacuum your databases periodically,
particularly after doing a lot of INSERT, DELETE,
or UPDATE commands. Vacuuming the database will update the
indeces, etc., and make your queries run faster. To vacuum a
database while connected to it (using the psql interface), simply
Consult the postgres documentation for more information about this command.
Sometimes, the vacuum cleaner will crash before it finishes. This
is rare, but usually causes a fatal error. To be safe, you should restart the
postmaster after this occurs. You will also need to remove the
vacuum cleaner's lock file, or else you will not be able to run
vacuum again. This lock file is stored in:
For instance, I have my data stored in /home/postgres/pgsql/data, and my database is named cabp_db, so my vacuum cleaner lock file is in: