快捷導(dǎo)航

Postgres copy命令導(dǎo)入導(dǎo)出數(shù)據(jù)的操作方法

更新時(shí)間：2024年08月08日 16:59:02 作者：wtopps

最近有需要對(duì)數(shù)據(jù)進(jìn)行遷移的需求,由于postgres性能的關(guān)系,單表3000W的數(shù)據(jù)量查詢起來有一些慢,需要對(duì)大表進(jìn)行切割,拆成若干個(gè)子表,涉及到原有數(shù)據(jù)要遷移到子表的需求,這篇文章主要介紹了Postgres copy命令導(dǎo)入導(dǎo)出數(shù)據(jù)的操作方法,需要的朋友可以參考下

前言

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

copy命令

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

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

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

常用參數(shù)說明：

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

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

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

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

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

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

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

FORMAT
選擇被讀或者寫的數(shù)據(jù)格式：text、csv（逗號(hào)分隔值），或者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)測(cè)試10W的數(shù)據(jù)量只需要3秒左右的時(shí)間

示例：

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)出的時(shí)候，指定了header屬性，那么在導(dǎo)入的時(shí)候，也需要指定：

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

注意事項(xiàng)：

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

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.

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

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

\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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: