Postgresql Trigger -- (II) 待完

做個Trigger的範例。歷程功能。

相信大家都曾經碰過這樣的狀況,  有一些非常非常重要的資料表，像是金錢往來的會計資料，記錄進出貨的庫存資料，我們希望對它做的所有操作，都能留下記錄，以便萬一出事的時候，可以查到倒底是誰動過資料。

要達到這樣的效果，有兩種做法，一種是在應用程式裡面做，一開始的時候，就開兩張資料表，一張主資料表，一張歷程資料表，主資料表中記下誰，在什麼時候動過資料，歷程資料表中則多一個欄位，記錄是做了什麼動作（新增、修改、刪除）

可是世界往往不像我們想像的那麼美好，有誰會知道，有一天某張資料表會變得那麼重要，重要到要幫它做歷程呢？

因此，大部份狀況，我們都是臨時決定，或被通知，要幫某資料表做歷程。在這種狀況下，往往系統都已經上線使用，要請廠商修改，都要花一筆錢。這種狀況下，PostgreSQL的Trigger就幫上忙了。

第二種作法就是，你可以臨時加開一張歷程表，利用Trigger，設定主資料表有任何動作時（新增、修改、刪除），都要到歷程表中寫入歷程資料。但是，要特別提出來的，主資料表中，一定要記下誰，在什麼時候動過資料，這種做法才能夠適用，因此，記得，在採購任何系統時，一定要記得提出，每張資料表中，必需有operateBy (最後資料操作者)，這個欄位，以免碰上要加歷程功能時，廠商還要修改加錢。

如果你忘記了，那Trigger只能幫我們留下，主資料表在什麼時候 operateAt，被做了什麼操作operate。至於是誰，你可能要到系統的登入記錄去找找，那個時間，有那一個有權限的人登入了系統。至於是不是他做的，那就還有得爭了。

-- Drop Table exgRateLog;
-- Drop Table exgRate;


Create Table exgRate (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6),         　   -- 匯率
   operateAt timestamp,
   operateBy varchar(15)
);

Create Table exgRateLog (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6),         　   -- 匯率

   operateAt timestamp,

   operateBy varchar(15),

   operate char(1)
);

Postgresql Trigger -- (I)

Trigger 中文名稱叫觸發器。

為什麼要叫觸發器? 因為我們希望，我們可以在某個資料表上，設定在某個動作（新增、修改、刪除）之前或之後，PostgreSQL能夠被觸發，自動執行一段我們設計好的程式碼。

所以，觸發器的使用設計步驟可分為兩個動作

1. 設計要自動執行的程式碼，建立一個函數Function

2. 對要監控的資料表，設定觸發器，以呼叫已建立的函數。

3. 測試

Partition Table -- (III)

因為partition table 談到rule 和 trigger，　所以打算介紹一下。

前面在談到
－－－
開發rule 或 trigger, 對操作主資料表的行為（新增、修改、刪除），重導到子資料表去。
－－－
這個東西，對一般的開發者來說，恐怕是十分陌生。簡單講，當你不想改，或無法改客戶端程式的時候，你就需要rule或trigger。

想像一下下面的場景，公司或你，從某廠商那裡採購了一套系統，剛開始大家用得非常高興，經過一年兩年，你有新的需求，跟廠商談了以後，他給了一個天價，要不然就不做，或是很不幸地，這家廠商倒了，你也找不到人來幫你改。

這時候，這套系統對公司來說已經是不可或缺；你又沒錢，或是有錢但禁不起轉換系統的風險，而你需要的，只是小小的一個功能修改。你該怎麼做？

如果你使用PostgreSQL做為後端的資料庫，PostgreSQL支援trigger和rule來幫你，讓你不需要修改廠商的程式，也可以達到你要的效果。

Partition Table -- (II)

根據PostgreSQL網站的資料
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
PostgreSQL支援 1. Range Partiontion    2. List Partition   兩種分割方式。實際上, 這兩種分割方式只是應用的不同, 它底層的機制, 是相同的。

