PostgreSQL 15 preview - COPY FROM|file_fdw 支持 header match , 第一行作为列名匹配判断

作者

digoal

日期

2022-03-31

标签

PostgreSQL , copy from , file_fdw , header match , 列名匹配

PostgreSQL 15 copy from 支持 header match 语法, 匹配第一行的列名是否与目标表字段名一致.

避免原来简单的header跳过第一行, 但是字段、类型与目标表匹配但是实际上不是目标表的文本被误导入.

file_fdw, copy from都已支持header match选项.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=072132f04e55c1c3b0f1a582318da78de7334379

Add header matching mode to COPY FROM  
author  Peter Eisentraut <peter@eisentraut.org>   
Wed, 30 Mar 2022 06:56:58 +0000 (08:56 +0200)  
committer   Peter Eisentraut <peter@eisentraut.org>   
Wed, 30 Mar 2022 07:02:31 +0000 (09:02 +0200)  
commit  072132f04e55c1c3b0f1a582318da78de7334379  
tree    d855c0b1716968cd26966e34f41f77de0c0d0af6    tree  
parent  edcedcc2c7bb8390858bbccda9637318598f2473    commit | diff  
Add header matching mode to COPY FROM  
COPY FROM supports the HEADER option to silently discard the header  
line from a CSV or text file.  It is possible to load by mistake a  
file that matches the expected format, for example, if two text  
columns have been swapped, resulting in garbage in the database.  
This adds a new option value HEADER MATCH that checks the column names  
in the header line against the actual column names and errors out if  
they do not match.  
Author: Rémi Lapeyre <remi.lapeyre@lenstra.fr>  
Reviewed-by: Daniel Verite <daniel@manitou-mail.org>  
Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>  
Discussion: https://www.postgresql.org/message-id/flat/CAF1-J-0PtCWMeLtswwGV2M70U26n4g33gpe1rcKQqe6wVQDrFA@mail.gmail.com  

+      names from the table.  On input, the first line is discarded when this  
+      option is set to <literal>true</literal> (or equivalent Boolean value).  
+      If this option is set to <literal>match</literal>, the number and names  
+      of the columns in the header line must match the actual column names of  
+      the table, otherwise an error is raised.  

例子

+-- Test header matching feature  
+create table header_copytest (  
+   a int,  
+   b int,  
+   c text  
+);  
+copy header_copytest from stdin with (header wrong_choice);  
+ERROR:  header requires a Boolean value or "match"  
+copy header_copytest from stdin with (header match);  
+copy header_copytest from stdin with (header match);  
+ERROR:  column name mismatch in header line field 3: got null value ("\N"), expected "c"  
+CONTEXT:  COPY header_copytest, line 1: "a b   \N"  
+copy header_copytest from stdin with (header match);  
+ERROR:  wrong number of fields in header line: field count is 2, expected 3  
+CONTEXT:  COPY header_copytest, line 1: "a b"  
+copy header_copytest from stdin with (header match);  
+ERROR:  wrong number of fields in header line: field count is 4, expected 3  
+CONTEXT:  COPY header_copytest, line 1: "a b   c   d"  
+copy header_copytest from stdin with (header match);  
+ERROR:  column name mismatch in header line field 3: got "d", expected "c"  
+CONTEXT:  COPY header_copytest, line 1: "a b   d"  
+copy header_copytest from stdin with (header match, format csv);  
+drop table header_copytest;  
+CREATE FOREIGN TABLE header_match ("1" int, foo text) SERVER file_server  
+OPTIONS (format 'csv', filename :'filename', delimiter ',', header 'match');  
+SELECT * FROM header_match;  
+ 1 | foo   
+---+-----  
+ 1 | bar  
+(1 row)  
+  
+CREATE FOREIGN TABLE header_doesnt_match (a int, foo text) SERVER file_server  
+OPTIONS (format 'csv', filename :'filename', delimiter ',', header 'match');     

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.