2024年04月24日玄貓（BlackCat）

PostgreSQL日期時間與資料型別深入解析

本文探討 PostgreSQL 中的日期時間和資料型別處理，涵蓋日期時間型別、JSON 與 JSONB 應用、資料型別轉換，以及使用 COPY 命令進行資料匯入和匯出。文章以美國縣級人口普查資料匯入為例，詳細說明瞭 CSV 檔案處理、標頭列和分隔符的應用，並提供程式碼範例和最佳實務。

資料函式庫 SQL

PostgreSQL 日期時間資料型別 JSON CSV COPY

PostgreSQL 提供豐富的日期時間型別，例如 timestamp、date、time 和 interval，滿足不同應用場景的需求。選擇正確的型別對於資料函式庫設計至關重要，例如 timestamp with time zone 能夠處理時區差異，確保時間資料的準確性。interval 型別則方便進行日期計算和時間間隔處理。除了日期時間，PostgreSQL 也支援 JSON 和 JSONB 格式，便於儲存和查詢結構化資料。利用 CAST() 函式，開發者可以靈活地進行資料型別轉換，滿足不同運算需求。

日期與時間的處理

在資料函式庫設計中，正確處理日期與時間是非常重要的。PostgreSQL 提供了多種日期和時間的資料型別，以滿足不同的需求。瞭解這些資料型別的特點和使用場景，可以幫助我們更好地設計和管理資料函式庫。

日期與時間的資料型別

PostgreSQL 支援多種日期和時間的資料型別，主要包括以下幾種：

日期與時間資料型別一覽

資料型別	儲存大小	描述	範圍
timestamp	8 位元組	日期和時間	公元前 4713 年至公元 294276 年
date	4 位元組	日期（無時間）	公元前 4713 年至公元 5874897 年
time	8 位元組	時間（無日期）	00:00:00 至 24:00:00
interval	16 位元組	時間間隔	+/- 178,000,000 年

詳細說明

timestamp：記錄日期和時間，通常用於需要精確記錄事件發生的時刻，如航班的起飛和降落時間、事件的時間戳等。建議使用 timestamp with time zone 以確保時間的正確性。
date：僅記錄日期，適用於只需要日期資訊的場景。
time：僅記錄時間，可以加上 with time zone 以指定時區，但單獨使用時間時，時區資訊可能無意義。
interval：表示一段時間間隔，用於計算或過濾其他日期和時間欄位。

使用範例

讓我們透過一個範例來瞭解 timestamp with time zone 和 interval 的使用。執行以下的 SQL 指令碼：

CREATE TABLE date_time_types (
    timestamp_column timestamp with time zone,
    interval_column interval
);

INSERT INTO date_time_types
VALUES
    ('2022-12-31 01:00 EST', '2 days'),
    ('2022-12-31 01:00 -8', '1 month'),
    ('2022-12-31 01:00 Australia/Melbourne', '1 century'),
    (now(), '1 week');

SELECT * FROM date_time_types;

程式碼解析

建立表格：首先，我們建立了一個名為 date_time_types 的表格，包含兩個欄位：timestamp_column 和 interval_column。
- timestamp_column 的型別是 timestamp with time zone，用於儲存日期和時間，並包含時區資訊。
- interval_column 的型別是 interval，用於儲存時間間隔。
插入資料：接著，我們向表格中插入了四行資料。
- 前三行的 timestamp_column 使用了相同的日期和時間（2022 年 12 月 31 日凌晨 1 點），但指定了不同的時區：EST、-8 和 Australia/Melbourne。
- 第四行使用 PostgreSQL 的 now() 函式來取得目前的交易時間。
- 每行的 interval_column 分別指定了不同的時間間隔。
查詢結果：最後，我們查詢了 date_time_types 表格中的所有資料。

結果分析

儘管我們在前三行中為 timestamp_column 提供了相同的日期和時間，但輸出結果卻不同。這是因為 PostgreSQL 自動將輸入的時間轉換為查詢時所使用的時區（在此範例中似乎是 UTC-5）。這展示了 timestamp with time zone 資料型別的強大功能，它能夠根據不同的時區輸入正確地處理和顯示時間。