我們最常使用到的分割，大概就是Range Partition。我們常會碰到隨著日期增加，資料量也不斷增加的資料，像是銀行的匯率資料，計程車隊的GPS定位資料等。這些資料，有些是不能丟，必需保存做為統計資料使用，這種資料，就必需使用partition的技術，進行保存，以方便後續的查詢使用。

但是，以系統設計的觀點來看，當你的資料會隨時間無限制增大的時候，整個系統的效能，勢必會被拖垮，容我提醒一聲，Patition並不是萬靈丹，它只是讓你操控大容量資料的一個手段而已，以之前提到的案例，銀行的匯率資料，過了一年（一個會計經營年度），恐怕就沒有什麼使用的價值，GPS定位資料，過了一個月（一個拆帳結算周期），恐怕也就沒什麼再使用的必要，在系統設計時，必需設計一個定期清理的機制，每年每月，將資料搬到備份表或歷程表中。只留下有使用價值的資料區間。

系統要使用的，必需是一個穩定的資料量或資料區間，否則，你就無法保證系統的穩定性。在這個設計前提下，我們才可以使用Partition的技術，技術是為系統服務的，絕對不要陷入為技術而技術的境地，這會傷害到你個人的價值。

簡單地講，PostgreSQL Partition Table的使用步驟如下：

1. 建立一個主資料表 Master table，主資料表必需有一個關鍵欄位，做為分隔界限的依據。
2. 繼承主資料表，建立子資料表 child table
3. 在子資料表建立限制Constraint, 限定關鍵欄位的區間間隔。
4. 在子資料表針對可能的查詢欄位，建立索引index
5. 開發rule 或 trigger, 對操作主資料表的行為（新增、修改、刪除），重導到子資料表去。
6. 把postgresql.conf 設定檔中的constraint exclusion參數打開，這樣針對主資料表的查詢，會針對子資料表進行最佳化。

完成上述的步驟後，我們對主資料表的新增、修改、刪除，PostgreSQL會自動地，對相應的子資料表做動作，當我們對資料表做出大資料量的查詢時，PostgreSQL會將查詢改寫，針對子資料表查詢，我們不會感覺到子資料表partition table 的存在。

Partition Table -- (I)

打算深入地談談partition table。
如果曾經開發過一些真實運行的系統，那就一定會碰上一個問題，資料量過大。

舉個例子來說，像是計程車業用的GPS衛星定位系統，每台車，每隔三分鐘就傳回最新的位置，一天就會累積 (60 min / 3 duration) *24 = 480 筆資料，一年就是 175,200筆資料，以200台加盟計程車來算，一年會累積的資料大概就會有3504萬筆資料。

三千五百多萬筆資料，可以想見的，如果要你查查某台車，在某個時間所經過的位置，那對資料庫會是多大的負擔。所以當我們碰上巨量的資料要儲存、查詢時，就需要資料庫支援一種特殊的機制 Patitioning，將巨量的資料分散成為小的資料區塊。

這也就是要談的 PostgreSQL partition table.

PostgreSQL Crosstab -- (VII)

之前資料格式有點雜亂, 利用 group by 整理一下
交叉表三 Version 2
------------------------------------
select currency, purpose, sum(即期)as 即期,sum(現金) as 現金
From
(
SELECT *
FROM crosstab(
  'select currency, purpose, currType, rate From exgRate',
  'Select distinct currType from exgRate' )
  AS t(
       currency char(3),
       purpose varchar(10),
       "即期" numeric(18,6),
       "現金" numeric(18,6)
       ) order by purpose, currency
) dt group by currency, purpose

PostgreSQL Crosstab -- (VI)

交叉表三
－－－－－－－－－－－－－－－－－－－
SELECT *
FROM crosstab(
  'select currency, purpose, currType, rate From exgRate',
  'Select distinct currType from exgRate' )
  AS t(
       currency char(3),
       purpose varchar(10),
       "即期" numeric(18,6),
       "現金" numeric(18,6)
       ) order by purpose, currency;

