前言

最近有需要對數(shù)據(jù)進行遷移的需求，由于postgres性能的關(guān)系，單表3000W的數(shù)據(jù)量查詢起來有一些慢，需要對大表進行切割，拆成若干個子表，涉及到原有數(shù)據(jù)要遷移到子表的需求。起初的想法是使用存儲過程，使用select insert方式進行，但是數(shù)據(jù)量大的時候耗時有點久，于是需要想別的辦法，就發(fā)現(xiàn)了postgres本身支持的copy命令。

copy命令

COPY在PostgreSQL表和文件之間交換數(shù)據(jù)。 COPY TO把一個表的所有內(nèi)容都拷貝到一個文件，而COPY FROM從一個文件里拷貝數(shù)據(jù)到一個表里(把數(shù)據(jù)附加到表中已經(jīng)存在的內(nèi)容里)。 COPY TO還能拷貝SELECT查詢的結(jié)果。

如果聲明了一個字段列表，COPY將只在文件和表之間拷貝已聲明字段的數(shù)據(jù)。 如果表中有任何不在字段列表里的字段，那么COPY FROM將為那些字段插入缺省值。

帶文件名的COPY指示PostgreSQL服務(wù)器直接從文件中讀寫數(shù)據(jù)。 如果聲明了文件名，那么服務(wù)器必須可以訪問該文件，而且文件名必須從服務(wù)器的角度聲明。 如果使用了PROGRAM選項，則服務(wù)器會從指定的這個程序進行輸入或是寫入該程序作為輸出。 如果使用了STDIN 或STDOUT選項，那么數(shù)據(jù)將通過客戶端和服務(wù)器之間的連接來傳輸。

常用參數(shù)說明：

table_name
現(xiàn)存表的名字(可以有模式修飾)

column_name
可選的待拷貝字段列表。如果沒有聲明字段列表，那么將使用所有字段

query
一個必須用圓括弧包圍的SELECT或VALUES命令，其結(jié)果將被拷貝

filename
輸入或輸出文件的路徑名。輸入文件名可以是絕對或是相對的路徑，但輸出文件名必須是絕對路徑。 Windows用戶可能需要使用E”字符串和雙反斜線作為路徑名稱

PROGRAM
需執(zhí)行的程序名。在COPY FROM命令中，輸入是從程序的標(biāo)準(zhǔn)輸出中讀取，而在COPY TO中，命令的輸出會作為程序的標(biāo)準(zhǔn)輸入。

注意，程序一般是在命令行界面下執(zhí)行，當(dāng)用戶需要傳遞一些變量給程序時，如果這些變量的來源不是可靠的，用戶必須小心過濾處理那些對命令行界面來說是有特殊意義的字符。 基于安全的原因，最好是使用固定的命令字符串，或者至少是應(yīng)避免直接使用用戶輸入（應(yīng)先過濾特殊字符）

STDOUT
聲明輸入將寫入客戶端應(yīng)用

FORMAT
選擇被讀或者寫的數(shù)據(jù)格式：text、csv（逗號分隔值），或者binary。 默認(rèn)是text

導(dǎo)出CSV：

命令：

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

copy to的導(dǎo)出速度非常之快，經(jīng)測試10W的數(shù)據(jù)量只需要3秒左右的時間

示例：

COPY user TO '/tmp/data/test.csv' WITH csv;

也可以導(dǎo)出指定的屬性：

COPY user(name,password) TO '/tmp/data/test.csv' WITH csv;

也可以使用select 語句：

COPY (select * from user) TO '/tmp/data/test.csv' WITH csv;

也可以指定要導(dǎo)出哪些字段：

COPY (select name,age from user) TO '/tmp/data/test.csv' WITH csv header;

導(dǎo)入CSV：

命令：

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

示例：

COPY user_1 FROM '/tmp/data/test.csv' WITH csv;

導(dǎo)入命令基本與導(dǎo)出一樣，只是將TO 改為 FROM

如果導(dǎo)出的時候，指定了header屬性，那么在導(dǎo)入的時候，也需要指定：

COPY user_1(name, age) FROM '/tmp/data/test.csv' WITH csv header;

注意事項：

第一點：
copy命令必須在plsql命令行執(zhí)行，執(zhí)行用戶必須為superuser，否則會提示：

ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

普通用戶進行執(zhí)行，需要在copy前面加入 “\”，即 \copy即可

第二點：
如果導(dǎo)出的字段，有integer[]類型，直接導(dǎo)出，再導(dǎo)入的話，會有問題，解決辦法是需要在導(dǎo)出的時候，進行處理：

\COPY ( select coalesce(integer_array, '{}')::integer[] as integer_array from table ) TO '/tmp/data.csv' with csv header;

更多詳細(xì)信息，可以查看官方文檔：

http://www.postgres.cn/docs/9.3/sql-copy.html

到此這篇關(guān)于Postgres copy命令導(dǎo)入導(dǎo)出數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Postgres copy導(dǎo)入導(dǎo)出數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論