用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;


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