在PostgreSQL中，利用copy命令快速導入導出數據到平面文件CSV和TXT

1，利用copy to 命令導出表數據到CSV，copy from 命令從CSV文件中導入數據到表，可以自定義，換行符。

create table if not exists test_copy
(
	c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	c2 int,
	c3 varchar(50),
	c4 timestamp
);

create table if not exists test_copy2
(
	c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	c2 int,
	c3 varchar(50),
	c4 timestamp
);

insert into test_copy(c2,c3,c4)values(100,'abc',now());
insert into test_copy(c2,c3,c4)values(200,'def',now());
insert into test_copy(c2,c3,c4)values(null,'xxx',now());
insert into test_copy(c2,c3,c4)values(300, null,now());
insert into test_copy(c2,c3,c4)values(500, 'yyy',null);
insert into test_copy(c2,c3,c4)values(600, 'zzz',now());

select * from test_copy;

c1|c2 |c3 |c4                     |
--+---+---+-----------------------+
 1|100|abc|2025-08-04 15:51:58.388|
 2|200|def|2025-08-04 15:52:11.673|
 3|   |xxx|2025-08-04 15:52:29.600|
 4|300|   |2025-08-04 15:52:45.656|
 5|500|yyy|                       |
 6|600|zzz|2025-08-04 15:53:33.747|

 
-- 導出，不指定分隔符的時，默認分隔符是","
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy1.csv' WITH (FORMAT csv, HEADER true);

-- 導出，不指定分隔符的時，自定義分隔符
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy2.csv' WITH (FORMAT csv, HEADER true, DELIMITER '|');


-- 導入,不指定分隔符的時，默認分隔符是","
COPY test_copy2 FROM '/usr/local/pgsql16/backup/test_copy1.csv' WITH (FORMAT csv, HEADER true);


-- 導入,不指定分隔符的時
COPY test_copy2 FROM '/usr/local/pgsql16/backup/test_copy2.csv' WITH (FORMAT csv, HEADER true, DELIMITER '|');


select * from test_copy2;
c1|c2 |c3 |c4                     |
--+---+---+-----------------------+
 1|100|abc|2025-08-04 15:51:58.388|
 2|200|def|2025-08-04 15:52:11.673|
 3|   |xxx|2025-08-04 15:52:29.600|
 4|300|   |2025-08-04 15:52:45.656|
 5|500|yyy|                       |
 6|600|zzz|2025-08-04 15:53:33.747|

 

2，利用copy to 命令導出表數據到txt，copy from 命令從txt文件中導入數據到表，可以自定義，換行符，分隔符等信息。

-- 導出到txt，指定 NULL為'\N'
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy1.txt' WITH (
    FORMAT text,
    DELIMITER E'\t',   	-- 換行符：Tab分隔符
    NULL '\N',			-- null值處理
    ENCODING 'UTF8'
);

 -- 導出到txt，指定 NULL為''，不建議
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy2.txt' WITH (
    FORMAT text,
    DELIMITER E'\t',	-- 換行符：Tab分隔符
    NULL '',			-- null值處理
    ENCODING 'UTF8'
);



create table if not exists test_copy3
(
	c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	c2 int,
	c3 varchar(50),
	c4 timestamp
);


create table if not exists test_copy4
(
	c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	c2 int,
	c3 varchar(50),
	c4 timestamp
);



--導入txt
COPY test_copy3 from '/usr/local/pgsql16/backup/test_copy1.txt' WITH (
    FORMAT text,
    DELIMITER E'\t',   	-- 換行符：Tab分隔符
    NULL '\N',			-- null值處理
    ENCODING 'UTF8'
);


select * from test_copy3;
c1|c2 |c3 |c4                     |
--+---+---+-----------------------+
 1|100|abc|2025-08-04 15:51:58.388|
 2|200|def|2025-08-04 15:52:11.673|
 3|   |xxx|2025-08-04 15:52:29.600|
 4|300|   |2025-08-04 15:52:45.656|
 5|500|yyy|                       |
 6|600|zzz|2025-08-04 15:53:33.747|
 

COPY test_copy4 from '/usr/local/pgsql16/backup/test_copy2.txt' WITH (
    FORMAT text,
    DELIMITER E'\t',   	-- 換行符：Tab分隔符
    NULL '',			-- null值處理
    ENCODING 'UTF8'
);

select * from test_copy4;

c1|c2 |c3 |c4                     |
--+---+---+-----------------------+
 1|100|abc|2025-08-04 15:51:58.388|
 2|200|def|2025-08-04 15:52:11.673|
 3|   |xxx|2025-08-04 15:52:29.600|
 4|300|   |2025-08-04 15:52:45.656|
 5|500|yyy|                       |
 6|600|zzz|2025-08-04 15:53:33.747|

3，用copy命令，將PostgreSQL的csv日誌快速導入到表中

-- refer:https://www.postgresql.org/docs/16/file-fdw.html
CREATE TABLE pg_log_csv 
(
	  log_time timestamp(3) with time zone,
	  user_name text,
	  database_name text,
	  process_id integer,
	  connection_from text,
	  session_id text,
	  session_line_num bigint,
	  command_tag text,
	  session_start_time timestamp with time zone,
	  virtual_transaction_id text,
	  transaction_id bigint,
	  error_severity text,
	  sql_state_code text,
	  message text,
	  detail text,
	  hint text,
	  internal_query text,
	  internal_query_pos integer,
	  context text,
	  query text,
	  query_pos integer,
	  location text,
	  application_name text,
	  backend_type text,
	  leader_pid integer,
	  query_id bigint
) 

--或者直接使用copy命令，將csv日誌導入表中做分析
DO $$
DECLARE sql_text text;
BEGIN
    sql_text :=  
	    'COPY public.pg_log_csv FROM ''/usr/local/pgsql16/pg9300/data/log/postgresql-' 
		|| to_char(now(),'YYYY-MM-DD_')  
		|| '000000' 
		||'.csv'' DELIMITER '','' CSV HEADER';

	RAISE NOTICE '%', sql_text;

	EXECUTE sql_text;
END;
$$ LANGUAGE plpgsql;