資料型別轉換與應用

在SQL的應用中，正確理解和運用資料型別至關重要。PostgreSQL提供了多種資料型別，包括日期時間、數值、字元等，並支援JSON和JSONB等結構化資料格式。同時，透過CAST()函式，可以實作資料型別之間的轉換，以滿足不同的運算需求。

日期與時間的處理

PostgreSQL的日期時間資料型別可以記錄精確的時間戳記，並根據時區進行調整。例如，timestamp with time zone型別能夠根據使用者的時區顯示不同的時間。

使用interval進行日期計算

interval資料型別可用於對日期和時間資料進行計算。例如，透過將interval新增到或減去timestamp，可以計算出新的日期。

SELECT 
    timestamp_column,
    interval_column,
    timestamp_column - interval_column AS new_date
FROM date_time_types;

內容解密：

timestamp_column: 儲存原始的時間戳記。
interval_column: 儲存時間間隔，例如天、月、年等。
timestamp_column - interval_column: 將時間戳記減去指定的時間間隔，得出新的日期。

JSON與JSONB的應用

JSON（JavaScript Object Notation）是一種用於儲存和交換資料的結構化格式。PostgreSQL支援兩種JSON資料型別：json和jsonb。其中，jsonb支援索引，能夠提高處理速度。

JSON範例

{
    "business_name": "Old Ebbitt Grill",
    "business_type": "Restaurant",
    "employees": 300,
    "address": {
        "street": "675 15th St NW",
        "city": "Washington",
        "state": "DC",
        "zip_code": "20005"
    }
}

內容解密：

JSON結構：由鍵值對組成，例如"business_name": "Old Ebbitt Grill"。
json與jsonb：兩者皆支援JSON格式，但jsonb以二進位格式儲存，支援索引。

其他資料型別的應用

PostgreSQL支援多種其他資料型別，包括布林值、幾何型別、文字搜尋型別、網路地址型別、UUID型別、二進位資料型別等。這些資料型別能夠滿足不同的應用需求。

使用CAST()進行資料型別轉換

在某些情況下，需要將資料從一種型別轉換為另一種。可以使用CAST()函式實作此功能。例如，將時間戳記轉換為字元，或將字元轉換為日期。

CAST()範例

SELECT 
    timestamp_column, 
    CAST(timestamp_column AS varchar(10))
FROM date_time_types;

SELECT 
    numeric_column,
    CAST(numeric_column AS integer),
    CAST(numeric_column AS text)
FROM number_data_types;

SELECT 
    CAST(char_column AS integer) 
FROM char_data_types;

內容解密：

CAST(timestamp_column AS varchar(10)): 將時間戳記轉換為字元。
CAST(numeric_column AS integer): 將數值轉換為整數。
CAST(char_column AS integer): 將字元轉換為整數（若字元中包含非數字內容，將會出錯）。

資料匯入與匯出技術解析

在資料函式倉管理中，資料的匯入與匯出是常見且重要的操作。PostgreSQL 提供了一個強大的 COPY 命令來實作這項功能。本章將探討如何使用 COPY 命令進行資料的匯入與匯出。

使用 COPY 命令進行資料匯入

當資料以分隔文字檔案的形式存在時，PostgreSQL 可以使用 COPY 命令進行批次匯入。COPY 命令是 PostgreSQL 特有的實作，具有多種選項，可以用於包含或排除特定欄位，以及處理不同型別的分隔文字。

資料匯入步驟

取得來源資料：首先，需要將來源資料以分隔文字檔案的形式儲存。
建立資料表：根據來源資料的結構，建立一個合適的資料表來儲存資料。
執行 COPY 陳述式：編寫 COPY 陳述式來執行資料匯入操作。

使用 COPY 命令進行資料匯出

同樣地，COPY 命令也可以用於將資料從 PostgreSQL 資料表或查詢結果匯出到分隔文字檔案中。這種技術在需要與同事分享資料或將資料轉換為其他格式（如 Excel 檔案）時非常有用。

處理分隔文字檔案