PostgreSQL Crosstab -- (V)

交叉表二
－－－－－－－－－－－－－－－－－－－

SELECT *
FROM crosstab(
  'select currency, currType, purpose, rate From exgRate',
  'Select distinct purpose from exgRate' )
  AS t(
       currency char(3),
       currType varchar(10),
       "買入" numeric(18,6),
       "賣出" numeric(18,6)
       ) order by currType, currency

    
看起來，資料表還需要進一步的資料整理，不過功能是OK了

PostgreSQL Crosstab -- (IV)

接下來，就是以輸入的基礎資料表，測試crosstab函式的功能。
crosstab有兩道SQL，第一道稱為Source SQL，指定資料的來源；第二道稱為Category SQL，指定crosstab要由那個欄位延伸展開。

在這個例子中，我們將資料依不同幣別，進行展開。

交叉表一
－－－－－－－－－－－－－－－－－－－
SELECT *
FROM crosstab(
  'select currType, purpose, currency, rate From exgRate',
  'Select distinct currency from exgRate Order by currency' )
  AS t(
       currType varchar(10),
       purpose varchar(10),
       AUD numeric(18,6),
       CAD numeric(18,6),
       GBP numeric(18,6),
       HKD numeric(18,6),
       USD numeric(18,6)
       )
 order by currType;

PostgreSQL Crosstab -- (III)

接下來要建個測試資料，我就以之前提到的匯率應用做例子，建個匯率表exgRate

建資料表的SQL如下


Create Table exgRate (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6)         　   -- 匯率
);

插入資料的SQL如下：
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','買入','現金',31.08);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','買入','現金',3.911);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','買入','現金',46.51);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','買入','現金',28.24);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','買入','現金',30.27);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','買入','即期',31.37);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','買入','即期',4.014);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','買入','即期',47.36);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','買入','即期',28.45);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','買入','即期',30.53);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','賣出','現金',31.62);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','賣出','現金',4.087);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','賣出','現金',48.44);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','賣出','現金',28.95);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','賣出','現金',31.08);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','賣出','即期',31.49);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','賣出','即期',4.084);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','賣出','即期',47.86);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','賣出','即期',28.71);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','賣出','即期',30.81);

比較有趣的是這道SQL指令中，用到to_timestamp的函數。因為匯率是不斷更新變動的，所以我們必需記錄匯率輸入的時間（精確到秒），這樣在換匯時，才能知道要用什麼時候的匯率。

PostgreSQL Crosstab -- (II)

PostgreSQL的預設安裝，是沒有啟用crosstab功能的。如果你需要crosstab，你需要

1. 檢查函式庫
檢查一下C:\Program Files\PostgreSQL\8.4\lib目錄中，有沒有名為tablefunc.dll的函式庫檔。

2. 裝載tablefunc功能
開啟　C:\Program Files\PostgreSQL\8.4\share\contrib目錄，找到名為tablefunc.sql的檔案，執行它。

3. 檢查資料庫的函式
如下圖，如果安裝成功，你應該多了11個資料庫的函式

其它平台的安裝，我就不細講了，相信能用其它平台的人，也應該有能力照貓畫虎才是。

PostgreSQL Crosstab -- (I)

我準備用長一點的篇幅來講講Crosstab。Crosstab的中文，在postgresql中一般翻做「交叉表」，而微軟系列一般翻做「樞紐分析表」「樞軸表」，我個人喜歡交叉表。

那什麼是Crosstab呢? 簡單講，如果我現在有一個匯率表如下，
－－－－－－－－－－－－－－－－－－－－－
貨幣種類  匯率種類      AUD  CAD  GBP  HKD  USD
即期      　買入    　      28.45 30.53 47.36 4.014 31.37
即期      　賣出        　  28.71 30.81 47.86 4.084 31.49
現金      　買入          　28.24 30.27 46.51 3.911 31.08
現金      　賣出          　28.95 31.08 48.44 4.087 31.622

