digoal
2023-03-15
PostgreSQL , PolarDB , copy from , file_fdw , default
有些文件中的某些数据不想导入时设置为null, 想要用表字段提供的default值来替代, 例如时间, 导入到数据库时, 设置为当时的时间. 则可以使用copy from default来达到目的.
Add a DEFAULT option to COPY FROM
This allows for a string which if an input field matches causes the
column's default value to be inserted. The advantage of this is that
the default can be inserted in some rows and not others, for which
non-default data is available.
The file_fdw extension is also modified to take allow use of this
option.
+ <varlistentry>
+ <term><literal>DEFAULT</literal></term>
+ <listitem>
+ <para>
+ Specifies the string that represents a default value. Each time the string
+ is found in the input file, the default value of the corresponding column
+ will be used.
+ This option is allowed only in <command>COPY FROM</command>, and only when
+ not using <literal>binary</literal> format.
+ </para>
+ </listitem>
+ </varlistentry>
当文件中包含copy from default指定的string, 则以表column定义的default代替.
例如一下有3个'\D', 当使用了copy from default '\D'时, 这三个值会被对应表定义的column default值替代.
1 1,value,2022-07-04
2 2,\D,2022-07-03
3 3,\D,\D
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+ id integer,
+ text_value text not null default 'test',
+ ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+ id | text_value | ts_value
+----+------------+--------------------------
+ 1 | value | Mon Jul 04 00:00:00 2022
+ 2 | test | Sun Jul 03 00:00:00 2022 // 这一行text_value 被设置为表设置的默认值.
+ 3 | test | Tue Jul 05 00:00:00 2022 // 这一行text_value, ts_value 被设置为表设置的默认值.
+(3 rows)
+
+DROP FOREIGN TABLE copy_default;
