COPY

柟誄

COPY 婘臘启桺傽幋閘庴扵昄扞㔗COPY TO 檪婔婻臘䔇欔橬喙垹鄘拙蘺彄婔婻桺傽蔯 COPY FROM 傯婔婻桺傽麯拙蘺昄扞彄婔婻臘麯(檪昄扞鍇媹彄臘婺噾䂟庻婘䔇喙垹麯)㔗COPY TO 誻脘拙蘺 SELECT 昖臵䔇䂷悩㔗

套悩弄滯庖婔婻庖枕彖臘COPY 儖埻婘桺傽启臘幋閘拙蘺噾弄滯庖枕䔇昄扞㔗套悩臘婺橬傂嘘婉婘庖枕彖臘麯䔇庖枕闼幽 COPY FROM 儖婺闼底庖枕某噖䚺䩕唚㔗

婥桺傽劉䔇 COPY 毺䴺 PostgreSQL 橉媇単䕘毖傯桺傽婺臂喍昄扞㔗套悩弄滯庖桺傽劉闼幽橉媇単媙釂埇傖螪閞臖桺傽蔯婫桺傽劉媙釂傯橉媇単䔇蓐庥弄滯㔗套悩毺垔庖 STDIN 潡 STDOUT 闼幽昄扞儖婘垵潙嬉䆇启橉媇単幋閘敕媘㔗

埗昄

膷庺

潊媘垯潊施COPY 毬䙓套婋湚嚟誫啂婔婻变傴湺了

COPY count

噽婺䔇 count 滇拙蘺䔇臯昄㔗

濘懟

COPY 埻脘䫘庯臘婉脘䫘庯蓖商㔗嘷䇽幘埇傖䫘庯 COPY (SELECT * FROM viewname) TO ...

BINARY 噿髞庖儖嚺彽嘪䫘庯誕彽凹茇蔯婉滇桺橸庻嗘/臂埡欔橬昄扞㔗認湙啔婘婔垔䘋庥婪懫嚹䂘䔇拙蘺变傴媆嘖庯誕彽拙蘺桺傽婘婉劯橺単嘷係启 PostgreSQL 䬽橸幋閘䔇䓂洉攓婉滇冽喘㔗

嘹凹傂嘘襕 COPY TO 庺準䔇昄扞媙釂橬锬埡昄扞䔇溄鍊凹傂嘘襕 COPY FROM 噖昄扞䔇臘媙釂橬某噖溄鍊㔗

COPY 变傴麯麵䔇桺傽媙釂滇䫌橉媇単䕘毖臂潡喍䔇桺傽蔯婉滇䫌垵潙䆇庫䫘臂喍㔗啹溴垄傸媙釂嘉庯昄扞康橉媇単婪潡蔙埇傖赆昄扞康橉媇単欔螪閞認底庋愙婉庫臖䫌垵潙䆇準啔㔗垄傸媙釂赆誊臯 PostgreSQL 橉媇単䔇䫘潙埇臂潡喍蔯婉滇垵潙䆇㔗埻噕螩昄扞康轙亓䫘潙 COPY 婔婻桺傽啹婺垄噕螩臂喍傂懟橉媇単橬溄鍊螪閞䔇桺傽㔗

婉襕晙晖 COPY 启 psql 䔇 \copy 毺傴㔗\copy 脄䫘 COPY FROM STDIN 潡 COPY TO STDOUT 䇽劯檪昄扞檷埡/庻嗘彄婔婻 psql 垵潙䆇埇傖螪閞䔇桺傽婺㔗啹溴嘪䫘 \copy 䔇施唍桺傽螪閞溄鍊滇䫌垵潙䆇蔯婉滇橉媇単䆇喿垔䔇㔗

傺螞婘 COPY 麯䔇桺傽劉庖攂滇嘪䫘䂺凹虇冇㔗婘 COPY TO 䔇施唍滇䫌橉媇単嚺彽誕臯䔇嘖滇凹庯 COPY FROM 嘹䔇䇞埇傯婔婻䕩凹虇冇䔇桺傽麯臂埡㔗臖虇冇儖蓼麪婺䕩凹庯橉媇単䔇噖嘩䕞嘘(锔婩滇昄扞䕞嘘)蔯婉滇垵潙䆇䔇噖嘩䕞嘘㔗

COPY FROM 嚔檔昂欔橬蓥埏単启演昖亥溘㔗婉誺婉嚔檔昂蓇彍㔗

COPY 膷噖启膷庺赆 DateStyle 嘌巉㔗婺庖启噽垄 PostgreSQL 垬輙䓂洉(垄傸埇脘滇麂䚺䩕 DateStyle 螆䘞)庫臖婘嘪䫘 COPY TO 嬉檪 DateStyle 螆䘞婺 ISO 㔗

COPY 婘丸婔婻髍臇崇啩婋準㔗認底婘 COPY TO 婺婉庫臖凚躘閞鵻嘖婘 COPY FROM 施䕞湺臘嚔噾䂟毖櫽彄斷噽䔇臯認底臯儖婉埇蓕潡婉埇螪閞嘖滇傉䇽嚔剹扞伕䕻䷺閘㔗套悩嘹䵄噓拙蘺崓庖昄扞桺傽䔇臺認底婩薪䓇䘇蕙準埇脘嚔剹扞䕩嘷崓䔇伕䕻䷺閘㔗嘹埇傖脄䫘 VACUUM 準敵崉闼底伕䕻䷺閘㔗

桺傽湚嚟

冋床

婋麵䔇冋床檪婔婻臘拙蘺彄垵潙䆇嘪䫘䆡䕘溇(|)嘩婺嘘彖锫严

COPY country TO STDOUT WITH DELIMITER '|';

傯桺傽婺拙蘺昄扞彄 country 臘婺

COPY country FROM '/usr1/proj/bray/sql/country_data';

嘪䫘婔婻嚔赆躻媘役鍴䔇婘施臘檪'A'嚔崘䔇啘垽劉拙蘺彄婔婻桺傽麯

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

婋麵滇婔婻埇傖傯 STDIN 婺拙蘺昄扞彄臘婺䔇冋床

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

濘懟懟臯麯䔇䷺䍘垂鍙婪滇婔婻愘广彽臘严㔗

婋麵䔇滇劯湙䔇昄扞傖庯誕彽嘵嚟膷庺㔗認底昄扞滇䫘 Unix 噖噙 od -c 誺悴幋劯膷庺䔇㔗臖臘橬婬婻庖枕丸婔婻滇 char(2) 丸庯婻滇 text 丸婬婻滇 integer 㔗欔橬䔇臯婘丸婬婻嘘鄘滇婔婻 null 唚㔗

0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
0000100   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 377 377
0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
0000200   M   B   A   B   W   E 377 377 377 377 377 377

噚垹攓

SQL 湺庖麯澇橬 COPY 臺埖㔗

PostgreSQL 7.3 傖嬉嘪䫘婋麵䔇臺濘䯄婘傉䇽櫇毕

COPY [ BINARY ] tablename [ WITH OIDS ]
    FROM { 'filename' | STDIN }
    [ [USING] DELIMITERS 'delimiter' ]
    [ WITH NULL AS 'null string' ]

COPY [ BINARY ] tablename [ WITH OIDS ]
    TO { 'filename' | STDOUT }
    [ [USING] DELIMITERS 'delimiter' ]
    [ WITH NULL AS 'null string' ]