我在資料庫中的資料設計是不是就照著看到的做就好？很明顯的答案是NO，舉例來說，如果明天要支援的貨幣多一個日元JPY呢? 那是不是要去alter table 變更資料表的設計??
換一步說，明天，可能人家要的資料會變這樣
－－－－－－－－－－－－－－－－－－－－－
貨幣種類  幣別  買入 賣出
即期      AUD  28.45 28.71
即期      CAD  30.53 30.81
即期      GBP  47.36 47.86
即期      HKD  4.014 4.084
即期      USD  31.37 31.49
現金      AUD  28.24 28.95
現金      CAD  30.27 31.08
現金      GBP  46.51 48.44
現金      HKD  3.911 4.087
現金      USD  31.08 31.622

甚至可能是這樣，同時還要多支援「遠期10天」「遠期30天」，兩個貨幣種類

－－－－－－－－－－－－－－－－－－－－－

匯率種類  幣別  即期 現金    

買入      AUD  28.45 28.24   

買入      CAD  30.53 30.27   

買入      GBP  47.36 46.51   

買入      HKD  4.014 3.911   

買入      USD  31.37 31.08   

賣出      AUD  28.71 28.95   

賣出      CAD  30.81 31.08   

賣出      GBP  47.86 48.44   

賣出      HKD  4.084 4.087   

賣出      USD  31.49 31.622  

哇，又要人仰馬翻去重新整理一次嗎？我們有更聰明的解法，資料庫的設計，應該如下，把所有可能變顯示欄位的資料項，變成一個個欄位，之後再採用Crosstab的方式，去動態產生所有的資料表。

------------------------------------------------------------
幣別     匯率種類 貨幣種類 匯率
 USD     買入     現金    31.08
 HKD     買入     現金    3.911
 GBP     買入    現金    46.51
 AUD     買入    現金    28.24
 CAD     買入    現金    30.27
 USD     買入    即期    31.37
 HKD     買入    即期    4.014
 GBP     買入    即期    47.36
 AUD     買入    即期    28.45
 CAD     買入    即期    30.53
 USD     賣出    現金    31.62
 HKD     賣出    現金    4.087
 GBP     賣出    現金    48.44
 AUD     賣出    現金    28.95
 CAD     賣出    現金    31.08
 USD     賣出    即期    31.49
 HKD     賣出    即期    4.084
 GBP     賣出    即期    47.86
 AUD     賣出    即期    28.71
 CAD     賣出    即期    30.81

這樣子，前面三個表，就是最後這個「資料表」的「交叉表」，PostgreSQL以tablefunc套件，支援交叉表的實作。

資料庫移植性 移植什麼？

有個同行跟我說，他們家的系統移植性很棒，DB2, Oracle, MS SQL每個資料庫都可以搭

我心裡在說，那是你沒碰上要一秒內要處理上百萬上千萬筆資料的狀況，沒碰上那種連IBM大暴龍都搞不定的運算，等再過五年，你再來說說這話。

實際上，你就是會碰上這種操資料庫，就是會碰上需要那種需要某牌資料庫特異功能的應用，碰上這種東西，資料庫移植性，就變成一句笑話。

讓我們面對現實吧，鳥托邦並不存在，我們不能保證完美的資料庫移植性。我們應該要考慮的是，我的系統要支援幾個資料庫?　為什麼? 通常，考慮的因素是


一、商業的需求
客戶已經講的很清楚，他們家就是用DB2 Oracle，維護人員受的訓也都是DB2, Oracle。如果你不用，那最好你們家的系統強悍無比，Domain knowledge比客戶還精，客戶不找你不行，要不然，接不到案子不要怪別人。

二、功能的需求
如果客戶熟悉的資料庫，功能不符合你系統的需求。像是你就是需要某牌資料庫的特異功能，才能把他要的功能或報表跑出來，那你可以堅持，要不然，還是別太鐵齒。