分隔文字檔案是一種常見的檔案格式，能夠在不同軟體應用程式之間輕鬆轉移資料。在這種檔案中，每一行代表資料表中的一行，而每個資料欄位則由特定的分隔字元（如逗號）分隔。

分隔文字檔案的操作

許多軟體應用程式都支援從分隔文字檔案匯入和匯出資料。這使得分隔文字檔案成為不同系統之間交換資料的理想格式。

其他資料函式倉管理系統的批次匯入功能

雖然 COPY 命令是 PostgreSQL 特有的，但其他資料函式倉管理系統也有類別似的功能。例如，MySQL 提供了 LOAD DATA INFILE 陳述式，而 Microsoft SQL Server 則有自己的 BULK INSERT 命令。

實務應用與考量

在實際操作中，需要考慮資料的格式、欄位的對應以及可能的錯誤處理。例如，當匯入包含日期時間格式的資料時，需要確保目標欄位的資料型別能夠正確地儲存這些值。

程式碼範例

COPY date_time_types (timestamp_column, numeric_column)
FROM '/path/to/your/file.csv'
DELIMITER ','
CSV HEADER;

內容解密：

COPY 命令用於從檔案中匯入資料到指定的資料表中。
date_time_types 是目標資料表的名稱，括號內指定了要匯入的欄位。
FROM 子句指定了來源檔案的路徑。
DELIMITER ',' 表示使用逗號作為欄位分隔符號。
CSV HEADER 選項表示檔案的第一行包含欄位名稱，應被忽略。

練習題

假設你的公司需要追蹤每天司機的行駛里程到十分之一英里，且假設沒有司機一天會行駛超過999英里，請問適合用什麼資料型別來儲存里程資料？為什麼？
在儲存司機資訊的資料表中，司機的名字和姓氏適合用什麼資料型別？為什麼將名字和姓氏分成兩個欄位儲存是一個好主意？
假設你有一個文字欄位，其中包含格式化為日期的字串。其中一個字串寫作 '4//2021'。當你嘗試將這個字串轉換為時間戳記（timestamp）資料型別時，會發生什麼事？

使用逗號分隔值（CSV）檔案匯入資料

逗號分隔值（CSV）是一種常見的檔案型別，用於儲存和交換資料。CSV 和逗號分隔是可互換的術語。下面是一個典型的 CSV 資料列範例： John,Doe,123 Main St.,Hyde Park,NY,845-555-1212

處理標頭列

在分隔文字檔案中，經常會遇到標頭列。標頭列是檔案頂部的單一行，列出了每個資料欄位的名稱。當資料從資料函式庫或試算表匯出時，通常會包含標頭列。下面是一個範例： FIRSTNAME,LASTNAME,STREET,CITY,STATE,PHONE John,Doe,123 Main St.,Hyde Park,NY,845-555-1212

標頭列有多個用途。首先，標頭列中的值可以識別每個欄位中的資料，這在解讀檔案內容時非常有用。其次，一些資料函式倉管理系統（雖然 PostgreSQL 不支援）使用標頭列將分隔檔案中的欄位對應到匯入表格中的正確欄位。PostgreSQL 不使用標頭列，因此我們在匯入資料時會使用 COPY 命令的 HEADER 選項來排除它。

引述包含分隔符的欄位

使用逗號作為欄位分隔符可能會導致一個問題：如果欄位中的值包含逗號。例如，有些人會將公寓號碼與街道地址合併，如 “123 Main St., Apartment 200”。除非分隔系統能夠處理額外的逗號，否則在匯入過程中，該行將被視為具有額外的欄位，並導致匯入失敗。

為瞭解決這個問題，分隔檔案使用一個任意字元（稱為文字限定符）來包圍包含分隔符的欄位。這樣可以讓系統忽略該分隔符，並將文字限定符之間的內容視為單一欄位。在逗號分隔的檔案中，通常使用雙引號作為文字限定符。下面是範例資料再次出現，但街道名稱欄位被雙引號包圍： FIRSTNAME,LASTNAME,STREET,CITY,STATE,PHONE John,Doe,“123 Main St., Apartment 200”,Hyde Park,NY,845-555-1212

