Like most relational database systems, the key component of the PostgreSQL is the database server. The database server runs as a daemon process named postmaster. The job of the postmaster process is to handle requests, in the form of SQL statements, from client instances. All client interaction with the database occurs through the SQL server process.
There are a variety of clients that can be used, but we will focus more on the PHP interface and the command line interface.
The PHP PostgreSQL client interface must be enabled in PHP before it can be used from within a PHP script. Alternatively, PostgreSQL can also be used in PHP through the ODBC interface. This can be done either by enabling the PostgreSQL extension at build time, using the --with-pgsql configuration parameter, or by adding the extension dynamically at run time. Consult the PHP documentation for details.
The PostgreSQL command line client works very much like the MySQL command line client. To start the command line client, simply run psql from the command prompt. psql will default to a database with the same name as your username if no database is explicitly specified. To specify a database, simply pass the database name as a parameter to psql:
$ psql library
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
library=#
We are now working with the library database.
Before we can use a database, we must create one. We'll shortly see how to create a database. For detailed information on creating databases and managing database users, refer to Beginning Databases with PostgreSQL from Wrox Press or the PostgreSQL documentation at http://www.postgresql.org/idocs/.
In addition to standard SQL statements, a wide range of additional commands can be issued from within the psql client. These commands are prefixed with a backslash (\) and are called "slash" commands. For a complete list of the available "slash" commands, issue the \? command at the psql command prompt.
Data definition statements are used to create or modify the structure of the database and its tables.
CREATE DATABASE database_name
This statement is used to create a new database. To create a new database named "library" at the psql command prompt, write:
psql=# CREATE DATABASE library;
CREATE DATABASE
The semicolon indicates that the statement has ended. When issuing SQL statements from within PHP code, the semicolon is not necessary.
To change to the database that you have just created, use the \connect command:
psql=# \connect library
You are now connected to database library.
library=#
This command is equivalent to the MySQL USE statement.
CREATE [TEMPORARY | TEMP] TABLE table_name (
{column_name type [column_constraint [...]] |
table_constraint } [, ...])
[INHERITS (inherited_table [, ...])]
Where column_constraint can be:
[CONSTRAINT constraint_name]
{NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT value | CHECK (condition) |
REFERENCES table [(column)] [MATCH FULL | MATCH PARTIAL]
[ON DELETE action] [ON UPDATE action]
[DEFERRABLE | NOT DEFERRABLE] [INITIALLY DEFERRED | INITIALLY IMMEDIATE] }
And where table_constraint can be:
[CONSTRAINT constraint_name]
{UNIQUE (column_name [, ...]) |
PRIMARY KEY (column_name [, ...]) |
CHECK (condition) |
FOREIGN KEY (column_name [, ...]) REFERENCES table [(column [, ...])]
[MATCH FULL | MATCH PARTIAL] [ON DELETE action] [ON UPDATE action]
[DEFERRABLE | NOT DEFERRABLE] [INITIALLY DEFERRED | INITIALLY IMMEDIATE] }
CREATE TABLE is used to define a new table in the selected database. The syntax given above is specific to PostgreSQL. Here's an example of a typical invocation:
library=# CREATE TABLE details (
library(# ISB N VARCHAR (13) PRIMARY KEY not NULL,
library(# price FLOAT,
library(# num_of_books INT not NULL,
library(# num_booked INT NOT NULL,
library(# series_ID INT NOT NULL
library(#);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'details_pkey' for
table 'details'
CREATE
Note that PostgreSQL automatically created an implicit index for our primary key.
If you have already read through the MySQL chapter, you'll note that the creation syntax is basically the same. However, you can see that some of the field definitions vary from their MySQL counterparts. That's because PostgreSQL offers a slightly different set of field types and field modifiers. The SQL- standard types, such as VARCHAR and INT, should work with most databases.
Let's continue by creating the title table:
library=# CREATE TABLE title (
library(# ISB N VARCHAR(13) NOT NULL PRIMARY KEY,
library(# book_title VARCHAR (255) NOT NULL
library(#);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'title_pkey' for
table 'title'
CREATE
The \d slash command in the psql interpreter shows the structure of a table. This is a useful way to check our work. In the output, the Type column indicates the field type. The Modifier column lists any additional field modifiers that have been specified, such as NOT NULL. Below the field listing, indices that are associated with this table are listed. Note the details_pkey index (for our primary key) in the output below:
library=# \d details
Table "details"
Attribute | Type | Modifier
--------------+-----------------------+-----------
isb n | character varying(13) | not null
price | double precision |
num_of_books | integer | not null
num_booked | integer | not null
series_id | integer | not null
Index: details_pkey
Let's create the rest of our tables:
library=# CREATE TABLE author (
library(# auth_id SERIAL PRIMARY KEY,
library(# auth_name VARCHAR (128) NOT NULL
library(#);
NOTICE: CREATE TABLE will create implicit sequence 'author_auth_id_seq' for
SERIAL column 'author.auth_id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'author_pkey' for
table 'author'
CREATE
Note that we've introduced a new field type here: SERIAL. The SERIAL field type is similar to the MySQL's AUTO_INCREMENT field. It creates an implicit sequence (author_auth_id_seq) that can be used to generate unique surrogate keys for our table:
library=# CREATE TABLE authortitle (
library(# ISB N VARCHAR (13) NOT NULL,
library(# auth_ID INT NOT NULL,
library(# PRIMARY KEY (ISB N, auth_ID)
library(#);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'authortitle_pkey'
for table 'authortitle'
CREATE
In this table, you can see an example of a primary key composed of two fields: ISB N and auth_ID. PostgreSQL will still create an implicit index for the composite primary key:
library=# CREATE TABLE series ( library(# series_ID SERIAL PRIMARY KEY, library(# book_series VARCHAR (64) NOT NULL library(#); NOTICE: CREATE TABLE will create implicit sequence 'series_series_id_seq' for SERIAL column 'series.series_id' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'series_pkey' for table 'series' CREATE library=# CREATE TABLE users ( library(# username CHAR (32) PRIMARY KEY, library(# password CHAR (32) NOT NULL library(#); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'users_pkey' for table 'users' CREATE
ALTER TABLE [ONLY] table [*]
ADD [COLUMN] column type
ALTER TABLE [ONLY] table [*]
ALTER [COLUMN] column { SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table [*]
RENAME [COLUMN] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD table constraint definition
ALTER TABLE table
OWNER TO new owner
ALTER TABLE allows you to change the structure of a table. For example, to add a column to the details table, the statement would be:
library=# ALTER TABLE details ADD COLUMN pages INT;
ALTER
library=# \d details
Table "details"
Attribute | Type | Modifier
---------------+-----------------------+--------
isb n | character varying(13) | not null
price | double precision |
num_of_books | integer | not null
num_booked | integer | not null
series_id | integer | not null
pages | integer |
Index: details_pkey
PostgreSQL adds new columns to the end of the table definition. At this time, new columns cannot be inserted between existing columns.
Columns can easily be renamed using an ALTER TABLE statement:
library=# ALTER TABLE details RENAME COLUMN pages TO num_pages;
ALTER
library=# \d details
Table "details"
Attribute | Type | Modifier
---------------+-----------------------+----------
isb n | character varying(13) | not null
price | double precision |
num_of_books | integer | not null
num_booked | integer | not null
series_id | integer | not null
num_pages | integer |
Index: details_pkey
PostgreSQL does not yet allow you to drop columns from an existing table. The documented work- around is to store the continuing data in a temporary table, drop the old table, recreate it with the modified table definition, and restore the old data.
To drop the num_booked and num_pages columns from the above details table, you would do something like this:
CREATE TABLE temp AS SELECT ISB N, price, num_of_books, series_ID FROM details;
DROP TABLE details;
CREATE TABLE details (
ISB N VARCHAR(13) PRIMARY KEY,
price FLOAT,
num_of_books INT NOT NULL,
series_ID INT NOT NULL
);
INSERT INTO details SELECT * FROM temp;
DROP TABLE temp;
These statements are used to get at your data in the database, as well as manipulate it.
INSERT INTO table [(column [, ...])]
{DEFAULT VALUES | VALUES (expression [, ...]) | SELECT query}
INSERT is used to add new records to a table. Note that the character strings in the example below are quoted using single quotes. The SQL standard dictates that character values must always be quoted in this manner. Numeric types and the special keyword NULL are not quoted:
library=# INSERT INTO title library-# VALUES ('1861005156', 'Beginning Databases with PostgreSQL') library-# ; INSERT 101798 1
If you attempt to insert a new entry that contains the same primary key value as an existing entry, the insertion attempt will fail.
DELETE FROM [ONLY] table [WHERE condition]
DELETE is used to remove records from a table. The WHERE clause is an important component of DELETE, UPDATE, and SELECT queries, as it allows you to specify conditions that determine which records your query will affect. The query below will only delete records that have a value of "1231003730" in the ISB N field:
Library=# DELETE FROM title WHERE ISB N = '1231003730';
DELETE 1
| Important |
If you omit the WHERE clause from a DELETE statement, every record in the table will be deleted. |
UPDATE [ONLY] table SET col = expression [, ...]
[FROM fromlist]
[WHERE condition]
UPDATE is used to change the data in existing records in the database. It is important to specify the WHERE condition carefully. The query below only affected one row, but it had the potential to change many rows if more than one record had the same ISB N. This is impossible in this particular example because ISB N is a primary key:
library=# UPDATE title SET book_title='New Title' WHERE ISB N='1231003730';
UPDATE 1
Even though PostgreSQL honors the notion of referential integrity, there is no automatic protection for it built into the UPDATE or DELETE actions. For example, if author_ID was renamed or removed from the author table, there may still be records in the author_title table that refer to the now nonexisting record. Thus, referential integrity has been compromised by the UPDATE or DELETE operation.
| Important |
If you omit the WHERE clause from an UPDATE statement, every record in the table will be updated. |
SELECT [ALL | DISTINCT [ON (expression [, ...])]] * | expression [AS output_name] [, ...] [FROM from_item [, ...]] [WHERE condition] [GROUP BY expression [, ...]] [HAVING condition [, ...]] [{ UNION | INTERSECT | EXCEPT [ALL] } select] [ORDER BY expression [ASC | DESC | USING operator] [, ...]] [FOR UPDATE [OF tablename [, ...]]] [LIMIT { count | ALL } [{ OFFSET | ,} start]]
Where from_item can be:
[ONLY] table_name [*]
[[AS] alias [(column_alias_list)]]
|
(select)
[AS] alias [(column_alias_list)]
|
from_item [NATURAL] join_type from_item
[ON join_condition | USING (join_column_list)]
SELECT is used to retrieve data from one or more tables. To specify the fields that we want returned:
library=# SELECT ISB N, price FROM details;
isb n | price
------------+-------
1231003730 | 39.95
1231005156 | 39.95
1231005083 | 29.95
1231002092 | 49.95
1231005334 | 24.95
(5 rows)
To indicate "all fields", use an asterisk (*):
library=# SELECT * FROM details;
isb n | price | num_of_books | num_booked | series_id
------------+-------+--------------+------------+-----------
1231003730 | 39.95 | 10 | 10 | 1
1231005156 | 39.95 | 10 | 10 | 1
1231005083 | 29.95 | 10 | 10 | 1
1231002092 | 49.95 | 10 | 10 | 1
1231005334 | 24.95 | 10 | 10 | 1
(5 rows)
The WHERE clause is used the same way in SELECT statements as it is in DELETE and UPDATE statements. To select only the records that have a price value greater than or equal to 39.95, we specify a condition:
library=# SELECT * FROM details WHERE price >= 39.95; isb n | price | num_of_books | num_booked | series_id ------------+-------+--------------+------------+----------- 1231003730 | 39.95 | 10 | 10 | 1 1231005156 | 39.95 | 10 | 10 | 1 1231002092 | 49.95 | 10 | 10 | 1 (3 rows)
ORDER BY makes it possible to control the sort order of a resultset:
library=# SELECT * FROM details WHERE price >= 39.95 ORDER BY ISB N;
isb n | price | num_of_books | num_booked | series_id
------------+-------+--------------+------------+-----------
1231002092 | 49.95 | 10 | 10 | 1
1231003730 | 39.95 | 10 | 10 | 1
1231005156 | 39.95 | 10 | 10 | 1
(3 rows)