三、預算的需求
這就更簡單了，付錢的是老大，老大要什麼，我們就給什麼。如果老大沒錢，那更簡單，我們就是老大，就用PostgreSQL吧，畢竟系統原生就是在PostgreSQL環境發展的。

資料庫移植性，這其實是一個角力的問題，其實終端客戶才不管你用什麼資料庫，只是有時候你需要去符合客戶IT部門的某些政策，某些規定。碰上這種東西，我都是叫業務先去談上線，再談移植計劃。先把系統裝進去，符合客戶的需求，終端客戶用上手了，我們再來談資料庫移植。

而在大多的的狀況下，移植資料庫要花時間改程式，要花時間測試，更重要的，是終端客戶也要花時間確認沒有問題，客戶會願意花時間去做這些沒用的功嗎？有這些時間，不如幫他多寫一隻新功能，滿足他一個新需求，資料庫移植這件事，自然就無疾而終了。

但是，這種作法，有時候也會碰上鐵板，特別是在應用很成熟的領域裡。在月度工作會議裡，終端客戶的部門沒有新需求提出，IT部門就會把這件事拉出來講。我的作法是，就配合嘛，反正這領域沒有新功能好寫了，那就排進度做移植，擴充支援的資料庫，也是增強這個產品線的競爭力，要不然，客戶簽維護是讓我們大眼看小眼嗎? 總要有個交待吧，是吧 !!

PostgreSQL 9.0出來了, So What ??

PostgreSQL 9.0出來了, 那又怎麼樣 ??


最近PostgreSQL 9.0出來了，大家都十分的興奮，看了看，零零總總共２００個修改，其中有許多是許多人想很久的，像是64位元視窗平台的支援，支援Python 3等。

不過，就我個人而言，我想說的是「那又怎樣？」

我開發的平台是Java，配合Hibernate建立的ORM映射層，比較關心的是後端PL/pgSQL的功能有沒有新發展。9.0 的出現，並沒有什麼殺手級的新功能，讓我一定要馬上昇級到9.0，也就是說9.0並沒有大幅改變我的工作。

相反的，9.0 為我帶來不必要的昇級負擔。年輕的時候，一有新版本出來，總會想要馬上昇級到最新版本，但是，年紀漸長後，卻出現不同的思考。

對我來說，發展的是ERP進銷存之類的商用軟體，如果不是在開發功能時需要新版的新功能，我為什麼要昇級？客戶端已佈署好、運作正常的系統，為什麼要昇級? 為了爽嗎？不必吧!! 就算客戶的IT部門提到，我總會問，為什麼要昇級，是現在的功能不符需求，需要新版本的新功能嗎？客戶買的是一個系統，不是買一個資料庫引擎。如果是系統的需求，客戶不提，我也會主動去幫客戶昇級，如果不是，昇級過程發生的風險，是我負責，還是客戶自己扛？

狀況很明顯，昇級是要走的，但是什麼時候走，必需由我決定。

根據我的經驗，新版本剛出現的時候，絕對不是該昇級的時候。我會等到9.2, 9.4, 9.6，甚至是10.0出現的時候，再針對9.X系列，挑一個穩定的平台出來做移植，並進行所有功能的測試。至於現在，並不是我會考慮昇級的時間，再等一兩年，這一兩年，好好測測新功能，才是該做的事情。

絕對..絕對..不要趕流行，可以玩玩新功能，可以利用新功能，想想系統可以加什麼賣點，但是絕對不要為了昇級而昇級。這是一個老工程師的肺腑之言。

PostgreSQL 簡繁用語 對照

英文的中譯，一向是個問題，簡繁體習慣用語的不一致，在問題上加上了更多的問題。

記得在PostgreSQL 7.X的版本，我做了一個繁體中文的手冊，這個版本，後來被改變為簡體版的初始版本，現在看起來，很多用語仍遵循我當初訂下的用詞，看起來實在很有成就感。

