Postgresql 如何建立一個有N個列的表

標籤：postgresql   產生列   array_agg   array_to_string   

如果需要建立一個有99列的表，列名都是有規律的比如s01,s02,s03……也許你會想到如下語句

CREATE TABLE tb_sxx ( s01 varchar(10), s02 varchar(10), s03 varchar(10), …… s99 varchar(10) );

有沒有更快捷的方法呢？當然有了，不妨藉助一下Postgresql的三個有力函數
generate_series(x,y) --產生x到y之間的值
array_agg(x) --把x值轉成 如{1,2,3,4}的格式，即轉成數組
array_to_string({} , ‘,‘) 把數組轉成字串，其中 ‘,‘ 是數組的分隔字元

為了更直觀的理解以下舉例（產生一個5列的表），分步說明，如果對上述三個函數很熟悉可直接到第6步：

1.產生1到5之間的數
SELECT generate_series(1,5);

hotel=> SELECT generate_series(1,5); generate_series -----------------               1               2               3               4               5(5 rows)

2.把1到5的數轉成數組
 SELECT array_agg(i)
FROM generate_series(1,5) AS i;

hotel=> SELECT array_agg(i)hotel-> FROM generate_series(1,5) AS i;  array_agg  ------------- {1,2,3,4,5}(1 row)

3.加工第2步產生的資料，將格式變為{s01,s02,s03,s04,s05}
 SELECT array_agg(‘s‘ || lpad(i::text,2,‘0‘))
FROM generate_series(1,5) AS i;

hotel=> SELECT array_agg(‘s‘ || lpad(i::text,2,‘0‘))hotel-> FROM generate_series(1,5) AS i;       array_agg       ----------------------- {s01,s02,s03,s04,s05}(1 row)

解釋：
i::text 是將i轉換成字元類形
lpad 是將i轉換為2位，位元不足時在左邊加0

4.加工第3步產生的資料，將其轉換為{s01 varchar(10),s02 varchar(10),s03 varchar(10),s04 varchar(10),s05 varchar(10)}
 SELECT  array_agg(‘s‘ || lpad(i::text,2,‘0‘) || ‘ varchar(10)‘)
FROM generate_series(1,5) AS i;

hotel=>  SELECT  array_agg(‘s‘ || lpad(i::text,2,‘0‘) || ‘ varchar(10)‘) hotel-> FROM generate_series(1,5) AS i;                                          array_agg                                          --------------------------------------------------------------------------------------------- {"s01 varchar(10)","s02 varchar(10)","s03 varchar(10)","s04 varchar(10)","s05 varchar(10)"}(1 row)

5.加工第4步產生的資料，將其轉換成字串
 SELECT  array_to_string (
              array_agg(‘s‘ || lpad(i::text,2,‘0‘) || ‘ varchar(10)‘)
               , ‘,‘)
FROM generate_series(1,5) AS i;

hotel=> SELECT  array_to_string ( hotel(>               array_agg(‘s‘ || lpad(i::text,2,‘0‘) || ‘ varchar(10)‘) hotel(>                , ‘,‘)hotel-> FROM generate_series(1,5) AS i;                                 array_to_string                                 --------------------------------------------------------------------------------- s01 varchar(10),s02 varchar(10),s03 varchar(10),s04 varchar(10),s05 varchar(10)(1 row)

6.最終產生建表語句
 SELECT  ‘CREATE TABLE tb_s5 ( ‘ ||
              array_to_string (
              array_agg(‘s‘ || lpad(i::text,2,‘0‘) || ‘ varchar(10)‘)
               , ‘,‘) ||
              ‘);‘
FROM generate_series(1,5) AS i;

hotel=>  SELECT  ‘CREATE TABLE tb_s5 ( ‘ ||hotel->               array_to_string ( hotel(>               array_agg(‘s‘ || lpad(i::text,2,‘0‘) || ‘ varchar(10)‘) hotel(>                , ‘,‘) ||hotel->               ‘);‘hotel-> FROM generate_series(1,5) AS i;                                                ?column?                                                -------------------------------------------------------------------------------------------------------- CREATE TABLE tb_s5 ( s01 varchar(10),s02 varchar(10),s03 varchar(10),s04 varchar(10),s05 varchar(10));(1 row)

至此建表語句就產生了，複製粘貼就可以執行了，如果是psql亦可用下面的將下面的語句儲存為
build_s5.psql檔案，然後用psql直接執行
psql -h localhost -p 5432 -U username -f  build_s5.psql -d dbname

檔案內容如下：

\a \t
 SELECT  ‘CREATE TABLE tb_s5 ( ‘ ||
              array_to_string (
              array_agg(‘s‘ || lpad(i::text,2,‘0‘) || ‘ varchar(10)‘)
               , ‘,‘) ||
              ‘);‘
FROM generate_series(1,5) AS i;
\g create_s5.sql
\i create_s5.sql

解釋：
\a 關閉對齊模式
\t 關閉標行輸出
\g 將查詢結果輸出到指定檔案
\i 執行檔案

本文出自 “鏡子” 部落格，請務必保留此出處http://383133430.blog.51cto.com/454215/1739310

Postgresql 如何建立一個有N個列的表