Getting Started with PostgreSQL
eryar@163.com
Abstract. PostgreSQL is an excellent implementation of relational database, fully featured, open source, and free to use. Nearly nontrivial computer applications manipulate large amounts of data, and a lot of applications are written primarily to deal with data rather than perform calculations. Some writers estimate that 80% of all application development in the world today is connected in some way to complex data stored in a database, so databases are very important foundation to many applications. This article mainly about the usage of SQL shell of PostgreSQL(psql).
Key Words. Database, PostgreSQL, psql
1. Introduction
PostgreSQL是一款開源的關系－對象數據庫，其授權方式為BSD形式的開源協議，比OpenCASCADE的LGPL協議更為自由。將這些高質量的開源產品組合一下，應該可以創造出實用的軟件，提高工作效率。
如下圖所示為一款產于英國劍橋的工廠輔助設計管理系統PDMS的主要界面：
Figure 1.1 AVEVA Plant(PDMS) GUI
像AVEVA Plant(PDMS)這樣的產品，最終的結果都是以數據庫的形式將模型及其他信息保存。因此，需要有數據庫管理系統來對這些數據進行管理。不管是以樹形的方式，還是以三維模型的方式，都是其統一數據模型的一種表現形式。基于Observer設計模式定義：
定義對象間的一對多的依賴關系，當一個對象的狀態發生變化時，所有依賴于它的對象都得到通知，并自動更新。
由Observer定義可知，樹形顯示及三維顯示都是數據模型的Observer。不管是在樹上修改還是在三維模型中以直觀的交互方式修改模型，根本上還是修改了數據模型。并且在一個Observer中修改了數據模型，另一個Observer中會得到通知并自動更新和數據模型保持一致。其核心數據模型歸根到底是由一個高性能的數據庫來管理，由此可見數據庫管理系統的重要性。
Figure 1.2 PDMS Architecture
我認為的PDMS軟件架構如圖1.2所示，樹形視圖、三維視圖及管道ISO圖和安裝圖等都是數據模型的觀察者。因為對數據模型的存儲非常重要，所以數據管理系統：數據庫的需求就顯而易見。但是對于應用開發而言，提供一個MVC框架來統一數據的增、刪、改的接口更為重要。因為其好處更多：
v 接口統一，便于程序開發及給用戶一致的操作，便于用戶輕松掌握軟件；
v 只有基于統一的接口，才能基于此提供Undo/Redo功能；
v 便于與Tcl, Python等腳本語言綁定，為程序提供二次開發功能；
v 基于腳本綁定，為程序提供自動化測試，有力保證軟件質量；
綜上所述可知，OpenCASCADE提供的OCAF框架在思想上對于軟件開發的重要性。不過本文主要記錄如何在Windows平臺上使用另一款高質量的開源數據庫管理系統PostgreSQL。理解PostgreSQL，就解決了軟件底層數據的管理，為軟件開發的數據模型提供根本保障。
2.Creating User Records
在Windows系統中，打開命令窗口并將PostgreSQL程序所在目錄置為當前路徑，然后運行createuser.exe程序，如下圖所示：
Figure 2.1 Create User by createuser.exe
Figure 2.2 View user in pgAdmin
－U選項用來指定創建新用戶時使用的賬號，必須為PostgreSQL具有創建用戶權限的用戶；
－P選項為使用程序createuser創建的新用戶的用戶名；
當然，也可以在pgAdmin中直接創建用戶，圖形化的界面更為直觀。
3.Creating the Database
在Windows系統中創建數據庫使用是程序createdb.exe，用法如下：
Figure 3.1 Create the Database by createdb.exe
Figure 3.2 Databases in pgAdmin
新版本9.4的createdb.exe創建出來的用戶沒有詢問是否有創建新數據庫的權限。修改后即可。成功創建數據庫后，就可以輸入以下命令來連接了：
Figure 3.3 Connect to the database in psql
Figure 3.4 Server status
如圖3.4所示，連接成功后，會從服務器狀態中看到相關的連接信息。
4.Creating the Tables
連接到數據庫后，psql提供了一些基本命令，如下表4.1所示：
Table 4.1 Basic psql Commands
由上表可知，可以使用psql的\i命令來執行相關的表格創建、插入數據等操作。
-- customer table
CREATE TABLE customer
(
customer_id serial ,
title char(4) ,
fname varchar(32) ,
lname varchar(32) NOT NULL,
addressline varchar(64) ,
town varchar(32) ,
zipcode char(10) NOT NULL,
phone varchar(16) ,
CONSTRAINT customer_pk PRIMARY KEY(customer_id)
);
-- item table
CREATE TABLE item
(
item_id serial ,
description varchar(64) NOT NULL,
cost_price numeric(7,2) ,
sell_price numeric(7,2) ,
CONSTRAINT item_pk PRIMARY KEY(item_id)
);
-- orderinfo table
CREATE TABLE orderinfo
(
orderinfo_id serial ,
customer_id integer NOT NULL,
date_placed date NOT NULL,
date_shipped date ,
shipping numeric(7,2) ,
CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id)
);
-- stock table
CREATE TABLE stock
(
item_id integer NOT NULL,
quantity integer NOT NULL,
CONSTRAINT stock_pk PRIMARY KEY(item_id)
);
-- orderline table
CREATE TABLE orderline
(
orderinfo_id integer NOT NULL,
item_id integer NOT NULL,
quantity integer NOT NULL,
CONSTRAINT orderline_pk PRIMARY KEY(orderinfo_id, item_id)
);
-- barcode table
CREATE TABLE barcode
(
barcode_ean char(13) NOT NULL,
item_id integer NOT NULL,
CONSTRAINT barcode_pk PRIMARY KEY(barcode_ean)
);
將上述sql保存為create_tables-bpsimple.sql，并在psql中執行，如下圖所示：
Figure 4.1 Create Tables by SQL File
Figure 4.2 Tables in pgAdmin
5.Populating the Tables
與創建表的方式一樣，將下述SQL保存為文件pop_tablenames.sql，并在psql中執行\i命令，將數據都插入到相應的表格中，如下所示：
-- customer table
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Simon','Cozens','7 Shady Lane','Oakenham','OA3 6QW','514 5926');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mrs','Ann','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Richard','Neill','42 Thatched Way','Winersby','WB3 6GQ','505 6482');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mrs','Laura','Hardy','73 Margarita Way','Oxbridge','OX2 3HX','821 2335');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','Bill','O Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526');
-- item table
INSERT INTO item(description, cost_price, sell_price)
VALUES('Wood Puzzle', 15.23, 21.95);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Rubik Cube', 7.45, 11.49);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Linux CD', 1.99, 2.49);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Tissues', 2.11, 3.99);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Picture Frame', 7.54, 9.95);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Fan Small', 9.23, 15.75);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Fan Large', 13.36, 19.95);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Toothbrush', 0.75, 1.45);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Roman Coin', 2.34, 2.45);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Carrier Bag', 0.01, 0.0);
INSERT INTO item(description, cost_price, sell_price)
VALUES('Speakers', 19.73, 25.32);
-- orderinfo table
INSERT INTO orderinfo(customer_id, date_placed, date_shipped, shipping)
VALUES(3,'03-13-2000','03-17-2000', 2.99);
INSERT INTO orderinfo(customer_id, date_placed, date_shipped, shipping)
VALUES(8,'06-23-2000','06-24-2000', 0.00);
INSERT INTO orderinfo(customer_id, date_placed, date_shipped, shipping)
VALUES(15,'09-02-2000','09-12-2000', 3.99);
INSERT INTO orderinfo(customer_id, date_placed, date_shipped, shipping)
VALUES(13,'09-03-2000','09-10-2000', 2.99);
INSERT INTO orderinfo(customer_id, date_placed, date_shipped, shipping)
VALUES(8,'07-21-2000','07-24-2000', 0.00);
-- stock table
INSERT INTO stock(item_id, quantity) VALUES(1,12);
INSERT INTO stock(item_id, quantity) VALUES(2,2);
INSERT INTO stock(item_id, quantity) VALUES(4,8);
INSERT INTO stock(item_id, quantity) VALUES(5,3);
INSERT INTO stock(item_id, quantity) VALUES(7,8);
INSERT INTO stock(item_id, quantity) VALUES(8,18);
INSERT INTO stock(item_id, quantity) VALUES(10,1);
-- orderline table
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(1, 4, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(1, 7, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(1, 9, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 1, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 10, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 7, 2);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 4, 2);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(3, 2, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(3, 1, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(4, 5, 2);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(5, 1, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(5, 3, 1);
-- barcode table
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241527836173', 1);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241574635234', 2);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6264537836173', 3);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241527746363', 3);
INSERT INTO barcode(barcode_ean, item_id) VALUES('7465743843764', 4);
INSERT INTO barcode(barcode_ean, item_id) VALUES('3453458677628', 5);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6434564564544', 6);
INSERT INTO barcode(barcode_ean, item_id) VALUES('8476736836876', 7);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241234586487', 8);
INSERT INTO barcode(barcode_ean, item_id) VALUES('9473625532534', 8);
INSERT INTO barcode(barcode_ean, item_id) VALUES('9473627464543', 8);
INSERT INTO barcode(barcode_ean, item_id) VALUES('4587263646878', 9);
INSERT INTO barcode(barcode_ean, item_id) VALUES('9879879837489', 11);
INSERT INTO barcode(barcode_ean, item_id) VALUES('2239872376872', 11);
輸入命令如下圖所示：
Figure 5.1 Insert data to tables
6.Accessing the Data
在psql中輸入\dt命令來查看數據庫中的表格，如下圖6.1所示：
Figure 6.1 Use \dt command to list tables
插入數據后，可以用SELECT命令來簡單查看一下數據，如下圖所示：
Figure 6.2 Query the data
也可以用同樣的命令來查詢其他表中的數據。當然也可以用pgAdmin來查看數據，如下圖6.3所示：
Figure 6.3 View and Edit Data in pgAdmin
7. Summary
通過對國外軟件的簡單介紹，說明了數據庫管理系統在軟件中的重要作用，并說明了在數據庫與應用層之間的數據框架的重要性。
由于PostgreSQL基于類似BSD協議，開源且免費使用，很自由，所以選擇PostgreSQL來學習數據庫的知識。
通過使用psql來創建數據表及插入測試數據，便于對PostgreSQL做進一步的學習。
8. References
1. Neil Matthew, Richard Stones. Beginning Databases with PostgreSQL. Apress. 2005
2. Richard Blum. PostgreSQL 8 FOR Windows. The McGraw-Hill. 2007
3. http://www.postgresql.org/docs/books/