請耐心等待,因為這是我的第一篇文章。
我正在嘗試在PostgreSQL-9.2中運行COPY命令,將.txt文件中的製表符分隔表添加到PostgreSQL數據庫,例如:
COPY raw_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER ' ');
我已經使用SQL命令在數據庫中創建了一個名為“raw_data”的空表:
CREATE TABLE raw_data ();
嘗試運行COPY
命令時,我不斷收到以下錯誤消息:
ERROR: extra data after last expected column
CONTEXT: COPY raw_data, line 1: " 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 ..."
(這裡的數字應該是列標題)
我不確定它是否因為我在創建db表時沒有指定表列但是我試圖避免手動輸入800或列。
對於如何解決這個問題,有任何的建議嗎?
以下是.txt文件的示例:
1 2 3 4 5 6 7 8 9
binary1 1 1 0 1 1 1 1 1 1
binary2 1 0 0 1 0 1 1 0 0
binary3 1 0 1 1 1 0 0 1 0
binary4 1 1 1 1 0 1 0 1 0
空表不會。您需要與輸入數據的結構相匹配的表。就像是:
CREATE TABLE raw_data (
col1 int
, col2 int
...
);
您無需將tab
聲明為DELIMITER
因為這是默認設置:
COPY raw_data FROM '/home/Projects/TestData/raw_data.txt';
你說800列?這麼多列通常表示您的設計有問題。無論如何,有一些方法可以使CREATE TABLE
腳本半自動化。
假設簡化的原始數據
1 2 3 4 -- first row contains "column names"
1 1 0 1 -- tab separated
1 0 0 1
1 0 1 1
定義一個不同的DELIMITER
(一個根本不在導入數據中出現的DELIMITER
),然後使用單個text
列導入到臨時登台表中:
CREATE TEMP TABLE tmp_data (raw text);
COPY tmp_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER '§');
此查詢創建CREATE TABLE
腳本:
SELECT 'CREATE TABLE tbl (col' || replace (raw, E'\t', ' bool, col') || ' bool)'
FROM (SELECT raw FROM tmp_data LIMIT 1) t;
更通用,更安全的查詢:
SELECT 'CREATE TABLE tbl('
|| string_agg(quote_ident('col' || col), ' bool, ' ORDER BY ord)
|| ' bool);'
FROM (SELECT raw FROM tmp_data LIMIT 1) t
, unnest(string_to_array(t.raw, E'\t')) WITH ORDINALITY c(col, ord);
返回值:
CREATE TABLE tbl (col1 bool, col2 bool, col3 bool, col4 bool);
驗證有效性後執行-如果您信任結果,則動態執行:
DO
$$BEGIN
EXECUTE (
SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)'
FROM (SELECT raw FROM tmp_data LIMIT 1) t
);
END$$;
然後使用以下查詢INSERT
數據:
INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
raw
, '1', 't')
, '0', 'f')
, E'\t', ',')
|| ')')::tbl).*
FROM (SELECT raw FROM tmp_data OFFSET 1) t;
或更簡單的與translate()
:
INSERT INTO tbl
SELECT (('(' || translate(raw, E'10\t', 'tf,') || ')')::tbl).*
FROM (SELECT raw FROM tmp_data OFFSET 1) t;
該字符串將轉換為行文字,轉換為新創建的表行類型,並使用(row).*
分解。
全做完了。
您可以將所有這些內容放入plpgsql函數中,但需要防止SQL注入。 (SO上有許多相關的解決方案。請嘗試搜索。
您可以直接從copy命令創建表,查看COPY中的HEADER選項,如:COPY FROM'/path/to/csv/SourceCSVFile.csv'DELIMITERS','CSV HEADER