在匯入過程中，資料函式庫會識別雙引號內的內容為單一欄位，無論是否包含分隔符。PostgreSQL 在匯入 CSV 檔案時，預設會忽略雙引號內的分隔符，但如果需要，也可以指定不同的文字限定符。

程式碼範例：使用 COPY 命令匯入資料

COPY table_name
FROM 'C:\YourDirectory\your_file.csv'
WITH (FORMAT CSV, HEADER);

內容解密：

COPY table_name：將資料複製到名為 table_name 的表格中，該表格必須已經存在於資料函式庫中。
FROM 'C:\YourDirectory\your_file.csv'：指定來源檔案的完整路徑，並將路徑用單引號括起來。路徑的格式取決於作業系統。
WITH (FORMAT CSV, HEADER)：指定匯入選項。FORMAT CSV 表示檔案格式為 CSV，而 HEADER 表示來源檔案具有標頭列，應在匯入時排除。

COPY 命令選項

輸入和輸出檔案格式：使用 FORMAT format_name 選項指定讀取或寫入的檔案型別。格式名稱可以是 CSV、TEXT 或 BINARY。
標頭列的存在：在匯入時，使用 HEADER 指定來源檔案具有標頭列，應排除在匯入之外。在匯出時，使用 HEADER 將欄位名稱作為標頭列包含在輸出檔案中。
分隔符：使用 DELIMITER 'character' 選項指定匯入或匯出檔案中使用的分隔符。分隔符必須是單一字元，且不能是回車符。

匯入美國縣級人口普查資料

在上一節中，我們瞭解到 CSV 檔案中的逗號如果出現在單一欄位值中，將會干擾匯入程式，除非該欄位值被特定的文字限定符號包圍，告訴資料函式庫將該值視為一個欄位。預設情況下，PostgreSQL 使用雙引號作為文字限定符號，但如果要匯入的 CSV 檔案使用不同的文字限定符號，可以使用 QUOTE 'quote_character' 選項指定。

現在，我們已經對分隔檔案有了更好的理解，接下來將實際匯入一個範例資料集。

匯入描述美國縣份的人口普查資料

這次匯入練習所使用的資料集比第 2 章中建立的 teachers 表格大得多。它包含了美國每個縣份的人口普查估計資料，共有 3,142 行和 16 個欄位。（美國的人口普查縣份包括一些具有不同名稱的地理區域：路易斯安那州的堂區，阿拉斯加州的自治區和普查區，以及一些獨立市，特別是在維吉尼亞州。）

為了理解這些資料，有必要對美國人口普查局（US Census Bureau）有所瞭解。該局是一個聯邦機構，負責追蹤國家的統計資料。它的最著名專案是每 10 年進行一次的人口普查，最近一次是在 2020 年。該資料用於確定來自每個州的 435 名成員在美國眾議院中的代表人數。近幾十年來，德克薩斯州和佛羅裡達州等成長較快的州獲得了更多的席位，而紐約州和俄亥俄州等成長較慢的州則失去了席位。

我們將要使用的資料是人口普查局每年的人口估計。這些估計以最近一次的 10 年人口普查為基礎，並考慮出生、死亡以及國內外遷移等因素，從而得出每年全國、州、縣和其他地理區域的人口估計。雖然沒有每年進行實際的人口統計，但這是取得美國各地人口更新測量的最佳方法。

對於這次練習，我從 2019 年美國人口普查縣級人口估計中選取了部分欄位（以及一些來自人口普查地理資料的描述性欄位），並將其編譯成一個名為 us_counties_pop_est_2019.csv 的檔案。如果您按照第 1 章中的指示下載了程式碼和資料，則應該已經在您的電腦上擁有這個檔案。

建立 `us_counties_pop_est_2019` 表格

清單 5-2 中的程式碼顯示了建立表格的 SQL 指令碼。在 pgAdmin 中點選第 2 章中建立的 analysis 資料函式庫。從 pgAdmin 的選單列中選擇「Tools」▶「Query Tool」。您可以將程式碼輸入到工具中，也可以從 GitHub 下載的檔案中複製並貼上。完成後，執行該指令碼。

