codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>COPY</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-commit-prepared.html" title="COMMIT PREPARED" /><link rel="next" href="sql-create-access-method.html" title="CREATE ACCESS METHOD" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">COPY</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-commit-prepared.html" title="COMMIT PREPARED">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-create-access-method.html" title="CREATE ACCESS METHOD">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-COPY"><div class="titlepage"></div><a id="id-1.9.3.55.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">COPY</span></h2><p>COPY — copy data between a file and a table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3COPY <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ]4 FROM { '<em class="replaceable"><code>filename</code></em>' | PROGRAM '<em class="replaceable"><code>command</code></em>' | STDIN }5 [ [ WITH ] ( <em class="replaceable"><code>option</code></em> [, ...] ) ]6 [ WHERE <em class="replaceable"><code>condition</code></em> ]7 8COPY { <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] | ( <em class="replaceable"><code>query</code></em> ) }9 TO { '<em class="replaceable"><code>filename</code></em>' | PROGRAM '<em class="replaceable"><code>command</code></em>' | STDOUT }10 [ [ WITH ] ( <em class="replaceable"><code>option</code></em> [, ...] ) ]11 12<span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span>13 14 FORMAT <em class="replaceable"><code>format_name</code></em>15 FREEZE [ <em class="replaceable"><code>boolean</code></em> ]16 DELIMITER '<em class="replaceable"><code>delimiter_character</code></em>'17 NULL '<em class="replaceable"><code>null_string</code></em>'18 DEFAULT '<em class="replaceable"><code>default_string</code></em>'19 HEADER [ <em class="replaceable"><code>boolean</code></em> | MATCH ]20 QUOTE '<em class="replaceable"><code>quote_character</code></em>'21 ESCAPE '<em class="replaceable"><code>escape_character</code></em>'22 FORCE_QUOTE { ( <em class="replaceable"><code>column_name</code></em> [, ...] ) | * }23 FORCE_NOT_NULL ( <em class="replaceable"><code>column_name</code></em> [, ...] )24 FORCE_NULL ( <em class="replaceable"><code>column_name</code></em> [, ...] )25 ENCODING '<em class="replaceable"><code>encoding_name</code></em>'26</pre></div><div class="refsect1" id="id-1.9.3.55.5"><h2>Description</h2><p>27 <code class="command">COPY</code> moves data between28 <span class="productname">PostgreSQL</span> tables and standard file-system29 files. <code class="command">COPY TO</code> copies the contents of a table30 <span class="emphasis"><em>to</em></span> a file, while <code class="command">COPY FROM</code> copies31 data <span class="emphasis"><em>from</em></span> a file to a table (appending the data to32 whatever is in the table already). <code class="command">COPY TO</code>33 can also copy the results of a <code class="command">SELECT</code> query.34 </p><p>35 If a column list is specified, <code class="command">COPY TO</code> copies only36 the data in the specified columns to the file. For <code class="command">COPY37 FROM</code>, each field in the file is inserted, in order, into the38 specified column. Table columns not specified in the <code class="command">COPY39 FROM</code> column list will receive their default values.40 </p><p>41 <code class="command">COPY</code> with a file name instructs the42 <span class="productname">PostgreSQL</span> server to directly read from43 or write to a file. The file must be accessible by the44 <span class="productname">PostgreSQL</span> user (the user ID the server45 runs as) and the name must be specified from the viewpoint of the46 server. When <code class="literal">PROGRAM</code> is specified, the server47 executes the given command and reads from the standard output of the48 program, or writes to the standard input of the program. The command49 must be specified from the viewpoint of the server, and be executable50 by the <span class="productname">PostgreSQL</span> user. When51 <code class="literal">STDIN</code> or <code class="literal">STDOUT</code> is52 specified, data is transmitted via the connection between the53 client and the server.54 </p><p>55 Each backend running <code class="command">COPY</code> will report its progress56 in the <code class="structname">pg_stat_progress_copy</code> view. See57 <a class="xref" href="progress-reporting.html#COPY-PROGRESS-REPORTING" title="28.4.3. COPY Progress Reporting">Section 28.4.3</a> for details.58 </p></div><div class="refsect1" id="id-1.9.3.55.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>59 The name (optionally schema-qualified) of an existing table.60 </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>61 An optional list of columns to be copied. If no column list is62 specified, all columns of the table except generated columns will be63 copied.64 </p></dd><dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt><dd><p>65 A <a class="link" href="sql-select.html" title="SELECT"><code class="command">SELECT</code></a>,66 <a class="link" href="sql-values.html" title="VALUES"><code class="command">VALUES</code></a>,67 <a class="link" href="sql-insert.html" title="INSERT"><code class="command">INSERT</code></a>,68 <a class="link" href="sql-update.html" title="UPDATE"><code class="command">UPDATE</code></a>, or69 <a class="link" href="sql-delete.html" title="DELETE"><code class="command">DELETE</code></a> command whose results are to be70 copied. Note that parentheses are required around the query.71 </p><p>72 For <code class="command">INSERT</code>, <code class="command">UPDATE</code> and73 <code class="command">DELETE</code> queries a <code class="literal">RETURNING</code> clause74 must be provided, and the target relation must not have a conditional75 rule, nor an <code class="literal">ALSO</code> rule, nor an76 <code class="literal">INSTEAD</code> rule that expands to multiple statements.77 </p></dd><dt><span class="term"><em class="replaceable"><code>filename</code></em></span></dt><dd><p>78 The path name of the input or output file. An input file name can be79 an absolute or relative path, but an output file name must be an absolute80 path. Windows users might need to use an <code class="literal">E''</code> string and81 double any backslashes used in the path name.82 </p></dd><dt><span class="term"><code class="literal">PROGRAM</code></span></dt><dd><p>83 A command to execute. In <code class="command">COPY FROM</code>, the input is84 read from standard output of the command, and in <code class="command">COPY TO</code>,85 the output is written to the standard input of the command.86 </p><p>87 Note that the command is invoked by the shell, so if you need to pass88 any arguments that come from an untrusted source, you89 must be careful to strip or escape any special characters that might90 have a special meaning for the shell. For security reasons, it is best91 to use a fixed command string, or at least avoid including any user input92 in it.93 </p></dd><dt><span class="term"><code class="literal">STDIN</code></span></dt><dd><p>94 Specifies that input comes from the client application.95 </p></dd><dt><span class="term"><code class="literal">STDOUT</code></span></dt><dd><p>96 Specifies that output goes to the client application.97 </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p>98 Specifies whether the selected option should be turned on or off.99 You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or100 <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>,101 <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it. The102 <em class="replaceable"><code>boolean</code></em> value can also103 be omitted, in which case <code class="literal">TRUE</code> is assumed.104 </p></dd><dt><span class="term"><code class="literal">FORMAT</code></span></dt><dd><p>105 Selects the data format to be read or written:106 <code class="literal">text</code>,107 <code class="literal">csv</code> (Comma Separated Values),108 or <code class="literal">binary</code>.109 The default is <code class="literal">text</code>.110 </p></dd><dt><span class="term"><code class="literal">FREEZE</code></span></dt><dd><p>111 Requests copying the data with rows already frozen, just as they112 would be after running the <code class="command">VACUUM FREEZE</code> command.113 This is intended as a performance option for initial data loading.114 Rows will be frozen only if the table being loaded has been created115 or truncated in the current subtransaction, there are no cursors116 open and there are no older snapshots held by this transaction. It is117 currently not possible to perform a <code class="command">COPY FREEZE</code> on118 a partitioned table.119 </p><p>120 Note that all other sessions will immediately be able to see the data121 once it has been successfully loaded. This violates the normal rules122 of MVCC visibility and users should be aware of the123 potential problems this might cause.124 </p></dd><dt><span class="term"><code class="literal">DELIMITER</code></span></dt><dd><p>125 Specifies the character that separates columns within each row126 (line) of the file. The default is a tab character in text format,127 a comma in <code class="literal">CSV</code> format.128 This must be a single one-byte character.129 This option is not allowed when using <code class="literal">binary</code> format.130 </p></dd><dt><span class="term"><code class="literal">NULL</code></span></dt><dd><p>131 Specifies the string that represents a null value. The default is132 <code class="literal">\N</code> (backslash-N) in text format, and an unquoted empty133 string in <code class="literal">CSV</code> format. You might prefer an134 empty string even in text format for cases where you don't want to135 distinguish nulls from empty strings.136 This option is not allowed when using <code class="literal">binary</code> format.137 </p><div class="note"><h3 class="title">Note</h3><p>138 When using <code class="command">COPY FROM</code>, any data item that matches139 this string will be stored as a null value, so you should make140 sure that you use the same string as you used with141 <code class="command">COPY TO</code>.142 </p></div></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>143 Specifies the string that represents a default value. Each time the string144 is found in the input file, the default value of the corresponding column145 will be used.146 This option is allowed only in <code class="command">COPY FROM</code>, and only when147 not using <code class="literal">binary</code> format.148 </p></dd><dt><span class="term"><code class="literal">HEADER</code></span></dt><dd><p>149 Specifies that the file contains a header line with the names of each150 column in the file. On output, the first line contains the column151 names from the table. On input, the first line is discarded when this152 option is set to <code class="literal">true</code> (or equivalent Boolean value).153 If this option is set to <code class="literal">MATCH</code>, the number and names154 of the columns in the header line must match the actual column names of155 the table, in order; otherwise an error is raised.156 This option is not allowed when using <code class="literal">binary</code> format.157 The <code class="literal">MATCH</code> option is only valid for <code class="command">COPY158 FROM</code> commands.159 </p></dd><dt><span class="term"><code class="literal">QUOTE</code></span></dt><dd><p>160 Specifies the quoting character to be used when a data value is quoted.161 The default is double-quote.162 This must be a single one-byte character.163 This option is allowed only when using <code class="literal">CSV</code> format.164 </p></dd><dt><span class="term"><code class="literal">ESCAPE</code></span></dt><dd><p>165 Specifies the character that should appear before a166 data character that matches the <code class="literal">QUOTE</code> value.167 The default is the same as the <code class="literal">QUOTE</code> value (so that168 the quoting character is doubled if it appears in the data).169 This must be a single one-byte character.170 This option is allowed only when using <code class="literal">CSV</code> format.171 </p></dd><dt><span class="term"><code class="literal">FORCE_QUOTE</code></span></dt><dd><p>172 Forces quoting to be173 used for all non-<code class="literal">NULL</code> values in each specified column.174 <code class="literal">NULL</code> output is never quoted. If <code class="literal">*</code> is specified,175 non-<code class="literal">NULL</code> values will be quoted in all columns.176 This option is allowed only in <code class="command">COPY TO</code>, and only when177 using <code class="literal">CSV</code> format.178 </p></dd><dt><span class="term"><code class="literal">FORCE_NOT_NULL</code></span></dt><dd><p>179 Do not match the specified columns' values against the null string.180 In the default case where the null string is empty, this means that181 empty values will be read as zero-length strings rather than nulls,182 even when they are not quoted.183 This option is allowed only in <code class="command">COPY FROM</code>, and only when184 using <code class="literal">CSV</code> format.185 </p></dd><dt><span class="term"><code class="literal">FORCE_NULL</code></span></dt><dd><p>186 Match the specified columns' values against the null string, even187 if it has been quoted, and if a match is found set the value to188 <code class="literal">NULL</code>. In the default case where the null string is empty,189 this converts a quoted empty string into NULL.190 This option is allowed only in <code class="command">COPY FROM</code>, and only when191 using <code class="literal">CSV</code> format.192 </p></dd><dt><span class="term"><code class="literal">ENCODING</code></span></dt><dd><p>193 Specifies that the file is encoded in the <em class="replaceable"><code>encoding_name</code></em>. If this option is194 omitted, the current client encoding is used. See the Notes below195 for more details.196 </p></dd><dt><span class="term"><code class="literal">WHERE</code></span></dt><dd><p>197 The optional <code class="literal">WHERE</code> clause has the general form198</p><pre class="synopsis">199WHERE <em class="replaceable"><code>condition</code></em>200</pre><p>201 where <em class="replaceable"><code>condition</code></em> is202 any expression that evaluates to a result of type203 <code class="type">boolean</code>. Any row that does not satisfy this204 condition will not be inserted to the table. A row satisfies the205 condition if it returns true when the actual row values are206 substituted for any variable references.207 </p><p>208 Currently, subqueries are not allowed in <code class="literal">WHERE</code>209 expressions, and the evaluation does not see any changes made by the210 <code class="command">COPY</code> itself (this matters when the expression211 contains calls to <code class="literal">VOLATILE</code> functions).212 </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.55.7"><h2>Outputs</h2><p>213 On successful completion, a <code class="command">COPY</code> command returns a command214 tag of the form215</p><pre class="screen">216COPY <em class="replaceable"><code>count</code></em>217</pre><p>218 The <em class="replaceable"><code>count</code></em> is the number219 of rows copied.220 </p><div class="note"><h3 class="title">Note</h3><p>221 <span class="application">psql</span> will print this command tag only if the command222 was not <code class="literal">COPY ... TO STDOUT</code>, or the223 equivalent <span class="application">psql</span> meta-command224 <code class="literal">\copy ... to stdout</code>. This is to prevent confusing the225 command tag with the data that was just printed.226 </p></div></div><div class="refsect1" id="id-1.9.3.55.8"><h2>Notes</h2><p>227 <code class="command">COPY TO</code> can be used only with plain228 tables, not views, and does not copy rows from child tables229 or child partitions. For example, <code class="literal">COPY <em class="replaceable"><code>table</code></em> TO</code> copies230 the same rows as <code class="literal">SELECT * FROM ONLY <em class="replaceable"><code>table</code></em></code>.231 The syntax <code class="literal">COPY (SELECT * FROM <em class="replaceable"><code>table</code></em>) TO ...</code> can be used to232 dump all of the rows in an inheritance hierarchy, partitioned table,233 or view.234 </p><p>235 <code class="command">COPY FROM</code> can be used with plain, foreign, or236 partitioned tables or with views that have237 <code class="literal">INSTEAD OF INSERT</code> triggers.238 </p><p>239 You must have select privilege on the table240 whose values are read by <code class="command">COPY TO</code>, and241 insert privilege on the table into which values242 are inserted by <code class="command">COPY FROM</code>. It is sufficient243 to have column privileges on the column(s) listed in the command.244 </p><p>245 If row-level security is enabled for the table, the relevant246 <code class="command">SELECT</code> policies will apply to <code class="literal">COPY247 <em class="replaceable"><code>table</code></em> TO</code> statements.248 Currently, <code class="command">COPY FROM</code> is not supported for tables249 with row-level security. Use equivalent <code class="command">INSERT</code>250 statements instead.251 </p><p>252 Files named in a <code class="command">COPY</code> command are read or written253 directly by the server, not by the client application. Therefore,254 they must reside on or be accessible to the database server machine,255 not the client. They must be accessible to and readable or writable256 by the <span class="productname">PostgreSQL</span> user (the user ID the257 server runs as), not the client. Similarly,258 the command specified with <code class="literal">PROGRAM</code> is executed directly259 by the server, not by the client application, must be executable by the260 <span class="productname">PostgreSQL</span> user.261 <code class="command">COPY</code> naming a file or command is only allowed to262 database superusers or users who are granted one of the roles263 <code class="literal">pg_read_server_files</code>,264 <code class="literal">pg_write_server_files</code>,265 or <code class="literal">pg_execute_server_program</code>, since it allows reading266 or writing any file or running a program that the server has privileges to267 access.268 </p><p>269 Do not confuse <code class="command">COPY</code> with the270 <span class="application">psql</span> instruction271 <code class="command"><a class="link" href="app-psql.html#APP-PSQL-META-COMMANDS-COPY">\copy</a></code>. <code class="command">\copy</code> invokes272 <code class="command">COPY FROM STDIN</code> or <code class="command">COPY TO273 STDOUT</code>, and then fetches/stores the data in a file274 accessible to the <span class="application">psql</span> client. Thus,275 file accessibility and access rights depend on the client rather276 than the server when <code class="command">\copy</code> is used.277 </p><p>278 It is recommended that the file name used in <code class="command">COPY</code>279 always be specified as an absolute path. This is enforced by the280 server in the case of <code class="command">COPY TO</code>, but for281 <code class="command">COPY FROM</code> you do have the option of reading from282 a file specified by a relative path. The path will be interpreted283 relative to the working directory of the server process (normally284 the cluster's data directory), not the client's working directory.285 </p><p>286 Executing a command with <code class="literal">PROGRAM</code> might be restricted287 by the operating system's access control mechanisms, such as SELinux.288 </p><p>289 <code class="command">COPY FROM</code> will invoke any triggers and check290 constraints on the destination table. However, it will not invoke rules.291 </p><p>292 For identity columns, the <code class="command">COPY FROM</code> command will always293 write the column values provided in the input data, like294 the <code class="command">INSERT</code> option <code class="literal">OVERRIDING SYSTEM295 VALUE</code>.296 </p><p>297 <code class="command">COPY</code> input and output is affected by298 <code class="varname">DateStyle</code>. To ensure portability to other299 <span class="productname">PostgreSQL</span> installations that might use300 non-default <code class="varname">DateStyle</code> settings,301 <code class="varname">DateStyle</code> should be set to <code class="literal">ISO</code> before302 using <code class="command">COPY TO</code>. It is also a good idea to avoid dumping303 data with <code class="varname">IntervalStyle</code> set to304 <code class="literal">sql_standard</code>, because negative interval values might be305 misinterpreted by a server that has a different setting for306 <code class="varname">IntervalStyle</code>.307 </p><p>308 Input data is interpreted according to <code class="literal">ENCODING</code>309 option or the current client encoding, and output data is encoded310 in <code class="literal">ENCODING</code> or the current client encoding, even311 if the data does not pass through the client but is read from or312 written to a file directly by the server.313 </p><p>314 <code class="command">COPY</code> stops operation at the first error. This315 should not lead to problems in the event of a <code class="command">COPY316 TO</code>, but the target table will already have received317 earlier rows in a <code class="command">COPY FROM</code>. These rows will not318 be visible or accessible, but they still occupy disk space. This might319 amount to a considerable amount of wasted disk space if the failure320 happened well into a large copy operation. You might wish to invoke321 <code class="command">VACUUM</code> to recover the wasted space.322 </p><p>323 <code class="literal">FORCE_NULL</code> and <code class="literal">FORCE_NOT_NULL</code> can be used324 simultaneously on the same column. This results in converting quoted325 null strings to null values and unquoted null strings to empty strings.326 </p></div><div class="refsect1" id="id-1.9.3.55.9"><h2>File Formats</h2><div class="refsect2" id="id-1.9.3.55.9.2"><h3>Text Format</h3><p>327 When the <code class="literal">text</code> format is used,328 the data read or written is a text file with one line per table row.329 Columns in a row are separated by the delimiter character.330 The column values themselves are strings generated by the331 output function, or acceptable to the input function, of each332 attribute's data type. The specified null string is used in333 place of columns that are null.334 <code class="command">COPY FROM</code> will raise an error if any line of the335 input file contains more or fewer columns than are expected.336 </p><p>337 End of data can be represented by a single line containing just338 backslash-period (<code class="literal">\.</code>). An end-of-data marker is339 not necessary when reading from a file, since the end of file340 serves perfectly well; it is needed only when copying data to or from341 client applications using pre-3.0 client protocol.342 </p><p>343 Backslash characters (<code class="literal">\</code>) can be used in the344 <code class="command">COPY</code> data to quote data characters that might345 otherwise be taken as row or column delimiters. In particular, the346 following characters <span class="emphasis"><em>must</em></span> be preceded by a backslash if347 they appear as part of a column value: backslash itself,348 newline, carriage return, and the current delimiter character.349 </p><p>350 The specified null string is sent by <code class="command">COPY TO</code> without351 adding any backslashes; conversely, <code class="command">COPY FROM</code> matches352 the input against the null string before removing backslashes. Therefore,353 a null string such as <code class="literal">\N</code> cannot be confused with354 the actual data value <code class="literal">\N</code> (which would be represented355 as <code class="literal">\\N</code>).356 </p><p>357 The following special backslash sequences are recognized by358 <code class="command">COPY FROM</code>:359 360 </p><div class="informaltable"><table class="informaltable" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Sequence</th><th>Represents</th></tr></thead><tbody><tr><td><code class="literal">\b</code></td><td>Backspace (ASCII 8)</td></tr><tr><td><code class="literal">\f</code></td><td>Form feed (ASCII 12)</td></tr><tr><td><code class="literal">\n</code></td><td>Newline (ASCII 10)</td></tr><tr><td><code class="literal">\r</code></td><td>Carriage return (ASCII 13)</td></tr><tr><td><code class="literal">\t</code></td><td>Tab (ASCII 9)</td></tr><tr><td><code class="literal">\v</code></td><td>Vertical tab (ASCII 11)</td></tr><tr><td><code class="literal">\</code><em class="replaceable"><code>digits</code></em></td><td>Backslash followed by one to three octal digits specifies361 the byte with that numeric code</td></tr><tr><td><code class="literal">\x</code><em class="replaceable"><code>digits</code></em></td><td>Backslash <code class="literal">x</code> followed by one or two hex digits specifies362 the byte with that numeric code</td></tr></tbody></table></div><p>363 364 Presently, <code class="command">COPY TO</code> will never emit an octal or365 hex-digits backslash sequence, but it does use the other sequences366 listed above for those control characters.367 </p><p>368 Any other backslashed character that is not mentioned in the above table369 will be taken to represent itself. However, beware of adding backslashes370 unnecessarily, since that might accidentally produce a string matching the371 end-of-data marker (<code class="literal">\.</code>) or the null string (<code class="literal">\N</code> by372 default). These strings will be recognized before any other backslash373 processing is done.374 </p><p>375 It is strongly recommended that applications generating <code class="command">COPY</code> data convert376 data newlines and carriage returns to the <code class="literal">\n</code> and377 <code class="literal">\r</code> sequences respectively. At present it is378 possible to represent a data carriage return by a backslash and carriage379 return, and to represent a data newline by a backslash and newline.380 However, these representations might not be accepted in future releases.381 They are also highly vulnerable to corruption if the <code class="command">COPY</code> file is382 transferred across different machines (for example, from Unix to Windows383 or vice versa).384 </p><p>385 All backslash sequences are interpreted after encoding conversion.386 The bytes specified with the octal and hex-digit backslash sequences must387 form valid characters in the database encoding.388 </p><p>389 <code class="command">COPY TO</code> will terminate each row with a Unix-style390 newline (<span class="quote">“<span class="quote"><code class="literal">\n</code></span>”</span>). Servers running on Microsoft Windows instead391 output carriage return/newline (<span class="quote">“<span class="quote"><code class="literal">\r\n</code></span>”</span>), but only for392 <code class="command">COPY</code> to a server file; for consistency across platforms,393 <code class="command">COPY TO STDOUT</code> always sends <span class="quote">“<span class="quote"><code class="literal">\n</code></span>”</span>394 regardless of server platform.395 <code class="command">COPY FROM</code> can handle lines ending with newlines,396 carriage returns, or carriage return/newlines. To reduce the risk of397 error due to un-backslashed newlines or carriage returns that were398 meant as data, <code class="command">COPY FROM</code> will complain if the line399 endings in the input are not all alike.400 </p></div><div class="refsect2" id="id-1.9.3.55.9.3"><h3>CSV Format</h3><p>401 This format option is used for importing and exporting the Comma402 Separated Value (<code class="literal">CSV</code>) file format used by many other403 programs, such as spreadsheets. Instead of the escaping rules used by404 <span class="productname">PostgreSQL</span>'s standard text format, it405 produces and recognizes the common <code class="literal">CSV</code> escaping mechanism.406 </p><p>407 The values in each record are separated by the <code class="literal">DELIMITER</code>408 character. If the value contains the delimiter character, the409 <code class="literal">QUOTE</code> character, the <code class="literal">NULL</code> string, a carriage410 return, or line feed character, then the whole value is prefixed and411 suffixed by the <code class="literal">QUOTE</code> character, and any occurrence412 within the value of a <code class="literal">QUOTE</code> character or the413 <code class="literal">ESCAPE</code> character is preceded by the escape character.414 You can also use <code class="literal">FORCE_QUOTE</code> to force quotes when outputting415 non-<code class="literal">NULL</code> values in specific columns.416 </p><p>417 The <code class="literal">CSV</code> format has no standard way to distinguish a418 <code class="literal">NULL</code> value from an empty string.419 <span class="productname">PostgreSQL</span>'s <code class="command">COPY</code> handles this by quoting.420 A <code class="literal">NULL</code> is output as the <code class="literal">NULL</code> parameter string421 and is not quoted, while a non-<code class="literal">NULL</code> value matching the422 <code class="literal">NULL</code> parameter string is quoted. For example, with the423 default settings, a <code class="literal">NULL</code> is written as an unquoted empty424 string, while an empty string data value is written with double quotes425 (<code class="literal">""</code>). Reading values follows similar rules. You can426 use <code class="literal">FORCE_NOT_NULL</code> to prevent <code class="literal">NULL</code> input427 comparisons for specific columns. You can also use428 <code class="literal">FORCE_NULL</code> to convert quoted null string data values to429 <code class="literal">NULL</code>.430 </p><p>431 Because backslash is not a special character in the <code class="literal">CSV</code>432 format, <code class="literal">\.</code>, the end-of-data marker, could also appear433 as a data value. To avoid any misinterpretation, a <code class="literal">\.</code>434 data value appearing as a lone entry on a line is automatically435 quoted on output, and on input, if quoted, is not interpreted as the436 end-of-data marker. If you are loading a file created by another437 application that has a single unquoted column and might have a438 value of <code class="literal">\.</code>, you might need to quote that value in the439 input file.440 </p><div class="note"><h3 class="title">Note</h3><p>441 In <code class="literal">CSV</code> format, all characters are significant. A quoted value442 surrounded by white space, or any characters other than443 <code class="literal">DELIMITER</code>, will include those characters. This can cause444 errors if you import data from a system that pads <code class="literal">CSV</code>445 lines with white space out to some fixed width. If such a situation446 arises you might need to preprocess the <code class="literal">CSV</code> file to remove447 the trailing white space, before importing the data into448 <span class="productname">PostgreSQL</span>.449 </p></div><div class="note"><h3 class="title">Note</h3><p>450 <code class="literal">CSV</code> format will both recognize and produce <code class="literal">CSV</code> files with quoted451 values containing embedded carriage returns and line feeds. Thus452 the files are not strictly one line per table row like text-format453 files.454 </p></div><div class="note"><h3 class="title">Note</h3><p>455 Many programs produce strange and occasionally perverse <code class="literal">CSV</code> files,456 so the file format is more a convention than a standard. Thus you457 might encounter some files that cannot be imported using this458 mechanism, and <code class="command">COPY</code> might produce files that other459 programs cannot process.460 </p></div></div><div class="refsect2" id="id-1.9.3.55.9.4"><h3>Binary Format</h3><p>461 The <code class="literal">binary</code> format option causes all data to be462 stored/read as binary format rather than as text. It is463 somewhat faster than the text and <code class="literal">CSV</code> formats,464 but a binary-format file is less portable across machine architectures and465 <span class="productname">PostgreSQL</span> versions.466 Also, the binary format is very data type specific; for example467 it will not work to output binary data from a <code class="type">smallint</code> column468 and read it into an <code class="type">integer</code> column, even though that would work469 fine in text format.470 </p><p>471 The <code class="literal">binary</code> file format consists472 of a file header, zero or more tuples containing the row data, and473 a file trailer. Headers and data are in network byte order.474 </p><div class="note"><h3 class="title">Note</h3><p>475 <span class="productname">PostgreSQL</span> releases before 7.4 used a476 different binary file format.477 </p></div><div class="refsect3" id="id-1.9.3.55.9.4.5"><h4>File Header</h4><p>478 The file header consists of 15 bytes of fixed fields, followed479 by a variable-length header extension area. The fixed fields are:480 481 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Signature</span></dt><dd><p>48211-byte sequence <code class="literal">PGCOPY\n\377\r\n\0</code> — note that the zero byte483is a required part of the signature. (The signature is designed to allow484easy identification of files that have been munged by a non-8-bit-clean485transfer. This signature will be changed by end-of-line-translation486filters, dropped zero bytes, dropped high bits, or parity changes.)487 </p></dd><dt><span class="term">Flags field</span></dt><dd><p>48832-bit integer bit mask to denote important aspects of the file format. Bits489are numbered from 0 (<acronym class="acronym">LSB</acronym>) to 31 (<acronym class="acronym">MSB</acronym>). Note that490this field is stored in network byte order (most significant byte first),491as are all the integer fields used in the file format. Bits49216–31 are reserved to denote critical file format issues; a reader493should abort if it finds an unexpected bit set in this range. Bits 0–15494are reserved to signal backwards-compatible format issues; a reader495should simply ignore any unexpected bits set in this range. Currently496only one flag bit is defined, and the rest must be zero:497 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Bit 16</span></dt><dd><p>498 If 1, OIDs are included in the data; if 0, not. Oid system columns499 are not supported in <span class="productname">PostgreSQL</span>500 anymore, but the format still contains the indicator.501 </p></dd></dl></div></dd><dt><span class="term">Header extension area length</span></dt><dd><p>50232-bit integer, length in bytes of remainder of header, not including self.503Currently, this is zero, and the first tuple follows504immediately. Future changes to the format might allow additional data505to be present in the header. A reader should silently skip over any header506extension data it does not know what to do with.507 </p></dd></dl></div><p>508 </p><p>509The header extension area is envisioned to contain a sequence of510self-identifying chunks. The flags field is not intended to tell readers511what is in the extension area. Specific design of header extension contents512is left for a later release.513 </p><p>514 This design allows for both backwards-compatible header additions (add515 header extension chunks, or set low-order flag bits) and516 non-backwards-compatible changes (set high-order flag bits to signal such517 changes, and add supporting data to the extension area if needed).518 </p></div><div class="refsect3" id="id-1.9.3.55.9.4.6"><h4>Tuples</h4><p>519Each tuple begins with a 16-bit integer count of the number of fields in the520tuple. (Presently, all tuples in a table will have the same count, but that521might not always be true.) Then, repeated for each field in the tuple, there522is a 32-bit length word followed by that many bytes of field data. (The523length word does not include itself, and can be zero.) As a special case,524-1 indicates a NULL field value. No value bytes follow in the NULL case.525 </p><p>526There is no alignment padding or any other extra data between fields.527 </p><p>528Presently, all data values in a binary-format file are529assumed to be in binary format (format code one). It is anticipated that a530future extension might add a header field that allows per-column format codes531to be specified.532 </p><p>533To determine the appropriate binary format for the actual tuple data you534should consult the <span class="productname">PostgreSQL</span> source, in535particular the <code class="function">*send</code> and <code class="function">*recv</code> functions for536each column's data type (typically these functions are found in the537<code class="filename">src/backend/utils/adt/</code> directory of the source538distribution).539 </p><p>540If OIDs are included in the file, the OID field immediately follows the541field-count word. It is a normal field except that it's not included in the542field-count. Note that oid system columns are not supported in current543versions of <span class="productname">PostgreSQL</span>.544 </p></div><div class="refsect3" id="id-1.9.3.55.9.4.7"><h4>File Trailer</h4><p>545 The file trailer consists of a 16-bit integer word containing -1. This546 is easily distinguished from a tuple's field-count word.547 </p><p>548 A reader should report an error if a field-count word is neither -1549 nor the expected number of columns. This provides an extra550 check against somehow getting out of sync with the data.551 </p></div></div></div><div class="refsect1" id="id-1.9.3.55.10"><h2>Examples</h2><p>552 The following example copies a table to the client553 using the vertical bar (<code class="literal">|</code>) as the field delimiter:554</p><pre class="programlisting">555COPY country TO STDOUT (DELIMITER '|');556</pre><p>557 </p><p>558 To copy data from a file into the <code class="literal">country</code> table:559</p><pre class="programlisting">560COPY country FROM '/usr1/proj/bray/sql/country_data';561</pre><p>562 </p><p>563 To copy into a file just the countries whose names start with 'A':564</p><pre class="programlisting">565COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';566</pre><p>567 </p><p>568 To copy into a compressed file, you can pipe the output through an external569 compression program:570</p><pre class="programlisting">571COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';572</pre><p>573 </p><p>574 Here is a sample of data suitable for copying into a table from575 <code class="literal">STDIN</code>:576</p><pre class="programlisting">577AF AFGHANISTAN578AL ALBANIA579DZ ALGERIA580ZM ZAMBIA581ZW ZIMBABWE582</pre><p>583 Note that the white space on each line is actually a tab character.584 </p><p>585 The following is the same data, output in binary format.586 The data is shown after filtering through the587 Unix utility <code class="command">od -c</code>. The table has three columns;588 the first has type <code class="type">char(2)</code>, the second has type <code class="type">text</code>,589 and the third has type <code class="type">integer</code>. All the rows have a null value590 in the third column.591</p><pre class="programlisting">5920000000 P G C O P Y \n 377 \r \n \0 \0 \0 \0 \0 \05930000020 \0 \0 \0 \0 003 \0 \0 \0 002 A F \0 \0 \0 013 A5940000040 F G H A N I S T A N 377 377 377 377 \0 0035950000060 \0 \0 \0 002 A L \0 \0 \0 007 A L B A N I5960000100 A 377 377 377 377 \0 003 \0 \0 \0 002 D Z \0 \0 \05970000120 007 A L G E R I A 377 377 377 377 \0 003 \0 \05980000140 \0 002 Z M \0 \0 \0 006 Z A M B I A 377 3775990000160 377 377 \0 003 \0 \0 \0 002 Z W \0 \0 \0 \b Z I6000000200 M B A B W E 377 377 377 377 377 377601</pre></div><div class="refsect1" id="id-1.9.3.55.11"><h2>Compatibility</h2><p>602 There is no <code class="command">COPY</code> statement in the SQL standard.603 </p><p>604 The following syntax was used before <span class="productname">PostgreSQL</span>605 version 9.0 and is still supported:606 607</p><pre class="synopsis">608COPY <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ]609 FROM { '<em class="replaceable"><code>filename</code></em>' | STDIN }610 [ [ WITH ]611 [ BINARY ]612 [ DELIMITER [ AS ] '<em class="replaceable"><code>delimiter_character</code></em>' ]613 [ NULL [ AS ] '<em class="replaceable"><code>null_string</code></em>' ]614 [ CSV [ HEADER ]615 [ QUOTE [ AS ] '<em class="replaceable"><code>quote_character</code></em>' ]616 [ ESCAPE [ AS ] '<em class="replaceable"><code>escape_character</code></em>' ]617 [ FORCE NOT NULL <em class="replaceable"><code>column_name</code></em> [, ...] ] ] ]618 619COPY { <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] | ( <em class="replaceable"><code>query</code></em> ) }620 TO { '<em class="replaceable"><code>filename</code></em>' | STDOUT }621 [ [ WITH ]622 [ BINARY ]623 [ DELIMITER [ AS ] '<em class="replaceable"><code>delimiter_character</code></em>' ]624 [ NULL [ AS ] '<em class="replaceable"><code>null_string</code></em>' ]625 [ CSV [ HEADER ]626 [ QUOTE [ AS ] '<em class="replaceable"><code>quote_character</code></em>' ]627 [ ESCAPE [ AS ] '<em class="replaceable"><code>escape_character</code></em>' ]628 [ FORCE QUOTE { <em class="replaceable"><code>column_name</code></em> [, ...] | * } ] ] ]629</pre><p>630 631 Note that in this syntax, <code class="literal">BINARY</code> and <code class="literal">CSV</code> are632 treated as independent keywords, not as arguments of a <code class="literal">FORMAT</code>633 option.634 </p><p>635 The following syntax was used before <span class="productname">PostgreSQL</span>636 version 7.3 and is still supported:637 638</p><pre class="synopsis">639COPY [ BINARY ] <em class="replaceable"><code>table_name</code></em>640 FROM { '<em class="replaceable"><code>filename</code></em>' | STDIN }641 [ [USING] DELIMITERS '<em class="replaceable"><code>delimiter_character</code></em>' ]642 [ WITH NULL AS '<em class="replaceable"><code>null_string</code></em>' ]643 644COPY [ BINARY ] <em class="replaceable"><code>table_name</code></em>645 TO { '<em class="replaceable"><code>filename</code></em>' | STDOUT }646 [ [USING] DELIMITERS '<em class="replaceable"><code>delimiter_character</code></em>' ]647 [ WITH NULL AS '<em class="replaceable"><code>null_string</code></em>' ]648</pre></div><div class="refsect1" id="id-1.9.3.55.12"><h2>See Also</h2><span class="simplelist"><a class="xref" href="progress-reporting.html#COPY-PROGRESS-REPORTING" title="28.4.3. COPY Progress Reporting">Section 28.4.3</a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-commit-prepared.html" title="COMMIT PREPARED">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-create-access-method.html" title="CREATE ACCESS METHOD">Next</a></td></tr><tr><td width="40%" align="left" valign="top">COMMIT PREPARED </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE ACCESS METHOD</td></tr></table></div></body></html>