如view，一般譯為「檢視」，我則譯為「視圖」。Oracle的譯詞則為視觀表，現在看起來，視觀表似乎是比較好的譯法。

如regression test, 當初找不到譯詞，我就譯為「迴歸測試」，隱諭它像統計迴歸一樣，是一個測試統計的功能。轉簡體時，字元對映出了一些狀況，變成「回歸測試」了。

再如Write-Ahead Logging (WAL)，譯為「預寫式日誌」，為了怕譯得不好，特地將英文原詞附於譯詞之後，到目前看起來，是已經被普遍接受了。在這種狀況下，英文原詞可以移掉了，還留在內容表中，實在是不需要了。

為此，我手上保留了一份　PostgreSQL 簡繁用語 對照表，有需要的同好，再與我聯絡吧。

Client Encoding, PostgreSQL開放性的亮點

client encoding 這個東西，實在是PostgreSQL開放性的一個亮點。

簡單講，資料庫與客戶端的文字編碼，不見得需要一致。相信開發者都曾經碰過，需要同時服務中國與台灣使用者的狀況，中國使用者輸入的是GB編碼的簡體，台灣使用者輸入的是BIG5編碼的繁體中文，兩者的資料都存入UTF8編碼的資料庫中。

在這種狀況下，開發Web版的程式還算簡單，但如果是開發AP版的程式，那就真得有點欲哭無淚了。

在PostgreSQL的使用手冊，第二十二章中有提到Localization，它提出了資料庫伺服端和程式客戶端文字編碼的說明，並支援一些常見的搭配組合。

使用上
1 .使用psql
以下列形式的指令，進行客戶端編碼設定

\encoding BIG5

2. 在程式中，下SQL指令換編碼

SET CLIENT_ENCODING TO 'value';

顯示現在的客戶端文字編碼

SHOW client_encoding;

取得系統預設的文字編碼

RESET client_encoding;

以我個人的使用經驗來看，資料庫的編碼最好使用UTF8，之前使用的EUC_TW, BIG5, MULE_INTERNAL等，現在完全可以放棄了。

用PSQL匯入固定長度的資料 Fixed Width Data

Leo Hsu and Regina Obe在其網誌上寫到了這個方法，心喜之，分享於同好。

有人覺得PSQL這種終端機形式的客戶端程式過時了，但是事實告訴我，在某些狀況下，特別是追求效能的狀況下，PSQL反而會比PgAdmin 這種GUI的客戶端有用，而且是有用多了，特別是在自動化作業中，如每天自動將資料匯入匯出的應用上，不用PSQL，簡直是無法想像。用pgAdmin去點選啟動，別鬧了。因此，適度的掌握PSQL是必需的。

如果和一些老系統打過交道，通常都會發現，接收的資料，通常都會是用長度來識別。傳送一個文字檔案過來，告訴你第一到第二字元是什麼資料，第三到第五字元是什麼資料，資料長度不足會補空格，諸如此類。

問題是什麼？問題在於，我們要轉入我們的資料庫時，要依長度先進行切割，切割後要把空格都刪掉，這是一件麻煩事，常要動用試算表軟體來支援處理。

這個方法的精神很簡單，其步驟約略如下：

1. 在psql中，將文字檔資料利用\copy指令，存入暫存的資料表places_staging
要注意，執行前先設定好資料的編碼方式，台灣的繁體中文，可能要指定為BIG5或UTF-8

set client_encoding = 'latin1';
\copy places_staging FROM C:/censusdata/places2k.txt DELIMITER AS '|'

2. 建立真正存資料的資料表places

CREATE TABLE places(
    usps char(2) NOT NULL,
    fips char(2) NOT NULL, 
    fips_code char(5),
    loc_name varchar(64)
);

3. 利用substring 搭配trim函式，使用SQL將資料轉入places 

