Debian Codegraph @

doc/src/sgml/ref/copy.sgml

raw
1 <!--
2 doc/src/sgml/ref/copy.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-copy">
7 <indexterm zone="sql-copy">
8 <primary>COPY</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>COPY</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>COPY</refname>
19 <refpurpose>copy data between a file and a table</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 COPY <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
25 FROM { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDIN }
26 [ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
27 [ WHERE <replaceable class="parameter">condition</replaceable> ]
28
29 COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
30 TO { '<replaceable class="parameter">filename</replaceable>' | PROGRAM '<replaceable class="parameter">command</replaceable>' | STDOUT }
31 [ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
32
33 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
34
35 FORMAT <replaceable class="parameter">format_name</replaceable>
36 FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
37 DELIMITER '<replaceable class="parameter">delimiter_character</replaceable>'
38 NULL '<replaceable class="parameter">null_string</replaceable>'
39 DEFAULT '<replaceable class="parameter">default_string</replaceable>'
40 HEADER [ <replaceable class="parameter">boolean</replaceable> | MATCH ]
41 QUOTE '<replaceable class="parameter">quote_character</replaceable>'
42 ESCAPE '<replaceable class="parameter">escape_character</replaceable>'
43 FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
44 FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
45 FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
46 ON_ERROR <replaceable class="parameter">error_action</replaceable>
47 REJECT_LIMIT <replaceable class="parameter">maxerror</replaceable>
48 ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
49 LOG_VERBOSITY <replaceable class="parameter">verbosity</replaceable>
50 </synopsis>
51 </refsynopsisdiv>
52
53 <refsect1>
54 <title>Description</title>
55
56 <para>
57 <command>COPY</command> moves data between
58 <productname>PostgreSQL</productname> tables and standard file-system
59 files. <command>COPY TO</command> copies the contents of a table
60 <emphasis>to</emphasis> a file, while <command>COPY FROM</command> copies
61 data <emphasis>from</emphasis> a file to a table (appending the data to
62 whatever is in the table already). <command>COPY TO</command>
63 can also copy the results of a <command>SELECT</command> query.
64 </para>
65
66 <para>
67 If a column list is specified, <command>COPY TO</command> copies only
68 the data in the specified columns to the file. For <command>COPY
69 FROM</command>, each field in the file is inserted, in order, into the
70 specified column. Table columns not specified in the <command>COPY
71 FROM</command> column list will receive their default values.
72 </para>
73
74 <para>
75 <command>COPY</command> with a file name instructs the
76 <productname>PostgreSQL</productname> server to directly read from
77 or write to a file. The file must be accessible by the
78 <productname>PostgreSQL</productname> user (the user ID the server
79 runs as) and the name must be specified from the viewpoint of the
80 server. When <literal>PROGRAM</literal> is specified, the server
81 executes the given command and reads from the standard output of the
82 program, or writes to the standard input of the program. The command
83 must be specified from the viewpoint of the server, and be executable
84 by the <productname>PostgreSQL</productname> user. When
85 <literal>STDIN</literal> or <literal>STDOUT</literal> is
86 specified, data is transmitted via the connection between the
87 client and the server.
88 </para>
89
90 <para>
91 Each backend running <command>COPY</command> will report its progress
92 in the <structname>pg_stat_progress_copy</structname> view. See
93 <xref linkend="copy-progress-reporting"/> for details.
94 </para>
95
96 <para>
97 By default, <command>COPY</command> will fail if it encounters an error
98 during processing. For use cases where a best-effort attempt at loading
99 the entire file is desired, the <literal>ON_ERROR</literal> clause can
100 be used to specify some other behavior.
101 </para>
102 </refsect1>
103
104 <refsect1>
105 <title>Parameters</title>
106
107 <variablelist>
108 <varlistentry>
109 <term><replaceable class="parameter">table_name</replaceable></term>
110 <listitem>
111 <para>
112 The name (optionally schema-qualified) of an existing table.
113 </para>
114 </listitem>
115 </varlistentry>
116
117 <varlistentry>
118 <term><replaceable class="parameter">column_name</replaceable></term>
119 <listitem>
120 <para>
121 An optional list of columns to be copied. If no column list is
122 specified, all columns of the table except generated columns will be
123 copied.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><replaceable class="parameter">query</replaceable></term>
130 <listitem>
131 <para>
132 A <link linkend="sql-select"><command>SELECT</command></link>,
133 <link linkend="sql-values"><command>VALUES</command></link>,
134 <link linkend="sql-insert"><command>INSERT</command></link>,
135 <link linkend="sql-update"><command>UPDATE</command></link>,
136 <link linkend="sql-delete"><command>DELETE</command></link>, or
137 <link linkend="sql-merge"><command>MERGE</command></link> command
138 whose results are to be copied. Note that parentheses are required
139 around the query.
140 </para>
141 <para>
142 For <command>INSERT</command>, <command>UPDATE</command>,
143 <command>DELETE</command>, and <command>MERGE</command> queries a
144 <literal>RETURNING</literal> clause must be provided, and the target
145 relation must not have a conditional rule, nor an
146 <literal>ALSO</literal> rule, nor an <literal>INSTEAD</literal> rule
147 that expands to multiple statements.
148 </para>
149 </listitem>
150 </varlistentry>
151
152 <varlistentry>
153 <term><replaceable class="parameter">filename</replaceable></term>
154 <listitem>
155 <para>
156 The path name of the input or output file. An input file name can be
157 an absolute or relative path, but an output file name must be an absolute
158 path. Windows users might need to use an <literal>E''</literal> string and
159 double any backslashes used in the path name.
160 </para>
161 </listitem>
162 </varlistentry>
163
164 <varlistentry>
165 <term><literal>PROGRAM</literal></term>
166 <listitem>
167 <para>
168 A command to execute. In <command>COPY FROM</command>, the input is
169 read from standard output of the command, and in <command>COPY TO</command>,
170 the output is written to the standard input of the command.
171 </para>
172 <para>
173 Note that the command is invoked by the shell, so if you need to pass
174 any arguments that come from an untrusted source, you
175 must be careful to strip or escape any special characters that might
176 have a special meaning for the shell. For security reasons, it is best
177 to use a fixed command string, or at least avoid including any user input
178 in it.
179 </para>
180 </listitem>
181 </varlistentry>
182
183 <varlistentry>
184 <term><literal>STDIN</literal></term>
185 <listitem>
186 <para>
187 Specifies that input comes from the client application.
188 </para>
189 </listitem>
190 </varlistentry>
191
192 <varlistentry>
193 <term><literal>STDOUT</literal></term>
194 <listitem>
195 <para>
196 Specifies that output goes to the client application.
197 </para>
198 </listitem>
199 </varlistentry>
200
201 <varlistentry>
202 <term><replaceable class="parameter">boolean</replaceable></term>
203 <listitem>
204 <para>
205 Specifies whether the selected option should be turned on or off.
206 You can write <literal>TRUE</literal>, <literal>ON</literal>, or
207 <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
208 <literal>OFF</literal>, or <literal>0</literal> to disable it. The
209 <replaceable class="parameter">boolean</replaceable> value can also
210 be omitted, in which case <literal>TRUE</literal> is assumed.
211 </para>
212 </listitem>
213 </varlistentry>
214
215 <varlistentry>
216 <term><literal>FORMAT</literal></term>
217 <listitem>
218 <para>
219 Selects the data format to be read or written:
220 <literal>text</literal>,
221 <literal>csv</literal> (Comma Separated Values),
222 or <literal>binary</literal>.
223 The default is <literal>text</literal>.
224 See <xref linkend="sql-copy-file-formats"/> below for details.
225 </para>
226 </listitem>
227 </varlistentry>
228
229 <varlistentry>
230 <term><literal>FREEZE</literal></term>
231 <listitem>
232 <para>
233 Requests copying the data with rows already frozen, just as they
234 would be after running the <command>VACUUM FREEZE</command> command.
235 This is intended as a performance option for initial data loading.
236 Rows will be frozen only if the table being loaded has been created
237 or truncated in the current subtransaction, there are no cursors
238 open and there are no older snapshots held by this transaction. It is
239 currently not possible to perform a <command>COPY FREEZE</command> on
240 a partitioned table or foreign table.
241 This option is only allowed in <command>COPY FROM</command>.
242 </para>
243 <para>
244 Note that all other sessions will immediately be able to see the data
245 once it has been successfully loaded. This violates the normal rules
246 of MVCC visibility and users should be aware of the
247 potential problems this might cause.
248 </para>
249 </listitem>
250 </varlistentry>
251
252 <varlistentry>
253 <term><literal>DELIMITER</literal></term>
254 <listitem>
255 <para>
256 Specifies the character that separates columns within each row
257 (line) of the file. The default is a tab character in text format,
258 a comma in <literal>CSV</literal> format.
259 This must be a single one-byte character.
260 This option is not allowed when using <literal>binary</literal> format.
261 </para>
262 </listitem>
263 </varlistentry>
264
265 <varlistentry>
266 <term><literal>NULL</literal></term>
267 <listitem>
268 <para>
269 Specifies the string that represents a null value. The default is
270 <literal>\N</literal> (backslash-N) in text format, and an unquoted empty
271 string in <literal>CSV</literal> format. You might prefer an
272 empty string even in text format for cases where you don't want to
273 distinguish nulls from empty strings.
274 This option is not allowed when using <literal>binary</literal> format.
275 </para>
276
277 <note>
278 <para>
279 When using <command>COPY FROM</command>, any data item that matches
280 this string will be stored as a null value, so you should make
281 sure that you use the same string as you used with
282 <command>COPY TO</command>.
283 </para>
284 </note>
285
286 </listitem>
287 </varlistentry>
288
289 <varlistentry>
290 <term><literal>DEFAULT</literal></term>
291 <listitem>
292 <para>
293 Specifies the string that represents a default value. Each time the string
294 is found in the input file, the default value of the corresponding column
295 will be used.
296 This option is allowed only in <command>COPY FROM</command>, and only when
297 not using <literal>binary</literal> format.
298 </para>
299 </listitem>
300 </varlistentry>
301
302 <varlistentry>
303 <term><literal>HEADER</literal></term>
304 <listitem>
305 <para>
306 Specifies that the file contains a header line with the names of each
307 column in the file. On output, the first line contains the column
308 names from the table. On input, the first line is discarded when this
309 option is set to <literal>true</literal> (or equivalent Boolean value).
310 If this option is set to <literal>MATCH</literal>, the number and names
311 of the columns in the header line must match the actual column names of
312 the table, in order; otherwise an error is raised.
313 This option is not allowed when using <literal>binary</literal> format.
314 The <literal>MATCH</literal> option is only valid for <command>COPY
315 FROM</command> commands.
316 </para>
317 </listitem>
318 </varlistentry>
319
320 <varlistentry>
321 <term><literal>QUOTE</literal></term>
322 <listitem>
323 <para>
324 Specifies the quoting character to be used when a data value is quoted.
325 The default is double-quote.
326 This must be a single one-byte character.
327 This option is allowed only when using <literal>CSV</literal> format.
328 </para>
329 </listitem>
330 </varlistentry>
331
332 <varlistentry>
333 <term><literal>ESCAPE</literal></term>
334 <listitem>
335 <para>
336 Specifies the character that should appear before a
337 data character that matches the <literal>QUOTE</literal> value.
338 The default is the same as the <literal>QUOTE</literal> value (so that
339 the quoting character is doubled if it appears in the data).
340 This must be a single one-byte character.
341 This option is allowed only when using <literal>CSV</literal> format.
342 </para>
343 </listitem>
344 </varlistentry>
345
346 <varlistentry>
347 <term><literal>FORCE_QUOTE</literal></term>
348 <listitem>
349 <para>
350 Forces quoting to be
351 used for all non-<literal>NULL</literal> values in each specified column.
352 <literal>NULL</literal> output is never quoted. If <literal>*</literal> is specified,
353 non-<literal>NULL</literal> values will be quoted in all columns.
354 This option is allowed only in <command>COPY TO</command>, and only when
355 using <literal>CSV</literal> format.
356 </para>
357 </listitem>
358 </varlistentry>
359
360 <varlistentry>
361 <term><literal>FORCE_NOT_NULL</literal></term>
362 <listitem>
363 <para>
364 Do not match the specified columns' values against the null string.
365 In the default case where the null string is empty, this means that
366 empty values will be read as zero-length strings rather than nulls,
367 even when they are not quoted.
368 If <literal>*</literal> is specified, the option will be applied to all columns.
369 This option is allowed only in <command>COPY FROM</command>, and only when
370 using <literal>CSV</literal> format.
371 </para>
372 </listitem>
373 </varlistentry>
374
375 <varlistentry>
376 <term><literal>FORCE_NULL</literal></term>
377 <listitem>
378 <para>
379 Match the specified columns' values against the null string, even
380 if it has been quoted, and if a match is found set the value to
381 <literal>NULL</literal>. In the default case where the null string is empty,
382 this converts a quoted empty string into NULL.
383 If <literal>*</literal> is specified, the option will be applied to all columns.
384 This option is allowed only in <command>COPY FROM</command>, and only when
385 using <literal>CSV</literal> format.
386 </para>
387 </listitem>
388 </varlistentry>
389
390 <varlistentry>
391 <term><literal>ON_ERROR</literal></term>
392 <listitem>
393 <para>
394 Specifies how to behave when encountering an error converting a column's
395 input value into its data type.
396 An <replaceable class="parameter">error_action</replaceable> value of
397 <literal>stop</literal> means fail the command, while
398 <literal>ignore</literal> means discard the input row and continue with the next one.
399 The default is <literal>stop</literal>.
400 </para>
401 <para>
402 The <literal>ignore</literal> option is applicable only for <command>COPY FROM</command>
403 when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
404 </para>
405 <para>
406 A <literal>NOTICE</literal> message containing the ignored row count is
407 emitted at the end of the <command>COPY FROM</command> if at least one
408 row was discarded. When <literal>LOG_VERBOSITY</literal> option is set to
409 <literal>verbose</literal>, a <literal>NOTICE</literal> message
410 containing the line of the input file and the column name whose input
411 conversion has failed is emitted for each discarded row.
412 When it is set to <literal>silent</literal>, no message is emitted
413 regarding ignored rows.
414 </para>
415 </listitem>
416 </varlistentry>
417
418 <varlistentry>
419 <term><literal>REJECT_LIMIT</literal></term>
420 <listitem>
421 <para>
422 Specifies the maximum number of errors tolerated while converting a
423 column's input value to its data type, when <literal>ON_ERROR</literal> is
424 set to <literal>ignore</literal>.
425 If the input causes more errors than the specified value, the <command>COPY</command>
426 command fails, even with <literal>ON_ERROR</literal> set to <literal>ignore</literal>.
427 This clause must be used with <literal>ON_ERROR</literal>=<literal>ignore</literal>
428 and <replaceable class="parameter">maxerror</replaceable> must be positive <type>bigint</type>.
429 If not specified, <literal>ON_ERROR</literal>=<literal>ignore</literal>
430 allows an unlimited number of errors, meaning <command>COPY</command> will
431 skip all erroneous data.
432 </para>
433 </listitem>
434 </varlistentry>
435
436 <varlistentry>
437 <term><literal>ENCODING</literal></term>
438 <listitem>
439 <para>
440 Specifies that the file is encoded in the <replaceable
441 class="parameter">encoding_name</replaceable>. If this option is
442 omitted, the current client encoding is used. See the Notes below
443 for more details.
444 </para>
445 </listitem>
446 </varlistentry>
447
448 <varlistentry>
449 <term><literal>LOG_VERBOSITY</literal></term>
450 <listitem>
451 <para>
452 Specifies the amount of messages emitted by a <command>COPY</command>
453 command: <literal>default</literal>, <literal>verbose</literal>, or
454 <literal>silent</literal>.
455 If <literal>verbose</literal> is specified, additional messages are
456 emitted during processing.
457 <literal>silent</literal> suppresses both verbose and default messages.
458 </para>
459 <para>
460 This is currently used in <command>COPY FROM</command> command when
461 <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>.
462 </para>
463 </listitem>
464 </varlistentry>
465
466 <varlistentry>
467 <term><literal>WHERE</literal></term>
468 <listitem>
469 <para>
470 The optional <literal>WHERE</literal> clause has the general form
471 <synopsis>
472 WHERE <replaceable class="parameter">condition</replaceable>
473 </synopsis>
474 where <replaceable class="parameter">condition</replaceable> is
475 any expression that evaluates to a result of type
476 <type>boolean</type>. Any row that does not satisfy this
477 condition will not be inserted to the table. A row satisfies the
478 condition if it returns true when the actual row values are
479 substituted for any variable references.
480 </para>
481
482 <para>
483 Currently, subqueries and generated columns are not allowed in
484 <literal>WHERE</literal> expressions, and the evaluation does not see
485 any changes made by the <command>COPY</command> itself (this matters
486 when the expression contains calls to <literal>VOLATILE</literal>
487 functions).
488 </para>
489
490 </listitem>
491 </varlistentry>
492
493 </variablelist>
494 </refsect1>
495
496 <refsect1>
497 <title>Outputs</title>
498
499 <para>
500 On successful completion, a <command>COPY</command> command returns a command
501 tag of the form
502 <screen>
503 COPY <replaceable class="parameter">count</replaceable>
504 </screen>
505 The <replaceable class="parameter">count</replaceable> is the number
506 of rows copied.
507 </para>
508
509 <note>
510 <para>
511 <application>psql</application> will print this command tag only if the command
512 was not <literal>COPY ... TO STDOUT</literal>, or the
513 equivalent <application>psql</application> meta-command
514 <literal>\copy ... to stdout</literal>. This is to prevent confusing the
515 command tag with the data that was just printed.
516 </para>
517 </note>
518 </refsect1>
519
520 <refsect1>
521 <title>Notes</title>
522
523 <para>
524 <command>COPY TO</command> can be used with plain
525 tables and populated materialized views.
526 For example,
527 <literal>COPY <replaceable class="parameter">table</replaceable>
528 TO</literal> copies the same rows as
529 <literal>SELECT * FROM ONLY <replaceable class="parameter">table</replaceable></literal>.
530 However it doesn't directly support other relation types,
531 such as partitioned tables, inheritance child tables, or views.
532 To copy all rows from such relations, use <literal>COPY (SELECT * FROM
533 <replaceable class="parameter">table</replaceable>) TO</literal>.
534 </para>
535
536 <para>
537 <command>COPY FROM</command> can be used with plain, foreign, or
538 partitioned tables or with views that have
539 <literal>INSTEAD OF INSERT</literal> triggers.
540 </para>
541
542 <para>
543 You must have select privilege on the table
544 whose values are read by <command>COPY TO</command>, and
545 insert privilege on the table into which values
546 are inserted by <command>COPY FROM</command>. It is sufficient
547 to have column privileges on the column(s) listed in the command.
548 </para>
549
550 <para>
551 If row-level security is enabled for the table, the relevant
552 <command>SELECT</command> policies will apply to <literal>COPY
553 <replaceable class="parameter">table</replaceable> TO</literal> statements.
554 Currently, <command>COPY FROM</command> is not supported for tables
555 with row-level security. Use equivalent <command>INSERT</command>
556 statements instead.
557 </para>
558
559 <para>
560 Files named in a <command>COPY</command> command are read or written
561 directly by the server, not by the client application. Therefore,
562 they must reside on or be accessible to the database server machine,
563 not the client. They must be accessible to and readable or writable
564 by the <productname>PostgreSQL</productname> user (the user ID the
565 server runs as), not the client. Similarly,
566 the command specified with <literal>PROGRAM</literal> is executed directly
567 by the server, not by the client application, must be executable by the
568 <productname>PostgreSQL</productname> user.
569 <command>COPY</command> naming a file or command is only allowed to
570 database superusers or users who are granted one of the roles
571 <literal>pg_read_server_files</literal>,
572 <literal>pg_write_server_files</literal>,
573 or <literal>pg_execute_server_program</literal>, since it allows reading
574 or writing any file or running a program that the server has privileges to
575 access.
576 </para>
577
578 <para>
579 Do not confuse <command>COPY</command> with the
580 <application>psql</application> instruction
581 <command><link linkend="app-psql-meta-commands-copy">\copy</link></command>. <command>\copy</command> invokes
582 <command>COPY FROM STDIN</command> or <command>COPY TO
583 STDOUT</command>, and then fetches/stores the data in a file
584 accessible to the <application>psql</application> client. Thus,
585 file accessibility and access rights depend on the client rather
586 than the server when <command>\copy</command> is used.
587 </para>
588
589 <para>
590 It is recommended that the file name used in <command>COPY</command>
591 always be specified as an absolute path. This is enforced by the
592 server in the case of <command>COPY TO</command>, but for
593 <command>COPY FROM</command> you do have the option of reading from
594 a file specified by a relative path. The path will be interpreted
595 relative to the working directory of the server process (normally
596 the cluster's data directory), not the client's working directory.
597 </para>
598
599 <para>
600 Executing a command with <literal>PROGRAM</literal> might be restricted
601 by the operating system's access control mechanisms, such as SELinux.
602 </para>
603
604 <para>
605 <command>COPY FROM</command> will invoke any triggers and check
606 constraints on the destination table. However, it will not invoke rules.
607 </para>
608
609 <para>
610 For identity columns, the <command>COPY FROM</command> command will always
611 write the column values provided in the input data, like
612 the <command>INSERT</command> option <literal>OVERRIDING SYSTEM
613 VALUE</literal>.
614 </para>
615
616 <para>
617 <command>COPY</command> input and output is affected by
618 <varname>DateStyle</varname>. To ensure portability to other
619 <productname>PostgreSQL</productname> installations that might use
620 non-default <varname>DateStyle</varname> settings,
621 <varname>DateStyle</varname> should be set to <literal>ISO</literal> before
622 using <command>COPY TO</command>. It is also a good idea to avoid dumping
623 data with <varname>IntervalStyle</varname> set to
624 <literal>sql_standard</literal>, because negative interval values might be
625 misinterpreted by a server that has a different setting for
626 <varname>IntervalStyle</varname>.
627 </para>
628
629 <para>
630 Input data is interpreted according to <literal>ENCODING</literal>
631 option or the current client encoding, and output data is encoded
632 in <literal>ENCODING</literal> or the current client encoding, even
633 if the data does not pass through the client but is read from or
634 written to a file directly by the server.
635 </para>
636
637 <para>
638 The <command>COPY FROM</command> command physically inserts input rows
639 into the table as it progresses. If the command fails, these rows are
640 left in a deleted state; these rows will not be visible, but still
641 occupy disk space. This might amount to considerable
642 wasted disk space if the failure happened well into a large copy
643 operation. <command>VACUUM</command> should be used to recover the
644 wasted space.
645 </para>
646
647 <para>
648 <literal>FORCE_NULL</literal> and <literal>FORCE_NOT_NULL</literal> can be used
649 simultaneously on the same column. This results in converting quoted
650 null strings to null values and unquoted null strings to empty strings.
651 </para>
652
653 </refsect1>
654
655 <refsect1 id="sql-copy-file-formats" xreflabel="File Formats">
656 <title>File Formats</title>
657
658 <refsect2 id="sql-copy-text-format" xreflabel="Text Format">
659 <title>Text Format</title>
660
661 <para>
662 When the <literal>text</literal> format is used,
663 the data read or written is a text file with one line per table row.
664 Columns in a row are separated by the delimiter character.
665 The column values themselves are strings generated by the
666 output function, or acceptable to the input function, of each
667 attribute's data type. The specified null string is used in
668 place of columns that are null.
669 <command>COPY FROM</command> will raise an error if any line of the
670 input file contains more or fewer columns than are expected.
671 </para>
672
673 <para>
674 End of data can be represented by a line containing just
675 backslash-period (<literal>\.</literal>). An end-of-data marker is
676 not necessary when reading from a file, since the end of file
677 serves perfectly well; in that context this provision exists only for
678 backward compatibility. However, <application>psql</application>
679 uses <literal>\.</literal> to terminate a <literal>COPY FROM
680 STDIN</literal> operation (that is, reading
681 in-line <command>COPY</command> data in an SQL script). In that
682 context the rule is needed to be able to end the operation before the
683 end of the script.
684 </para>
685
686 <para>
687 Backslash characters (<literal>\</literal>) can be used in the
688 <command>COPY</command> data to quote data characters that might
689 otherwise be taken as row or column delimiters. In particular, the
690 following characters <emphasis>must</emphasis> be preceded by a backslash if
691 they appear as part of a column value: backslash itself,
692 newline, carriage return, and the current delimiter character.
693 </para>
694
695 <para>
696 The specified null string is sent by <command>COPY TO</command> without
697 adding any backslashes; conversely, <command>COPY FROM</command> matches
698 the input against the null string before removing backslashes. Therefore,
699 a null string such as <literal>\N</literal> cannot be confused with
700 the actual data value <literal>\N</literal> (which would be represented
701 as <literal>\\N</literal>).
702 </para>
703
704 <para>
705 The following special backslash sequences are recognized by
706 <command>COPY FROM</command>:
707
708 <informaltable>
709 <tgroup cols="2">
710 <thead>
711 <row>
712 <entry>Sequence</entry>
713 <entry>Represents</entry>
714 </row>
715 </thead>
716
717 <tbody>
718 <row>
719 <entry><literal>\b</literal></entry>
720 <entry>Backspace (ASCII 8)</entry>
721 </row>
722 <row>
723 <entry><literal>\f</literal></entry>
724 <entry>Form feed (ASCII 12)</entry>
725 </row>
726 <row>
727 <entry><literal>\n</literal></entry>
728 <entry>Newline (ASCII 10)</entry>
729 </row>
730 <row>
731 <entry><literal>\r</literal></entry>
732 <entry>Carriage return (ASCII 13)</entry>
733 </row>
734 <row>
735 <entry><literal>\t</literal></entry>
736 <entry>Tab (ASCII 9)</entry>
737 </row>
738 <row>
739 <entry><literal>\v</literal></entry>
740 <entry>Vertical tab (ASCII 11)</entry>
741 </row>
742 <row>
743 <entry><literal>\</literal><replaceable>digits</replaceable></entry>
744 <entry>Backslash followed by one to three octal digits specifies
745 the byte with that numeric code</entry>
746 </row>
747 <row>
748 <entry><literal>\x</literal><replaceable>digits</replaceable></entry>
749 <entry>Backslash <literal>x</literal> followed by one or two hex digits specifies
750 the byte with that numeric code</entry>
751 </row>
752 </tbody>
753 </tgroup>
754 </informaltable>
755
756 Presently, <command>COPY TO</command> will never emit an octal or
757 hex-digits backslash sequence, but it does use the other sequences
758 listed above for those control characters.
759 </para>
760
761 <para>
762 Any other backslashed character that is not mentioned in the above table
763 will be taken to represent itself. However, beware of adding backslashes
764 unnecessarily, since that might accidentally produce a string matching the
765 end-of-data marker (<literal>\.</literal>) or the null string (<literal>\N</literal> by
766 default). These strings will be recognized before any other backslash
767 processing is done.
768 </para>
769
770 <para>
771 It is strongly recommended that applications generating <command>COPY</command> data convert
772 data newlines and carriage returns to the <literal>\n</literal> and
773 <literal>\r</literal> sequences respectively. At present it is
774 possible to represent a data carriage return by a backslash and carriage
775 return, and to represent a data newline by a backslash and newline.
776 However, these representations might not be accepted in future releases.
777 They are also highly vulnerable to corruption if the <command>COPY</command> file is
778 transferred across different machines (for example, from Unix to Windows
779 or vice versa).
780 </para>
781
782 <para>
783 All backslash sequences are interpreted after encoding conversion.
784 The bytes specified with the octal and hex-digit backslash sequences must
785 form valid characters in the database encoding.
786 </para>
787
788 <para>
789 <command>COPY TO</command> will terminate each row with a Unix-style
790 newline (<quote><literal>\n</literal></quote>). Servers running on Microsoft Windows instead
791 output carriage return/newline (<quote><literal>\r\n</literal></quote>), but only for
792 <command>COPY</command> to a server file; for consistency across platforms,
793 <command>COPY TO STDOUT</command> always sends <quote><literal>\n</literal></quote>
794 regardless of server platform.
795 <command>COPY FROM</command> can handle lines ending with newlines,
796 carriage returns, or carriage return/newlines. To reduce the risk of
797 error due to un-backslashed newlines or carriage returns that were
798 meant as data, <command>COPY FROM</command> will complain if the line
799 endings in the input are not all alike.
800 </para>
801 </refsect2>
802
803 <refsect2 id="sql-copy-csv-format" xreflabel="CSV Format">
804 <title>CSV Format</title>
805
806 <para>
807 This format option is used for importing and exporting the Comma-
808 Separated Value (<literal>CSV</literal>) file format used by many other
809 programs, such as spreadsheets. Instead of the escaping rules used by
810 <productname>PostgreSQL</productname>'s standard text format, it
811 produces and recognizes the common <literal>CSV</literal> escaping mechanism.
812 </para>
813
814 <para>
815 The values in each record are separated by the <literal>DELIMITER</literal>
816 character. If the value contains the delimiter character, the
817 <literal>QUOTE</literal> character, the <literal>NULL</literal> string, a carriage
818 return, or line feed character, then the whole value is prefixed and
819 suffixed by the <literal>QUOTE</literal> character, and any occurrence
820 within the value of a <literal>QUOTE</literal> character or the
821 <literal>ESCAPE</literal> character is preceded by the escape character.
822 You can also use <literal>FORCE_QUOTE</literal> to force quotes when outputting
823 non-<literal>NULL</literal> values in specific columns.
824 </para>
825
826 <para>
827 The <literal>CSV</literal> format has no standard way to distinguish a
828 <literal>NULL</literal> value from an empty string.
829 <productname>PostgreSQL</productname>'s <command>COPY</command> handles this by quoting.
830 A <literal>NULL</literal> is output as the <literal>NULL</literal> parameter string
831 and is not quoted, while a non-<literal>NULL</literal> value matching the
832 <literal>NULL</literal> parameter string is quoted. For example, with the
833 default settings, a <literal>NULL</literal> is written as an unquoted empty
834 string, while an empty string data value is written with double quotes
835 (<literal>""</literal>). Reading values follows similar rules. You can
836 use <literal>FORCE_NOT_NULL</literal> to prevent <literal>NULL</literal> input
837 comparisons for specific columns. You can also use
838 <literal>FORCE_NULL</literal> to convert quoted null string data values to
839 <literal>NULL</literal>.
840 </para>
841
842 <para>
843 Because backslash is not a special character in the <literal>CSV</literal>
844 format, the end-of-data marker used in text mode (<literal>\.</literal>)
845 is not normally treated as special when reading <literal>CSV</literal>
846 data. An exception is that <application>psql</application> will terminate
847 a <literal>COPY FROM STDIN</literal> operation (that is, reading
848 in-line <command>COPY</command> data in an SQL script) at a line containing
849 only <literal>\.</literal>, whether it is text or <literal>CSV</literal>
850 mode.
851 </para>
852
853 <note>
854 <para>
855 <productname>PostgreSQL</productname> versions before v18 always
856 recognized unquoted <literal>\.</literal> as an end-of-data marker,
857 even when reading from a separate file. For compatibility with older
858 versions, <command>COPY TO</command> will quote <literal>\.</literal>
859 when it's alone on a line, even though this is no longer necessary.
860 </para>
861 </note>
862
863 <note>
864 <para>
865 In <literal>CSV</literal> format, all characters are significant. A quoted value
866 surrounded by white space, or any characters other than
867 <literal>DELIMITER</literal>, will include those characters. This can cause
868 errors if you import data from a system that pads <literal>CSV</literal>
869 lines with white space out to some fixed width. If such a situation
870 arises you might need to preprocess the <literal>CSV</literal> file to remove
871 the trailing white space, before importing the data into
872 <productname>PostgreSQL</productname>.
873 </para>
874 </note>
875
876 <note>
877 <para>
878 <literal>CSV</literal> format will both recognize and produce <literal>CSV</literal> files with quoted
879 values containing embedded carriage returns and line feeds. Thus
880 the files are not strictly one line per table row like text-format
881 files.
882 </para>
883 </note>
884
885 <note>
886 <para>
887 Many programs produce strange and occasionally perverse <literal>CSV</literal> files,
888 so the file format is more a convention than a standard. Thus you
889 might encounter some files that cannot be imported using this
890 mechanism, and <command>COPY</command> might produce files that other
891 programs cannot process.
892 </para>
893 </note>
894
895 </refsect2>
896
897 <refsect2 id="sql-copy-binary-format" xreflabel="Binary Format">
898 <title>Binary Format</title>
899
900 <para>
901 The <literal>binary</literal> format option causes all data to be
902 stored/read as binary format rather than as text. It is
903 somewhat faster than the text and <literal>CSV</literal> formats,
904 but a binary-format file is less portable across machine architectures and
905 <productname>PostgreSQL</productname> versions.
906 Also, the binary format is very data type specific; for example
907 it will not work to output binary data from a <type>smallint</type> column
908 and read it into an <type>integer</type> column, even though that would work
909 fine in text format.
910 </para>
911
912 <para>
913 The <literal>binary</literal> file format consists
914 of a file header, zero or more tuples containing the row data, and
915 a file trailer. Headers and data are in network byte order.
916 </para>
917
918 <note>
919 <para>
920 <productname>PostgreSQL</productname> releases before 7.4 used a
921 different binary file format.
922 </para>
923 </note>
924
925 <refsect3>
926 <title>File Header</title>
927
928 <para>
929 The file header consists of 15 bytes of fixed fields, followed
930 by a variable-length header extension area. The fixed fields are:
931
932 <variablelist>
933 <varlistentry>
934 <term>Signature</term>
935 <listitem>
936 <para>
937 11-byte sequence <literal>PGCOPY\n\377\r\n\0</literal> &mdash; note that the zero byte
938 is a required part of the signature. (The signature is designed to allow
939 easy identification of files that have been munged by a non-8-bit-clean
940 transfer. This signature will be changed by end-of-line-translation
941 filters, dropped zero bytes, dropped high bits, or parity changes.)
942 </para>
943 </listitem>
944 </varlistentry>
945
946 <varlistentry>
947 <term>Flags field</term>
948 <listitem>
949 <para>
950 32-bit integer bit mask to denote important aspects of the file format. Bits
951 are numbered from 0 (<acronym>LSB</acronym>) to 31 (<acronym>MSB</acronym>). Note that
952 this field is stored in network byte order (most significant byte first),
953 as are all the integer fields used in the file format. Bits
954 16&ndash;31 are reserved to denote critical file format issues; a reader
955 should abort if it finds an unexpected bit set in this range. Bits 0&ndash;15
956 are reserved to signal backwards-compatible format issues; a reader
957 should simply ignore any unexpected bits set in this range. Currently
958 only one flag bit is defined, and the rest must be zero:
959 <variablelist>
960 <varlistentry>
961 <term>Bit 16</term>
962 <listitem>
963 <para>
964 If 1, OIDs are included in the data; if 0, not. Oid system columns
965 are not supported in <productname>PostgreSQL</productname>
966 anymore, but the format still contains the indicator.
967 </para>
968 </listitem>
969 </varlistentry>
970 </variablelist></para>
971 </listitem>
972 </varlistentry>
973
974 <varlistentry>
975 <term>Header extension area length</term>
976 <listitem>
977 <para>
978 32-bit integer, length in bytes of remainder of header, not including self.
979 Currently, this is zero, and the first tuple follows
980 immediately. Future changes to the format might allow additional data
981 to be present in the header. A reader should silently skip over any header
982 extension data it does not know what to do with.
983 </para>
984 </listitem>
985 </varlistentry>
986 </variablelist>
987 </para>
988
989 <para>
990 The header extension area is envisioned to contain a sequence of
991 self-identifying chunks. The flags field is not intended to tell readers
992 what is in the extension area. Specific design of header extension contents
993 is left for a later release.
994 </para>
995
996 <para>
997 This design allows for both backwards-compatible header additions (add
998 header extension chunks, or set low-order flag bits) and
999 non-backwards-compatible changes (set high-order flag bits to signal such
1000 changes, and add supporting data to the extension area if needed).
1001 </para>
1002 </refsect3>
1003
1004 <refsect3>
1005 <title>Tuples</title>
1006 <para>
1007 Each tuple begins with a 16-bit integer count of the number of fields in the
1008 tuple. (Presently, all tuples in a table will have the same count, but that
1009 might not always be true.) Then, repeated for each field in the tuple, there
1010 is a 32-bit length word followed by that many bytes of field data. (The
1011 length word does not include itself, and can be zero.) As a special case,
1012 -1 indicates a NULL field value. No value bytes follow in the NULL case.
1013 </para>
1014
1015 <para>
1016 There is no alignment padding or any other extra data between fields.
1017 </para>
1018
1019 <para>
1020 Presently, all data values in a binary-format file are
1021 assumed to be in binary format (format code one). It is anticipated that a
1022 future extension might add a header field that allows per-column format codes
1023 to be specified.
1024 </para>
1025
1026 <para>
1027 To determine the appropriate binary format for the actual tuple data you
1028 should consult the <productname>PostgreSQL</productname> source, in
1029 particular the <function>*send</function> and <function>*recv</function> functions for
1030 each column's data type (typically these functions are found in the
1031 <filename>src/backend/utils/adt/</filename> directory of the source
1032 distribution).
1033 </para>
1034
1035 <para>
1036 If OIDs are included in the file, the OID field immediately follows the
1037 field-count word. It is a normal field except that it's not included in the
1038 field-count. Note that oid system columns are not supported in current
1039 versions of <productname>PostgreSQL</productname>.
1040 </para>
1041 </refsect3>
1042
1043 <refsect3>
1044 <title>File Trailer</title>
1045
1046 <para>
1047 The file trailer consists of a 16-bit integer word containing -1. This
1048 is easily distinguished from a tuple's field-count word.
1049 </para>
1050
1051 <para>
1052 A reader should report an error if a field-count word is neither -1
1053 nor the expected number of columns. This provides an extra
1054 check against somehow getting out of sync with the data.
1055 </para>
1056 </refsect3>
1057 </refsect2>
1058 </refsect1>
1059
1060 <refsect1>
1061 <title>Examples</title>
1062
1063 <para>
1064 The following example copies a table to the client
1065 using the vertical bar (<literal>|</literal>) as the field delimiter:
1066 <programlisting>
1067 COPY country TO STDOUT (DELIMITER '|');
1068 </programlisting>
1069 </para>
1070
1071 <para>
1072 To copy data from a file into the <literal>country</literal> table:
1073 <programlisting>
1074 COPY country FROM '/usr1/proj/bray/sql/country_data';
1075 </programlisting>
1076 </para>
1077
1078 <para>
1079 To copy into a file just the countries whose names start with 'A':
1080 <programlisting>
1081 COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';
1082 </programlisting>
1083 </para>
1084
1085 <para>
1086 To copy into a compressed file, you can pipe the output through an external
1087 compression program:
1088 <programlisting>
1089 COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';
1090 </programlisting>
1091 </para>
1092
1093 <para>
1094 Here is a sample of data suitable for copying into a table from
1095 <literal>STDIN</literal>:
1096 <programlisting>
1097 AF AFGHANISTAN
1098 AL ALBANIA
1099 DZ ALGERIA
1100 ZM ZAMBIA
1101 ZW ZIMBABWE
1102 </programlisting>
1103 Note that the white space on each line is actually a tab character.
1104 </para>
1105
1106 <para>
1107 The following is the same data, output in binary format.
1108 The data is shown after filtering through the
1109 Unix utility <command>od -c</command>. The table has three columns;
1110 the first has type <type>char(2)</type>, the second has type <type>text</type>,
1111 and the third has type <type>integer</type>. All the rows have a null value
1112 in the third column.
1113 <programlisting>
1114 0000000 P G C O P Y \n 377 \r \n \0 \0 \0 \0 \0 \0
1115 0000020 \0 \0 \0 \0 003 \0 \0 \0 002 A F \0 \0 \0 013 A
1116 0000040 F G H A N I S T A N 377 377 377 377 \0 003
1117 0000060 \0 \0 \0 002 A L \0 \0 \0 007 A L B A N I
1118 0000100 A 377 377 377 377 \0 003 \0 \0 \0 002 D Z \0 \0 \0
1119 0000120 007 A L G E R I A 377 377 377 377 \0 003 \0 \0
1120 0000140 \0 002 Z M \0 \0 \0 006 Z A M B I A 377 377
1121 0000160 377 377 \0 003 \0 \0 \0 002 Z W \0 \0 \0 \b Z I
1122 0000200 M B A B W E 377 377 377 377 377 377
1123 </programlisting></para>
1124 </refsect1>
1125
1126 <refsect1>
1127 <title>Compatibility</title>
1128
1129 <para>
1130 There is no <command>COPY</command> statement in the SQL standard.
1131 </para>
1132
1133 <para>
1134 The following syntax was used before <productname>PostgreSQL</productname>
1135 version 9.0 and is still supported:
1136
1137 <synopsis>
1138 COPY <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
1139 FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
1140 [ [ WITH ]
1141 [ BINARY ]
1142 [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter_character</replaceable>' ]
1143 [ NULL [ AS ] '<replaceable class="parameter">null_string</replaceable>' ]
1144 [ CSV [ HEADER ]
1145 [ QUOTE [ AS ] '<replaceable class="parameter">quote_character</replaceable>' ]
1146 [ ESCAPE [ AS ] '<replaceable class="parameter">escape_character</replaceable>' ]
1147 [ FORCE NOT NULL <replaceable class="parameter">column_name</replaceable> [, ...] ] ] ]
1148
1149 COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
1150 TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
1151 [ [ WITH ]
1152 [ BINARY ]
1153 [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter_character</replaceable>' ]
1154 [ NULL [ AS ] '<replaceable class="parameter">null_string</replaceable>' ]
1155 [ CSV [ HEADER ]
1156 [ QUOTE [ AS ] '<replaceable class="parameter">quote_character</replaceable>' ]
1157 [ ESCAPE [ AS ] '<replaceable class="parameter">escape_character</replaceable>' ]
1158 [ FORCE QUOTE { <replaceable class="parameter">column_name</replaceable> [, ...] | * } ] ] ]
1159 </synopsis>
1160
1161 Note that in this syntax, <literal>BINARY</literal> and <literal>CSV</literal> are
1162 treated as independent keywords, not as arguments of a <literal>FORMAT</literal>
1163 option.
1164 </para>
1165
1166 <para>
1167 The following syntax was used before <productname>PostgreSQL</productname>
1168 version 7.3 and is still supported:
1169
1170 <synopsis>
1171 COPY [ BINARY ] <replaceable class="parameter">table_name</replaceable>
1172 FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
1173 [ [USING] DELIMITERS '<replaceable class="parameter">delimiter_character</replaceable>' ]
1174 [ WITH NULL AS '<replaceable class="parameter">null_string</replaceable>' ]
1175
1176 COPY [ BINARY ] <replaceable class="parameter">table_name</replaceable>
1177 TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
1178 [ [USING] DELIMITERS '<replaceable class="parameter">delimiter_character</replaceable>' ]
1179 [ WITH NULL AS '<replaceable class="parameter">null_string</replaceable>' ]
1180 </synopsis></para>
1181 </refsect1>
1182
1183 <refsect1>
1184 <title>See Also</title>
1185
1186 <simplelist type="inline">
1187 <member><xref linkend="copy-progress-reporting"/></member>
1188 </simplelist>
1189 </refsect1>
1190 </refentry>