CREATE TABLE us_counties_pop_est_2019 (
    state_fips text,
    county_fips text,
    region smallint,
    state_name text,
    county_name text,
    area_land bigint,
    area_water bigint,
    internal_point_lat numeric(10,7),
    internal_point_lon numeric(10,7),
    pop_est_2018 integer,
    pop_est_2019 integer,
    births_2019 integer,
    deaths_2019 integer,
    international_migr_2019 integer,
    domestic_migr_2019 integer,
    residual_2019 integer,
    CONSTRAINT counties_2019_key PRIMARY KEY (state_fips, county_fips)
);

清單 5-2：用於建立縣級人口普查估計表格的 CREATE TABLE 陳述式

回到 pgAdmin 的主視窗，在物件瀏覽器中右鍵點選並重新整理 analysis 資料函式庫。選擇「Schemas」▶「public」▶「Tables」以檢視新建立的表格。雖然目前該表格是空的，但您可以執行一個基本的 SELECT 查詢來檢視其結構：

SELECT * FROM us_counties_pop_est_2019;

執行該查詢後，您將在 pgAdmin 的「Data Output」面板中看到您建立的表格中的欄位。目前尚未有任何資料列被匯入。

瞭解人口普查欄位與資料型別

在匯入 CSV 檔案到表格之前，讓我們逐一檢視清單 5-2 中的幾個欄位和我所選擇的資料型別。我的參考依據是兩個官方的人口普查資料字典：一個用於估計資料，另一個用於包含地理欄位的十年一度的人口普查資料。我在表格定義中為某些欄位賦予了更易讀的名稱。在可能的情況下，依賴資料字典是一個良好的實踐，因為它可以幫助您避免錯誤組態欄位或潛在地遺失資料。如果有相關的資料字典，請務必查詢或進行線上搜尋（如果資料是公開的）。

內容解密：

state_fips 和 county_fips：這兩個欄位分別代表州和縣的 FIPS 程式碼，用於唯一標識每個縣。它們被定義為 text 型別，因為 FIPS 程式碼通常以字串形式表示。
region：代表所屬區域，被定義為 smallint，因為區域編號通常是整數，且範圍不大，使用 smallint 可以節省儲存空間。
state_name 和 county_name：分別代表州名和縣名，被定義為 text，以便儲存名稱。
area_land 和 area_water：分別代表陸地面積和水域面積，以平方單位計算，被定義為 bigint 以容納大數值。
internal_point_lat 和 internal_point_lon：代表縣內某一點的經緯度，被定義為 numeric(10,7) 以提供精確的小數經緯度座標。
pop_est_2018 和 pop_est_2019：分別代表 2018 年和 2019 年的人口估計，被定義為 integer 以儲存整數。
主鍵約束 counties_2019_key 被設定為 (state_fips, county_fips)，確保每個縣份由其州和縣的 FIPS 程式碼唯一標識。

日期與時間的處理

日期與時間的資料型別

日期與時間資料型別一覽

詳細說明

使用範例

程式碼解析

結果分析

資料型別轉換與應用

日期與時間的處理

使用interval進行日期計算

內容解密：

JSON與JSONB的應用

JSON範例

內容解密：

其他資料型別的應用

使用CAST()進行資料型別轉換

CAST()範例

內容解密：

資料匯入與匯出技術解析

使用 COPY 命令進行資料匯入

資料匯入步驟

使用 COPY 命令進行資料匯出

處理分隔文字檔案

分隔文字檔案的操作

其他資料函式倉管理系統的批次匯入功能

實務應用與考量

程式碼範例

內容解密：

練習題

使用逗號分隔值（CSV）檔案匯入資料

處理標頭列

引述包含分隔符的欄位

程式碼範例：使用 COPY 命令匯入資料

內容解密：

COPY 命令選項

匯入美國縣級人口普查資料

匯入描述美國縣份的人口普查資料

建立 us_counties_pop_est_2019 表格

清單 5-2：用於建立縣級人口普查估計表格的 CREATE TABLE 陳述式

瞭解人口普查欄位與資料型別

內容解密：

建立 `us_counties_pop_est_2019` 表格