Chazin Home | Ca-binding Protein DB | Vanderbilt Home
	Research Description | Publications | Wisdom | Search
	How to contribute | About this page

Basic Procedures in PostgreSQL

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:

To alter a table once it has been created:

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
FROM old_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.

To backup a database:

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

To define a foreign key for a table:

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 EXECUTE PROCEDURE
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 parent table is parent, the child table is child
• parent_pk is the primary key in the parent table, and child_fk is the foreign key in the child table. Notice that although they contain exactly the same information, they don't have to have the same name.
• sample_del_fk and sample_up_fk are the trigger names. Pick something informative for these names, because this is how you can refer to triggers (in the pg_trigger table: see below)

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 command:
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";

To insert a NULL value into a table:

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.

To grant privileges to a user:

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 it):
GRANT SELECT
ON table1
TO johndoe

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.

To insert a string containing a single quote into an attribute:

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:

However, if you are inserting the values using a COPY statement, there is no need to replace the single quote with two single quotes.

To define a primary key for a table:

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 (
column1 type,
column2 type,
etc.,
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,
column2 type,
etc. )

Yet another way to define a primary key is as follows:
CREATE TABLE table (
column1 type,
column2 type,
etc.,
PRIMARY KEY (column1) )

Vacuuming a database:

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 type:
vacuum;
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:
you_data_dir/base/your_dbname
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:
/home/postgres/pgsql/data/base/cabp_db