| 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> — 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–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–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> |