If
the database server resides on another machine the client
applications relies upon the environment variables PGHOST and PGPORT
to establish the connection.PostgreSQL
A free object oriented relational database system for Linux and several UNIX based systems (with a Win32 port in progress).
What is a "RDBMS" (Relation Data Base Management System)? It is a way of storing information in a manner that enforces consistency, facilitates access, ensures reasonable performance, without constraining how the data is correlated or presented.
-Most RDBMS systems (including M$-Sequel Server, Oracle, Informix, DB2, MySQL, Adabase, Sybase) speak a language refered to as SQL (pronounced sequel).
-A RDMBS breaks data into tables, which contain rows, which are broken into fields. Rows can be selected from tables based upon the value of a row's field(s). Most operations are performed on rows.
-A "database" is a collection of tables. A RDBMS may contain multiple databases.
-A database has a "schema" which is a description of all the tables in the database.
What is SQL? A universal RDBMS access language, defined by commitiee. The current standard is reffered to as SQL92, as that is the year it was ratitifed. Many RDMBSs (including PostgreSQL) implement proprietary extensions to SQL. SQL has the following basic command and structure.
Create - Used to create objects such as tables, indexes, and views.
Drop - Used to destroy objects such as tables, indexes, and views.
Select - Used to pull fields and rows from the database tables.
Insert - Used to populate a table.
Delete - Used to remove records from a table.
For example: select first_name, last_name
from phone_book
where phone_number matches "616*"
order by last_name, first_name
Will "select" the fields named first_name and last_name from the rows of the table called phone_book where the phone number begins with "616"
delete from phone_book
where phone_number = "6165551212"
Will delete all rows from the phone book table where the phone number field contains "6165551212"
PostgreSQL Features
Functions - Allows user defined functions to be directly executed by the back end via the use of shared libraries. Supported languages are C and SQL. Additional languages can be integrated using the create language command.
CREATE FUNCTION name ( [ ftype [, ...] ] )
RETURNS rtype
AS path
LANGUAGE 'langname'
CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS bool
AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'c';
CREATE TABLE product
(
id char(8) PRIMARY KEY,
eanprefix char(8) CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}')
REFERENCES brandname(ean_prefix),
eancode char(6) CHECK (eancode ~ '[0-9]{6}'),
CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode))
);
Constraints - A limit, type conversion, or validity check on table records. This functionality is defined in the SQL 92 standard.
CREATE [ TEMP ] TABLE table (
column type
[ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ]
[column_constraint_clause | PRIMARY KEY } [ ... ] ]
[, ... ]
[, PRIMARY KEY ( column [, ...] ) ]
[, CHECK ( condition ) ]
[, table_constraint_clause ]
Constraint Types:
NOT NULL - A field must contain a value.
UNIQUE - A field's value cannot be the same as in any other row in the table.
CHECK - A logical condition must be satisfied for the operation to succeed.
Example: CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40)
CONSTRAINT con1 CHECK (did > 100 AND name > ''));
PRIMARY KEY - Define a group of fields that must consitute a unique value.
Example: CREATE TABLE films (
code CHAR(05),
title VARCHAR(40),
did DECIMAL(03),
date_prod DATE,
kind CHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT code_title PRIMARY KEY(code,title));
In version 6.4 the usefulness of constraints is limited by the lack of support for foreign keys.
Triggers - Ties the execution of a user supplied procedure (function) to the execute of an operation on a relation (insert, update, or delete). The create trigger command is specific to PostgreSQL and may not apply or may act differently in other RDBMSs.
Rules - Define actions to occur when a particular SQL statement is executed by and application or user (current_user return the name of the user name associated with the application making the request.) Access to system functions or catalogs (other than the "oid" field) are not possible from within a rule. If a rule exceeds a system page (usually 8kb) it's creation may fail
In a rule the "current.*" expression corresponds to the record being affected by a select, update or delete.
create rule example_1 as
on update EMP.salary where current.name = "Joe"
do update EMP (salary = new.salary)
where EMP.name = "Sam"
Typically a rule action will be performed in addition to the user/application requested action. With the use of the "instead" keyword the rule will be performed in place of the user action.
create rule example_2 as
on select to EMP.salary
where current.name = "Bill"
do instead
select (EMP.salary) from EMP
where EMP.name = "Joe"
The "nothing" action in a "instead" rule causes no action to be perfumed when the application or user submits a particular action.
create rule example_3 as
on select to EMP.salary
where current.dept = "shoe" and current_user = "Joe"
do instead nothing
The "new.*" expression refers to fields in a records being inserted into a table:
create rule example_5 as
on insert to EMP where new.salary > 5000
do update new.salary = 5000
Rules are a PostgreSQL extension to the SQL standard and may not be supported or function in the same manner on other DBMS systems.
Transaction Integrity - confine multiple SQL statements into an atomic set using "begin work", "commit work", and "rollback".
Sequences - an auto incrementing or decrementing series of values.
CREATE SEQUENCE seqname
[ INCREMENT increment ]
[ MINVALUE minvalue ]
[ MAXVALUE maxvalue ]
[ START start ]
[ CACHE cache ]
[ CYCLE ]
CREATE SEQUENCE serial START 101;
SELECT NEXTVAL ('serial');
SELECT last_value FROM sequence_name;
SELECT * FROM sequence_name;
DROP SEQUENCE sequence_name;
CREATE TABLE distributors (
did DECIMAL(3) DEFAULT NEXTVAL('serial'),
name VARCHAR(40) DEFAULT 'luso films'
);
A sequence that is referenced by multiple simultaneous backbends with a cache value greater than one may in fact insert records "out of order" as each backend will have it's own set of cached values. But a value from a sequence will never be a duplicate.
SQL92 Compliant Interpreter - including primary keys, quoted identifiers, literal string type conversion, type casting, and binary and hexadecimal integer input.
Year 2000 Compliant.
User Defined Types - A new data type may be registered in the database system for data that cannot easily be represented by the default data types.
CREATE TYPE typename (
INPUT = input_function
, OUTPUT = output_function
, INTERNALLENGTH = (internallength | VARIABLE)
[ , EXTERNALLENGTH = (externallength | VARIABLE) ]
[ , ELEMENT = element ]
[ , DELIMITER = delimiter ]
[ , DEFAULT = "default" ]
[ , SEND = send_function ]
[ , RECEIVE = receive_function ]
[ , PASSEDBYVALUE ]
)
CREATE TYPE box (INTERNALLENGTH = 8,
INPUT = my_procedure_1, OUTPUT = my_procedure_2);
CREATE TABLE myboxes (id INT4, description box);
Every additional data type registed requires the registration of at least two functions to handle the new type. Create type is specific to PostgreSQL and may not be available on other database systems.
Privalages - Using the SQL 92 GRANT/REVOKE commands users can be limited to the operations they can perform on a table.
GRANT INSERT ON films TO PUBLIC;
Allow anyone who can connect to the table to insert records into the table films.
GRANT ALL ON kinds TO manuel;
Allow all type of operations on table kinds by user manuel.
REVOKE performs the opposite action as GRANT, by taking away privalages.
Architecture:
A supervisory daemon allocates shared memory and verifies database intergity. This process runs as a PostgreSQL superuser, typically "postgres".
Backend processes are spawned to handle each client application connection.
Client applications connect via sockets from either the local or a remote machine.
The library "libpq" contains functions to communicate with a PostgreSQL backend.
If
the database server resides on another machine the client
applications relies upon the environment variables PGHOST and PGPORT
to establish the connection.
Installation & Configuration:
By default PostgreSQL installs in /usr/local/pgsql.
A user called "postgres" must be created.
The libraries and binaries consume roughly 5Mb of disk space.
8Mb of free RAM is the minimum recommendation, with significant performance increases when more than 80Mb is available, especially on SMP systems.
Requires flex version 2.5.4 or greater (standard on recent RedHat distributions).
Once the PostgreSQL system is installed and the "postgres" superuser account has been created you must run the "initdb" command to create the required database catalogs.
Databases are created and destroyed using the "createdb" and "destroydb" commands.
Database users are created and destroyed using the "createuser" and "destroyuser" commands, specific users may be granted the ability to create and destroy databases.
The database supervisory process can be started in the system run control scripts using something similar to the following (as you do NOT want the process to run as root):
su postgres -c "/usr/local/pgsql/bin/postmaster -B 64 -S -D /usr/local/pgsql/data"
where - B is the number of 512 byte blocks of shared memory.
S means run silent, no messages to standard out.
D the data directory where the database catalogs are stored.
Utility "pgsql" gives you line by line SQL access to a database
pgsql {database name}
Maintenance:
VACUUM {table}; - This command updates the system statistics used to optimize queries as well as reclaiming space freed by delete statements. The vacuum command may be executed at any time, but preferably at a time of little or no system activity. The vacuum command creates a lock file (pg_lock) in the databases data directory to prevent more than one vacuum at a time from executing. This file will have to be manually removed if the system crashes during a vacuum. On an active database it is recommended that the vacuum be run daily. There is also a vacuumdb UNIX command that can be used in cron:
vacuumdb [ -h host ] [ -p port ]
[ --table 'table [ ( column [,...] ) ]' ]
[ dbname ]
Backup - There is currently no online backup procedure for PostgreSQL (that I know of). The preferred method of backup up data would be to backup the files in the systems data directory. A utility pg_dump database is provided with PostgreSQL that can write a database schema and/or data to a delimited file that can then be loaded back in using psql < output.file This is required when you move between version of the PostgresSQL system
Other
Supported client languages:
C, C++, Embedded SQL for C, TCL, JDBC, ODBC, Python
Supported data types
PostgreSQL Type SQL92 Type Description
bool boolean logical boolean (true/false)
box rectangular box in 2D plane
char(n) character(n) fixed-length character string
cidr IP version 4 network or host address
circle circle in 2D plane
date date calendar date without time of day
float4/8 float(p) floating-point number with precision p
float8 double precision double-precision floating-point number
inet IP version 4 network or host address
int2 smallint signed two-byte integer
int4 int, integer signed 4-byte integer
decimal(p,s) exact numeric for p <= 9, s = 0
numeric(p,s) exact numeric for p == 9, s = 0
int8 signed 8-byte integer
line infinite line in 2D plane
lseg line segment in 2D plane
money decimal(9,2) US-style currency
path open and closed geometric path in 2D plane
point geometric point in 2D plane
polygon closed geometric path in 2D plane
serial unique id for indexing and cross-reference
time time time of day
timespan interval general-use time span
timestamp timestamp with time zone date/time
varchar(n) character varying(n) variable-length character string
Function Constants
Postgres Function SQL92 Constant Description
getpgusername() current_user user name in current session
date('now') current_date date of current transaction
time('now') current_time time of current transaction
timestamp('now') current_timestamp date and time of current transaction