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的函數。因為匯率是不斷更新變動的，所以我們必需記錄匯率輸入的時間（精確到秒），這樣在換匯時，才能知道要用什麼時候的匯率。