INSERT INTO places(usps, fips, fips_code, loc_name)
    SELECT substring(data,1,2) As usps, 
           substring(data,3,2) As fips, 
           substring(data,3,5) As fips_code,
      trim(substring(data, 10,64)) As loc_name
    FROM places_staging;


掌握住精神，在其它的資料庫這種作法都可以使用，是非常有用的技巧，值得學習。

Materialized views 具現視觀表

最近在PostgreSQL主站上，看到Robert Hass談到Robert Hass談到Materialized views，這實在是一個很有趣的題目。


首先，我先解釋一下，什麼是具現視觀表 Materialized views，由原文來看實在很難了解它是什麼東東，不過如果對開發過資料倉儲Data WareHouse的人來說，具現視觀表就十分熟悉了。


舉個例子來說，如果我們手上有某店舖每天的各種商品的銷售營業額資料，如果我們現在希望有該店舖每周、每月、每季、每年的銷售營業額資料，那該怎麼辦？


－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－－
ＰＫ　　營業日　　　　　　商品種類　　　　　日銷售額
－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－－
１　　　2010-01-01 Ａ　　　　　　　 100
２　　　2010-01-01 B　　　　　　　　200
３　　　2010-01-02 Ａ　　　　　　　 121
４　　　2010-01-02 B　　　　　　　　210　

最直接的想法就是，我就根據這張表，搭配另一張時間的資料表，

－－－＋－－－－－＋－－－－－－－－＋－－－－－－－－＋
ＰＫ　 營業日　　week of year        month of year

－－－＋－－－－－＋－－－－－－－－＋－－－－－－－－＋
　１　　2010-01-01 W1 1
為每周、每月、每季、每年，各做一個視觀表view出來就可以了嘛

在資料倉儲的應用中，這些視觀表會被非常頻繁地查詢，因此，最合理的做法是，把視觀表的結果，以資料表的形式，直接放在資料庫中，不要每次查詢時，就去執行一次視觀表的查詢指令，動態產生視觀表。查詢這種視觀表，就直接查詢已統計完成的資料表即可。


因此，這種被產生出來的視觀表，它就和傳統的視觀表有了差別，它不是動態產生的，它是基礎資料統計的結果，是被具體實現後的視觀表，因此，我稱它叫具現視觀表。


我們可以想見，具現視觀表的實作，會有許多的問題，比如說，如果基礎資料有變動，那依據它產生的一系列具現視觀表，就需要全部重新更新，效能的問題要如何克服？


我們可以說，沒有具現視觀表，PostgreSQL在資料倉儲的應用上，就會受到了阻礙，因此在Peter Eisentraut的調查中，具現視觀表，就變成PostgreSQL 9中，大家最想要的功能。

pgAdmin III , PostgreSQL的GUI客戶端程式

時間很快，進入了２０１０年，我們使用資料庫的時候，當然是希望能夠有一個便利的GUI界面，讓我們以終端機模式作業，當然不是不可以，但是，誰會自己去找虐呀，有方便的方式，當然是要加以使用的。

pgAdmin III 就是這樣的東西，當你安裝視窗版本的PostgreSQL時，它是預設安裝的軟體，我們只要使用它，就可以便利地進行資料庫的操作。

當然，這不是說文字界面的東西就不需要學了，畢竟SQL語言還是以指令的形式在操作的，可不能變成傻瓜，連SQL都不學了，那真真叫丟了根本。

pgAdmin 連接資料庫

當pgAdmin連上資料庫後，可以在左方看到資料庫中所有的表格。在PostgreSQL的觀念中，預設使用名為public的架構綱要schema，點開後，就可以看到所有的資料表格。

讓PostgreSQL變成網路資料庫

讓PostgreSQL變成網路資料庫

PostgreSQL安裝時，預設是做為單機資料庫存在的。也就是說，它只接來自本機的資料庫操作動作，如果你開發的是單機程式，那當然是運作良好。

如果你開發的程式，需要執行在其它台機器時，這個預設的設定，就造成了問題。它不接受來自其它主機的資料庫操作。這時候，你就需要對它做進一步的修改。

PostgreSQL做為網路資料庫

PostgreSQL安裝的時候，會要求指定資料庫資料放置的目錄，如果你使用的是視窗版本的PostgreSQL，那它的目錄應該會是C:\Program Files\PostgreSQL\8.4\data


1. 設定接受外部主機的連線
在這個目錄中，有一個postgresql.conf的檔案，設定PostgreSQL是否接受外界的連線
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
# change listen_address to '*', ie., accept db operation request from all hosts
#listen_address=’localhost’
listen_address=’*’
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

2.設定外部連線的認證方式
另外，還有一個pg_hba.conf的檔案，負責設定外界連線的認證方式

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

# add auth from intranet, accept connection with db username and password

host all all 192.168.1.0/24 password

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

這個設定的意思是，接受所有192.168.1.XXX形式位址主機發出的資料庫連線，24是網路遮罩255.255.255.0的意思，連線的主機，必需送正確的資料庫使用者名稱與密碼，才能使用資料庫主機的服務。

為什麼要用PostgreSQL ?

這是一個最直接的問題, 為什麼我(你) 要用PostgreSQL ?

當我自己問我自己這個問題的時候, 我的答案大概如下

1. 我不想重覆又重覆地浪費自己的生命.
身為一個資訊時代的技術人員，我需要一個穩定的平台。我不想跟隨IBM, Oracle, MS的步調每兩年就來次大昇級，原本花了不少時間掌握的技能，需要再次重新學習。有時候還會來個全面大昇級，學過的全都不能用了。

2. 我需要可以商業化的資料庫核心
當我要販售我開發的系統時，我希望我使用的資料庫可以一併出貨給我的客人，客人不需要再向IBM, Oracle, MS等資料庫廠商付一筆授權費。而這授權費通常不怎麼便宜。PostgreSQL採用BSD的版權，我和客人不需要付授權費。

3. 我需要一個強大的資料庫引擎
當我開發我的程式時，我需要交易Transaction, 觸發Trigger等強力的機制，支援我的程式。沒有這些功能，可以想見，我的開發工作會難上不少。

4.我需要一個取得容易，價格低廉的資料庫引擎
最好網路可以下載，價格又很低廉。這樣我可以很容易取得。PostgreSQL可以在網路上下載到，免費，這真是太好了。

為了以上的這些因素，我決定使用PostgreSQL做為我開發系統的核心，但是我也有如下的疑慮。

1. 萬一資料庫掛掉了，誰來救我？
天有不測風雲，誰也不能保證系統一定沒問題，就算我初一十五都有拜乖乖，如果硬體出問題，或是資料庫軟體出狀況，資料庫掛掉時，誰能幫我？

後來我轉念一想，之前服務的公司，花了那麼多授權費，結果出狀況時，沒有一家派技術人員出來，還叫我去看授權的條文，每一家都不負責幫我把資料救回來，還是要靠自己用備份的資料做回復，讓我大嘆，那麼多的錢都花到狗身上去了，這些錢留下來，我都可以再建一部備份主機了。

反正都是自己要解決，我還是備份自動化，平常做做災難回復演練還比較有用。

2. PostgreSQL那裡可以找到學習的資料？
雖然我的英文不怎樣？但是看看技術文件是夠用了，市面上也有幾本中文的書，有問題上郵件列表發問題，也是可以得到解答。

對於一些免費的資料庫核心，像是IBM, Oracle, MS釋出的免費版本資料庫，我其實也不會刻意不用，但是我還是以PostgreSQL做為開發的主要測試對象，如果客人有要求，我才會去做移植的測試，畢竟付錢的是老大，客人願意付錢，我怎麼都可以配合的。

如果你像我一樣，是IT的從業人員，或者你是在學的學生，教學的老師，或者你是要賺錢的老板，我都建議您考慮PostgreSQL。