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 18.0 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">
3 COPY <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> ]
8 COPY { <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> [, ...] ) ]
12 <span class="phrase">where <em class="replaceable"><code>option</code></em> can be one of:</span>
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 ON_ERROR <em class="replaceable"><code>error_action</code></em>
26 REJECT_LIMIT <em class="replaceable"><code>maxerror</code></em>
27 ENCODING '<em class="replaceable"><code>encoding_name</code></em>'
28 LOG_VERBOSITY <em class="replaceable"><code>verbosity</code></em>
29 </pre></div><div class="refsect1" id="id-1.9.3.55.5"><h2>Description</h2><p>
30 <code class="command">COPY</code> moves data between
31 <span class="productname">PostgreSQL</span> tables and standard file-system
32 files. <code class="command">COPY TO</code> copies the contents of a table
33 <span class="emphasis"><em>to</em></span> a file, while <code class="command">COPY FROM</code> copies
34 data <span class="emphasis"><em>from</em></span> a file to a table (appending the data to
35 whatever is in the table already). <code class="command">COPY TO</code>
36 can also copy the results of a <code class="command">SELECT</code> query.
38 If a column list is specified, <code class="command">COPY TO</code> copies only
39 the data in the specified columns to the file. For <code class="command">COPY
40 FROM</code>, each field in the file is inserted, in order, into the
41 specified column. Table columns not specified in the <code class="command">COPY
42 FROM</code> column list will receive their default values.
44 <code class="command">COPY</code> with a file name instructs the
45 <span class="productname">PostgreSQL</span> server to directly read from
46 or write to a file. The file must be accessible by the
47 <span class="productname">PostgreSQL</span> user (the user ID the server
48 runs as) and the name must be specified from the viewpoint of the
49 server. When <code class="literal">PROGRAM</code> is specified, the server
50 executes the given command and reads from the standard output of the
51 program, or writes to the standard input of the program. The command
52 must be specified from the viewpoint of the server, and be executable
53 by the <span class="productname">PostgreSQL</span> user. When
54 <code class="literal">STDIN</code> or <code class="literal">STDOUT</code> is
55 specified, data is transmitted via the connection between the
56 client and the server.
58 Each backend running <code class="command">COPY</code> will report its progress
59 in the <code class="structname">pg_stat_progress_copy</code> view. See
60 <a class="xref" href="progress-reporting.html#COPY-PROGRESS-REPORTING" title="27.4.3. COPY Progress Reporting">Section 27.4.3</a> for details.
62 By default, <code class="command">COPY</code> will fail if it encounters an error
63 during processing. For use cases where a best-effort attempt at loading
64 the entire file is desired, the <code class="literal">ON_ERROR</code> clause can
65 be used to specify some other behavior.
66 </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>
67 The name (optionally schema-qualified) of an existing table.
68 </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
69 An optional list of columns to be copied. If no column list is
70 specified, all columns of the table except generated columns will be
72 </p></dd><dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt><dd><p>
73 A <a class="link" href="sql-select.html" title="SELECT"><code class="command">SELECT</code></a>,
74 <a class="link" href="sql-values.html" title="VALUES"><code class="command">VALUES</code></a>,
75 <a class="link" href="sql-insert.html" title="INSERT"><code class="command">INSERT</code></a>,
76 <a class="link" href="sql-update.html" title="UPDATE"><code class="command">UPDATE</code></a>,
77 <a class="link" href="sql-delete.html" title="DELETE"><code class="command">DELETE</code></a>, or
78 <a class="link" href="sql-merge.html" title="MERGE"><code class="command">MERGE</code></a> command
79 whose results are to be copied. Note that parentheses are required
82 For <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
83 <code class="command">DELETE</code>, and <code class="command">MERGE</code> queries a
84 <code class="literal">RETURNING</code> clause must be provided, and the target
85 relation must not have a conditional rule, nor an
86 <code class="literal">ALSO</code> rule, nor an <code class="literal">INSTEAD</code> rule
87 that expands to multiple statements.
88 </p></dd><dt><span class="term"><em class="replaceable"><code>filename</code></em></span></dt><dd><p>
89 The path name of the input or output file. An input file name can be
90 an absolute or relative path, but an output file name must be an absolute
91 path. Windows users might need to use an <code class="literal">E''</code> string and
92 double any backslashes used in the path name.
93 </p></dd><dt><span class="term"><code class="literal">PROGRAM</code></span></dt><dd><p>
94 A command to execute. In <code class="command">COPY FROM</code>, the input is
95 read from standard output of the command, and in <code class="command">COPY TO</code>,
96 the output is written to the standard input of the command.
98 Note that the command is invoked by the shell, so if you need to pass
99 any arguments that come from an untrusted source, you
100 must be careful to strip or escape any special characters that might
101 have a special meaning for the shell. For security reasons, it is best
102 to use a fixed command string, or at least avoid including any user input
104 </p></dd><dt><span class="term"><code class="literal">STDIN</code></span></dt><dd><p>
105 Specifies that input comes from the client application.
106 </p></dd><dt><span class="term"><code class="literal">STDOUT</code></span></dt><dd><p>
107 Specifies that output goes to the client application.
108 </p></dd><dt><span class="term"><em class="replaceable"><code>boolean</code></em></span></dt><dd><p>
109 Specifies whether the selected option should be turned on or off.
110 You can write <code class="literal">TRUE</code>, <code class="literal">ON</code>, or
111 <code class="literal">1</code> to enable the option, and <code class="literal">FALSE</code>,
112 <code class="literal">OFF</code>, or <code class="literal">0</code> to disable it. The
113 <em class="replaceable"><code>boolean</code></em> value can also
114 be omitted, in which case <code class="literal">TRUE</code> is assumed.
115 </p></dd><dt><span class="term"><code class="literal">FORMAT</code></span></dt><dd><p>
116 Selects the data format to be read or written:
117 <code class="literal">text</code>,
118 <code class="literal">csv</code> (Comma Separated Values),
119 or <code class="literal">binary</code>.
120 The default is <code class="literal">text</code>.
121 See <a class="xref" href="sql-copy.html#SQL-COPY-FILE-FORMATS" title="File Formats">File Formats</a> below for details.
122 </p></dd><dt><span class="term"><code class="literal">FREEZE</code></span></dt><dd><p>
123 Requests copying the data with rows already frozen, just as they
124 would be after running the <code class="command">VACUUM FREEZE</code> command.
125 This is intended as a performance option for initial data loading.
126 Rows will be frozen only if the table being loaded has been created
127 or truncated in the current subtransaction, there are no cursors
128 open and there are no older snapshots held by this transaction. It is
129 currently not possible to perform a <code class="command">COPY FREEZE</code> on
130 a partitioned table or foreign table.
131 This option is only allowed in <code class="command">COPY FROM</code>.
133 Note that all other sessions will immediately be able to see the data
134 once it has been successfully loaded. This violates the normal rules
135 of MVCC visibility and users should be aware of the
136 potential problems this might cause.
137 </p></dd><dt><span class="term"><code class="literal">DELIMITER</code></span></dt><dd><p>
138 Specifies the character that separates columns within each row
139 (line) of the file. The default is a tab character in text format,
140 a comma in <code class="literal">CSV</code> format.
141 This must be a single one-byte character.
142 This option is not allowed when using <code class="literal">binary</code> format.
143 </p></dd><dt><span class="term"><code class="literal">NULL</code></span></dt><dd><p>
144 Specifies the string that represents a null value. The default is
145 <code class="literal">\N</code> (backslash-N) in text format, and an unquoted empty
146 string in <code class="literal">CSV</code> format. You might prefer an
147 empty string even in text format for cases where you don't want to
148 distinguish nulls from empty strings.
149 This option is not allowed when using <code class="literal">binary</code> format.
150 </p><div class="note"><h3 class="title">Note</h3><p>
151 When using <code class="command">COPY FROM</code>, any data item that matches
152 this string will be stored as a null value, so you should make
153 sure that you use the same string as you used with
154 <code class="command">COPY TO</code>.
155 </p></div></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>
156 Specifies the string that represents a default value. Each time the string
157 is found in the input file, the default value of the corresponding column
159 This option is allowed only in <code class="command">COPY FROM</code>, and only when
160 not using <code class="literal">binary</code> format.
161 </p></dd><dt><span class="term"><code class="literal">HEADER</code></span></dt><dd><p>
162 Specifies that the file contains a header line with the names of each
163 column in the file. On output, the first line contains the column
164 names from the table. On input, the first line is discarded when this
165 option is set to <code class="literal">true</code> (or equivalent Boolean value).
166 If this option is set to <code class="literal">MATCH</code>, the number and names
167 of the columns in the header line must match the actual column names of
168 the table, in order; otherwise an error is raised.
169 This option is not allowed when using <code class="literal">binary</code> format.
170 The <code class="literal">MATCH</code> option is only valid for <code class="command">COPY
171 FROM</code> commands.
172 </p></dd><dt><span class="term"><code class="literal">QUOTE</code></span></dt><dd><p>
173 Specifies the quoting character to be used when a data value is quoted.
174 The default is double-quote.
175 This must be a single one-byte character.
176 This option is allowed only when using <code class="literal">CSV</code> format.
177 </p></dd><dt><span class="term"><code class="literal">ESCAPE</code></span></dt><dd><p>
178 Specifies the character that should appear before a
179 data character that matches the <code class="literal">QUOTE</code> value.
180 The default is the same as the <code class="literal">QUOTE</code> value (so that
181 the quoting character is doubled if it appears in the data).
182 This must be a single one-byte character.
183 This option is allowed only when using <code class="literal">CSV</code> format.
184 </p></dd><dt><span class="term"><code class="literal">FORCE_QUOTE</code></span></dt><dd><p>
186 used for all non-<code class="literal">NULL</code> values in each specified column.
187 <code class="literal">NULL</code> output is never quoted. If <code class="literal">*</code> is specified,
188 non-<code class="literal">NULL</code> values will be quoted in all columns.
189 This option is allowed only in <code class="command">COPY TO</code>, and only when
190 using <code class="literal">CSV</code> format.
191 </p></dd><dt><span class="term"><code class="literal">FORCE_NOT_NULL</code></span></dt><dd><p>
192 Do not match the specified columns' values against the null string.
193 In the default case where the null string is empty, this means that
194 empty values will be read as zero-length strings rather than nulls,
195 even when they are not quoted.
196 If <code class="literal">*</code> is specified, the option will be applied to all columns.
197 This option is allowed only in <code class="command">COPY FROM</code>, and only when
198 using <code class="literal">CSV</code> format.
199 </p></dd><dt><span class="term"><code class="literal">FORCE_NULL</code></span></dt><dd><p>
200 Match the specified columns' values against the null string, even
201 if it has been quoted, and if a match is found set the value to
202 <code class="literal">NULL</code>. In the default case where the null string is empty,
203 this converts a quoted empty string into NULL.
204 If <code class="literal">*</code> is specified, the option will be applied to all columns.
205 This option is allowed only in <code class="command">COPY FROM</code>, and only when
206 using <code class="literal">CSV</code> format.
207 </p></dd><dt><span class="term"><code class="literal">ON_ERROR</code></span></dt><dd><p>
208 Specifies how to behave when encountering an error converting a column's
209 input value into its data type.
210 An <em class="replaceable"><code>error_action</code></em> value of
211 <code class="literal">stop</code> means fail the command, while
212 <code class="literal">ignore</code> means discard the input row and continue with the next one.
213 The default is <code class="literal">stop</code>.
215 The <code class="literal">ignore</code> option is applicable only for <code class="command">COPY FROM</code>
216 when the <code class="literal">FORMAT</code> is <code class="literal">text</code> or <code class="literal">csv</code>.
218 A <code class="literal">NOTICE</code> message containing the ignored row count is
219 emitted at the end of the <code class="command">COPY FROM</code> if at least one
220 row was discarded. When <code class="literal">LOG_VERBOSITY</code> option is set to
221 <code class="literal">verbose</code>, a <code class="literal">NOTICE</code> message
222 containing the line of the input file and the column name whose input
223 conversion has failed is emitted for each discarded row.
224 When it is set to <code class="literal">silent</code>, no message is emitted
225 regarding ignored rows.
226 </p></dd><dt><span class="term"><code class="literal">REJECT_LIMIT</code></span></dt><dd><p>
227 Specifies the maximum number of errors tolerated while converting a
228 column's input value to its data type, when <code class="literal">ON_ERROR</code> is
229 set to <code class="literal">ignore</code>.
230 If the input causes more errors than the specified value, the <code class="command">COPY</code>
231 command fails, even with <code class="literal">ON_ERROR</code> set to <code class="literal">ignore</code>.
232 This clause must be used with <code class="literal">ON_ERROR</code>=<code class="literal">ignore</code>
233 and <em class="replaceable"><code>maxerror</code></em> must be positive <code class="type">bigint</code>.
234 If not specified, <code class="literal">ON_ERROR</code>=<code class="literal">ignore</code>
235 allows an unlimited number of errors, meaning <code class="command">COPY</code> will
236 skip all erroneous data.
237 </p></dd><dt><span class="term"><code class="literal">ENCODING</code></span></dt><dd><p>
238 Specifies that the file is encoded in the <em class="replaceable"><code>encoding_name</code></em>. If this option is
239 omitted, the current client encoding is used. See the Notes below
241 </p></dd><dt><span class="term"><code class="literal">LOG_VERBOSITY</code></span></dt><dd><p>
242 Specifies the amount of messages emitted by a <code class="command">COPY</code>
243 command: <code class="literal">default</code>, <code class="literal">verbose</code>, or
244 <code class="literal">silent</code>.
245 If <code class="literal">verbose</code> is specified, additional messages are
246 emitted during processing.
247 <code class="literal">silent</code> suppresses both verbose and default messages.
249 This is currently used in <code class="command">COPY FROM</code> command when
250 <code class="literal">ON_ERROR</code> option is set to <code class="literal">ignore</code>.
251 </p></dd><dt><span class="term"><code class="literal">WHERE</code></span></dt><dd><p>
252 The optional <code class="literal">WHERE</code> clause has the general form
253 </p><pre class="synopsis">
254 WHERE <em class="replaceable"><code>condition</code></em>
256 where <em class="replaceable"><code>condition</code></em> is
257 any expression that evaluates to a result of type
258 <code class="type">boolean</code>. Any row that does not satisfy this
259 condition will not be inserted to the table. A row satisfies the
260 condition if it returns true when the actual row values are
261 substituted for any variable references.
263 Currently, subqueries are not allowed in <code class="literal">WHERE</code>
264 expressions, and the evaluation does not see any changes made by the
265 <code class="command">COPY</code> itself (this matters when the expression
266 contains calls to <code class="literal">VOLATILE</code> functions).
267 </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.55.7"><h2>Outputs</h2><p>
268 On successful completion, a <code class="command">COPY</code> command returns a command
270 </p><pre class="screen">
271 COPY <em class="replaceable"><code>count</code></em>
273 The <em class="replaceable"><code>count</code></em> is the number
275 </p><div class="note"><h3 class="title">Note</h3><p>
276 <span class="application">psql</span> will print this command tag only if the command
277 was not <code class="literal">COPY ... TO STDOUT</code>, or the
278 equivalent <span class="application">psql</span> meta-command
279 <code class="literal">\copy ... to stdout</code>. This is to prevent confusing the
280 command tag with the data that was just printed.
281 </p></div></div><div class="refsect1" id="id-1.9.3.55.8"><h2>Notes</h2><p>
282 <code class="command">COPY TO</code> can be used with plain
283 tables and populated materialized views.
285 <code class="literal">COPY <em class="replaceable"><code>table</code></em>
286 TO</code> copies the same rows as
287 <code class="literal">SELECT * FROM ONLY <em class="replaceable"><code>table</code></em></code>.
288 However it doesn't directly support other relation types,
289 such as partitioned tables, inheritance child tables, or views.
290 To copy all rows from such relations, use <code class="literal">COPY (SELECT * FROM
291 <em class="replaceable"><code>table</code></em>) TO</code>.
293 <code class="command">COPY FROM</code> can be used with plain, foreign, or
294 partitioned tables or with views that have
295 <code class="literal">INSTEAD OF INSERT</code> triggers.
297 You must have select privilege on the table
298 whose values are read by <code class="command">COPY TO</code>, and
299 insert privilege on the table into which values
300 are inserted by <code class="command">COPY FROM</code>. It is sufficient
301 to have column privileges on the column(s) listed in the command.
303 If row-level security is enabled for the table, the relevant
304 <code class="command">SELECT</code> policies will apply to <code class="literal">COPY
305 <em class="replaceable"><code>table</code></em> TO</code> statements.
306 Currently, <code class="command">COPY FROM</code> is not supported for tables
307 with row-level security. Use equivalent <code class="command">INSERT</code>
310 Files named in a <code class="command">COPY</code> command are read or written
311 directly by the server, not by the client application. Therefore,
312 they must reside on or be accessible to the database server machine,
313 not the client. They must be accessible to and readable or writable
314 by the <span class="productname">PostgreSQL</span> user (the user ID the
315 server runs as), not the client. Similarly,
316 the command specified with <code class="literal">PROGRAM</code> is executed directly
317 by the server, not by the client application, must be executable by the
318 <span class="productname">PostgreSQL</span> user.
319 <code class="command">COPY</code> naming a file or command is only allowed to
320 database superusers or users who are granted one of the roles
321 <code class="literal">pg_read_server_files</code>,
322 <code class="literal">pg_write_server_files</code>,
323 or <code class="literal">pg_execute_server_program</code>, since it allows reading
324 or writing any file or running a program that the server has privileges to
327 Do not confuse <code class="command">COPY</code> with the
328 <span class="application">psql</span> instruction
329 <code class="command"><a class="link" href="app-psql.html#APP-PSQL-META-COMMANDS-COPY">\copy</a></code>. <code class="command">\copy</code> invokes
330 <code class="command">COPY FROM STDIN</code> or <code class="command">COPY TO
331 STDOUT</code>, and then fetches/stores the data in a file
332 accessible to the <span class="application">psql</span> client. Thus,
333 file accessibility and access rights depend on the client rather
334 than the server when <code class="command">\copy</code> is used.
336 It is recommended that the file name used in <code class="command">COPY</code>
337 always be specified as an absolute path. This is enforced by the
338 server in the case of <code class="command">COPY TO</code>, but for
339 <code class="command">COPY FROM</code> you do have the option of reading from
340 a file specified by a relative path. The path will be interpreted
341 relative to the working directory of the server process (normally
342 the cluster's data directory), not the client's working directory.
344 Executing a command with <code class="literal">PROGRAM</code> might be restricted
345 by the operating system's access control mechanisms, such as SELinux.
347 <code class="command">COPY FROM</code> will invoke any triggers and check
348 constraints on the destination table. However, it will not invoke rules.
350 For identity columns, the <code class="command">COPY FROM</code> command will always
351 write the column values provided in the input data, like
352 the <code class="command">INSERT</code> option <code class="literal">OVERRIDING SYSTEM
355 <code class="command">COPY</code> input and output is affected by
356 <code class="varname">DateStyle</code>. To ensure portability to other
357 <span class="productname">PostgreSQL</span> installations that might use
358 non-default <code class="varname">DateStyle</code> settings,
359 <code class="varname">DateStyle</code> should be set to <code class="literal">ISO</code> before
360 using <code class="command">COPY TO</code>. It is also a good idea to avoid dumping
361 data with <code class="varname">IntervalStyle</code> set to
362 <code class="literal">sql_standard</code>, because negative interval values might be
363 misinterpreted by a server that has a different setting for
364 <code class="varname">IntervalStyle</code>.
366 Input data is interpreted according to <code class="literal">ENCODING</code>
367 option or the current client encoding, and output data is encoded
368 in <code class="literal">ENCODING</code> or the current client encoding, even
369 if the data does not pass through the client but is read from or
370 written to a file directly by the server.
372 The <code class="command">COPY FROM</code> command physically inserts input rows
373 into the table as it progresses. If the command fails, these rows are
374 left in a deleted state; these rows will not be visible, but still
375 occupy disk space. This might amount to considerable
376 wasted disk space if the failure happened well into a large copy
377 operation. <code class="command">VACUUM</code> should be used to recover the
380 <code class="literal">FORCE_NULL</code> and <code class="literal">FORCE_NOT_NULL</code> can be used
381 simultaneously on the same column. This results in converting quoted
382 null strings to null values and unquoted null strings to empty strings.
383 </p></div><div class="refsect1" id="SQL-COPY-FILE-FORMATS"><h2>File Formats</h2><div class="refsect2" id="SQL-COPY-TEXT-FORMAT"><h3>Text Format</h3><p>
384 When the <code class="literal">text</code> format is used,
385 the data read or written is a text file with one line per table row.
386 Columns in a row are separated by the delimiter character.
387 The column values themselves are strings generated by the
388 output function, or acceptable to the input function, of each
389 attribute's data type. The specified null string is used in
390 place of columns that are null.
391 <code class="command">COPY FROM</code> will raise an error if any line of the
392 input file contains more or fewer columns than are expected.
394 End of data can be represented by a line containing just
395 backslash-period (<code class="literal">\.</code>). An end-of-data marker is
396 not necessary when reading from a file, since the end of file
397 serves perfectly well; in that context this provision exists only for
398 backward compatibility. However, <span class="application">psql</span>
399 uses <code class="literal">\.</code> to terminate a <code class="literal">COPY FROM
400 STDIN</code> operation (that is, reading
401 in-line <code class="command">COPY</code> data in an SQL script). In that
402 context the rule is needed to be able to end the operation before the
405 Backslash characters (<code class="literal">\</code>) can be used in the
406 <code class="command">COPY</code> data to quote data characters that might
407 otherwise be taken as row or column delimiters. In particular, the
408 following characters <span class="emphasis"><em>must</em></span> be preceded by a backslash if
409 they appear as part of a column value: backslash itself,
410 newline, carriage return, and the current delimiter character.
412 The specified null string is sent by <code class="command">COPY TO</code> without
413 adding any backslashes; conversely, <code class="command">COPY FROM</code> matches
414 the input against the null string before removing backslashes. Therefore,
415 a null string such as <code class="literal">\N</code> cannot be confused with
416 the actual data value <code class="literal">\N</code> (which would be represented
417 as <code class="literal">\\N</code>).
419 The following special backslash sequences are recognized by
420 <code class="command">COPY FROM</code>:
422 </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 specifies
423 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 specifies
424 the byte with that numeric code</td></tr></tbody></table></div><p>
426 Presently, <code class="command">COPY TO</code> will never emit an octal or
427 hex-digits backslash sequence, but it does use the other sequences
428 listed above for those control characters.
430 Any other backslashed character that is not mentioned in the above table
431 will be taken to represent itself. However, beware of adding backslashes
432 unnecessarily, since that might accidentally produce a string matching the
433 end-of-data marker (<code class="literal">\.</code>) or the null string (<code class="literal">\N</code> by
434 default). These strings will be recognized before any other backslash
437 It is strongly recommended that applications generating <code class="command">COPY</code> data convert
438 data newlines and carriage returns to the <code class="literal">\n</code> and
439 <code class="literal">\r</code> sequences respectively. At present it is
440 possible to represent a data carriage return by a backslash and carriage
441 return, and to represent a data newline by a backslash and newline.
442 However, these representations might not be accepted in future releases.
443 They are also highly vulnerable to corruption if the <code class="command">COPY</code> file is
444 transferred across different machines (for example, from Unix to Windows
447 All backslash sequences are interpreted after encoding conversion.
448 The bytes specified with the octal and hex-digit backslash sequences must
449 form valid characters in the database encoding.
451 <code class="command">COPY TO</code> will terminate each row with a Unix-style
452 newline (<span class="quote">“<span class="quote"><code class="literal">\n</code></span>”</span>). Servers running on Microsoft Windows instead
453 output carriage return/newline (<span class="quote">“<span class="quote"><code class="literal">\r\n</code></span>”</span>), but only for
454 <code class="command">COPY</code> to a server file; for consistency across platforms,
455 <code class="command">COPY TO STDOUT</code> always sends <span class="quote">“<span class="quote"><code class="literal">\n</code></span>”</span>
456 regardless of server platform.
457 <code class="command">COPY FROM</code> can handle lines ending with newlines,
458 carriage returns, or carriage return/newlines. To reduce the risk of
459 error due to un-backslashed newlines or carriage returns that were
460 meant as data, <code class="command">COPY FROM</code> will complain if the line
461 endings in the input are not all alike.
462 </p></div><div class="refsect2" id="SQL-COPY-CSV-FORMAT"><h3>CSV Format</h3><p>
463 This format option is used for importing and exporting the Comma-
464 Separated Value (<code class="literal">CSV</code>) file format used by many other
465 programs, such as spreadsheets. Instead of the escaping rules used by
466 <span class="productname">PostgreSQL</span>'s standard text format, it
467 produces and recognizes the common <code class="literal">CSV</code> escaping mechanism.
469 The values in each record are separated by the <code class="literal">DELIMITER</code>
470 character. If the value contains the delimiter character, the
471 <code class="literal">QUOTE</code> character, the <code class="literal">NULL</code> string, a carriage
472 return, or line feed character, then the whole value is prefixed and
473 suffixed by the <code class="literal">QUOTE</code> character, and any occurrence
474 within the value of a <code class="literal">QUOTE</code> character or the
475 <code class="literal">ESCAPE</code> character is preceded by the escape character.
476 You can also use <code class="literal">FORCE_QUOTE</code> to force quotes when outputting
477 non-<code class="literal">NULL</code> values in specific columns.
479 The <code class="literal">CSV</code> format has no standard way to distinguish a
480 <code class="literal">NULL</code> value from an empty string.
481 <span class="productname">PostgreSQL</span>'s <code class="command">COPY</code> handles this by quoting.
482 A <code class="literal">NULL</code> is output as the <code class="literal">NULL</code> parameter string
483 and is not quoted, while a non-<code class="literal">NULL</code> value matching the
484 <code class="literal">NULL</code> parameter string is quoted. For example, with the
485 default settings, a <code class="literal">NULL</code> is written as an unquoted empty
486 string, while an empty string data value is written with double quotes
487 (<code class="literal">""</code>). Reading values follows similar rules. You can
488 use <code class="literal">FORCE_NOT_NULL</code> to prevent <code class="literal">NULL</code> input
489 comparisons for specific columns. You can also use
490 <code class="literal">FORCE_NULL</code> to convert quoted null string data values to
491 <code class="literal">NULL</code>.
493 Because backslash is not a special character in the <code class="literal">CSV</code>
494 format, the end-of-data marker used in text mode (<code class="literal">\.</code>)
495 is not normally treated as special when reading <code class="literal">CSV</code>
496 data. An exception is that <span class="application">psql</span> will terminate
497 a <code class="literal">COPY FROM STDIN</code> operation (that is, reading
498 in-line <code class="command">COPY</code> data in an SQL script) at a line containing
499 only <code class="literal">\.</code>, whether it is text or <code class="literal">CSV</code>
501 </p><div class="note"><h3 class="title">Note</h3><p>
502 <span class="productname">PostgreSQL</span> versions before v18 always
503 recognized unquoted <code class="literal">\.</code> as an end-of-data marker,
504 even when reading from a separate file. For compatibility with older
505 versions, <code class="command">COPY TO</code> will quote <code class="literal">\.</code>
506 when it's alone on a line, even though this is no longer necessary.
507 </p></div><div class="note"><h3 class="title">Note</h3><p>
508 In <code class="literal">CSV</code> format, all characters are significant. A quoted value
509 surrounded by white space, or any characters other than
510 <code class="literal">DELIMITER</code>, will include those characters. This can cause
511 errors if you import data from a system that pads <code class="literal">CSV</code>
512 lines with white space out to some fixed width. If such a situation
513 arises you might need to preprocess the <code class="literal">CSV</code> file to remove
514 the trailing white space, before importing the data into
515 <span class="productname">PostgreSQL</span>.
516 </p></div><div class="note"><h3 class="title">Note</h3><p>
517 <code class="literal">CSV</code> format will both recognize and produce <code class="literal">CSV</code> files with quoted
518 values containing embedded carriage returns and line feeds. Thus
519 the files are not strictly one line per table row like text-format
521 </p></div><div class="note"><h3 class="title">Note</h3><p>
522 Many programs produce strange and occasionally perverse <code class="literal">CSV</code> files,
523 so the file format is more a convention than a standard. Thus you
524 might encounter some files that cannot be imported using this
525 mechanism, and <code class="command">COPY</code> might produce files that other
526 programs cannot process.
527 </p></div></div><div class="refsect2" id="SQL-COPY-BINARY-FORMAT"><h3>Binary Format</h3><p>
528 The <code class="literal">binary</code> format option causes all data to be
529 stored/read as binary format rather than as text. It is
530 somewhat faster than the text and <code class="literal">CSV</code> formats,
531 but a binary-format file is less portable across machine architectures and
532 <span class="productname">PostgreSQL</span> versions.
533 Also, the binary format is very data type specific; for example
534 it will not work to output binary data from a <code class="type">smallint</code> column
535 and read it into an <code class="type">integer</code> column, even though that would work
538 The <code class="literal">binary</code> file format consists
539 of a file header, zero or more tuples containing the row data, and
540 a file trailer. Headers and data are in network byte order.
541 </p><div class="note"><h3 class="title">Note</h3><p>
542 <span class="productname">PostgreSQL</span> releases before 7.4 used a
543 different binary file format.
544 </p></div><div class="refsect3" id="id-1.9.3.55.9.4.5"><h4>File Header</h4><p>
545 The file header consists of 15 bytes of fixed fields, followed
546 by a variable-length header extension area. The fixed fields are:
548 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Signature</span></dt><dd><p>
549 11-byte sequence <code class="literal">PGCOPY\n\377\r\n\0</code> — note that the zero byte
550 is a required part of the signature. (The signature is designed to allow
551 easy identification of files that have been munged by a non-8-bit-clean
552 transfer. This signature will be changed by end-of-line-translation
553 filters, dropped zero bytes, dropped high bits, or parity changes.)
554 </p></dd><dt><span class="term">Flags field</span></dt><dd><p>
555 32-bit integer bit mask to denote important aspects of the file format. Bits
556 are numbered from 0 (<acronym class="acronym">LSB</acronym>) to 31 (<acronym class="acronym">MSB</acronym>). Note that
557 this field is stored in network byte order (most significant byte first),
558 as are all the integer fields used in the file format. Bits
559 16–31 are reserved to denote critical file format issues; a reader
560 should abort if it finds an unexpected bit set in this range. Bits 0–15
561 are reserved to signal backwards-compatible format issues; a reader
562 should simply ignore any unexpected bits set in this range. Currently
563 only one flag bit is defined, and the rest must be zero:
564 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Bit 16</span></dt><dd><p>
565 If 1, OIDs are included in the data; if 0, not. Oid system columns
566 are not supported in <span class="productname">PostgreSQL</span>
567 anymore, but the format still contains the indicator.
568 </p></dd></dl></div></dd><dt><span class="term">Header extension area length</span></dt><dd><p>
569 32-bit integer, length in bytes of remainder of header, not including self.
570 Currently, this is zero, and the first tuple follows
571 immediately. Future changes to the format might allow additional data
572 to be present in the header. A reader should silently skip over any header
573 extension data it does not know what to do with.
574 </p></dd></dl></div><p>
576 The header extension area is envisioned to contain a sequence of
577 self-identifying chunks. The flags field is not intended to tell readers
578 what is in the extension area. Specific design of header extension contents
579 is left for a later release.
581 This design allows for both backwards-compatible header additions (add
582 header extension chunks, or set low-order flag bits) and
583 non-backwards-compatible changes (set high-order flag bits to signal such
584 changes, and add supporting data to the extension area if needed).
585 </p></div><div class="refsect3" id="id-1.9.3.55.9.4.6"><h4>Tuples</h4><p>
586 Each tuple begins with a 16-bit integer count of the number of fields in the
587 tuple. (Presently, all tuples in a table will have the same count, but that
588 might not always be true.) Then, repeated for each field in the tuple, there
589 is a 32-bit length word followed by that many bytes of field data. (The
590 length word does not include itself, and can be zero.) As a special case,
591 -1 indicates a NULL field value. No value bytes follow in the NULL case.
593 There is no alignment padding or any other extra data between fields.
595 Presently, all data values in a binary-format file are
596 assumed to be in binary format (format code one). It is anticipated that a
597 future extension might add a header field that allows per-column format codes
600 To determine the appropriate binary format for the actual tuple data you
601 should consult the <span class="productname">PostgreSQL</span> source, in
602 particular the <code class="function">*send</code> and <code class="function">*recv</code> functions for
603 each column's data type (typically these functions are found in the
604 <code class="filename">src/backend/utils/adt/</code> directory of the source
607 If OIDs are included in the file, the OID field immediately follows the
608 field-count word. It is a normal field except that it's not included in the
609 field-count. Note that oid system columns are not supported in current
610 versions of <span class="productname">PostgreSQL</span>.
611 </p></div><div class="refsect3" id="id-1.9.3.55.9.4.7"><h4>File Trailer</h4><p>
612 The file trailer consists of a 16-bit integer word containing -1. This
613 is easily distinguished from a tuple's field-count word.
615 A reader should report an error if a field-count word is neither -1
616 nor the expected number of columns. This provides an extra
617 check against somehow getting out of sync with the data.
618 </p></div></div></div><div class="refsect1" id="id-1.9.3.55.10"><h2>Examples</h2><p>
619 The following example copies a table to the client
620 using the vertical bar (<code class="literal">|</code>) as the field delimiter:
621 </p><pre class="programlisting">
622 COPY country TO STDOUT (DELIMITER '|');
625 To copy data from a file into the <code class="literal">country</code> table:
626 </p><pre class="programlisting">
627 COPY country FROM '/usr1/proj/bray/sql/country_data';
630 To copy into a file just the countries whose names start with 'A':
631 </p><pre class="programlisting">
632 COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';
635 To copy into a compressed file, you can pipe the output through an external
637 </p><pre class="programlisting">
638 COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';
641 Here is a sample of data suitable for copying into a table from
642 <code class="literal">STDIN</code>:
643 </p><pre class="programlisting">
650 Note that the white space on each line is actually a tab character.
652 The following is the same data, output in binary format.
653 The data is shown after filtering through the
654 Unix utility <code class="command">od -c</code>. The table has three columns;
655 the first has type <code class="type">char(2)</code>, the second has type <code class="type">text</code>,
656 and the third has type <code class="type">integer</code>. All the rows have a null value
658 </p><pre class="programlisting">
659 0000000 P G C O P Y \n 377 \r \n \0 \0 \0 \0 \0 \0
660 0000020 \0 \0 \0 \0 003 \0 \0 \0 002 A F \0 \0 \0 013 A
661 0000040 F G H A N I S T A N 377 377 377 377 \0 003
662 0000060 \0 \0 \0 002 A L \0 \0 \0 007 A L B A N I
663 0000100 A 377 377 377 377 \0 003 \0 \0 \0 002 D Z \0 \0 \0
664 0000120 007 A L G E R I A 377 377 377 377 \0 003 \0 \0
665 0000140 \0 002 Z M \0 \0 \0 006 Z A M B I A 377 377
666 0000160 377 377 \0 003 \0 \0 \0 002 Z W \0 \0 \0 \b Z I
667 0000200 M B A B W E 377 377 377 377 377 377
668 </pre></div><div class="refsect1" id="id-1.9.3.55.11"><h2>Compatibility</h2><p>
669 There is no <code class="command">COPY</code> statement in the SQL standard.
671 The following syntax was used before <span class="productname">PostgreSQL</span>
672 version 9.0 and is still supported:
674 </p><pre class="synopsis">
675 COPY <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ]
676 FROM { '<em class="replaceable"><code>filename</code></em>' | STDIN }
679 [ DELIMITER [ AS ] '<em class="replaceable"><code>delimiter_character</code></em>' ]
680 [ NULL [ AS ] '<em class="replaceable"><code>null_string</code></em>' ]
682 [ QUOTE [ AS ] '<em class="replaceable"><code>quote_character</code></em>' ]
683 [ ESCAPE [ AS ] '<em class="replaceable"><code>escape_character</code></em>' ]
684 [ FORCE NOT NULL <em class="replaceable"><code>column_name</code></em> [, ...] ] ] ]
686 COPY { <em class="replaceable"><code>table_name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] | ( <em class="replaceable"><code>query</code></em> ) }
687 TO { '<em class="replaceable"><code>filename</code></em>' | STDOUT }
690 [ DELIMITER [ AS ] '<em class="replaceable"><code>delimiter_character</code></em>' ]
691 [ NULL [ AS ] '<em class="replaceable"><code>null_string</code></em>' ]
693 [ QUOTE [ AS ] '<em class="replaceable"><code>quote_character</code></em>' ]
694 [ ESCAPE [ AS ] '<em class="replaceable"><code>escape_character</code></em>' ]
695 [ FORCE QUOTE { <em class="replaceable"><code>column_name</code></em> [, ...] | * } ] ] ]
698 Note that in this syntax, <code class="literal">BINARY</code> and <code class="literal">CSV</code> are
699 treated as independent keywords, not as arguments of a <code class="literal">FORMAT</code>
702 The following syntax was used before <span class="productname">PostgreSQL</span>
703 version 7.3 and is still supported:
705 </p><pre class="synopsis">
706 COPY [ BINARY ] <em class="replaceable"><code>table_name</code></em>
707 FROM { '<em class="replaceable"><code>filename</code></em>' | STDIN }
708 [ [USING] DELIMITERS '<em class="replaceable"><code>delimiter_character</code></em>' ]
709 [ WITH NULL AS '<em class="replaceable"><code>null_string</code></em>' ]
711 COPY [ BINARY ] <em class="replaceable"><code>table_name</code></em>
712 TO { '<em class="replaceable"><code>filename</code></em>' | STDOUT }
713 [ [USING] DELIMITERS '<em class="replaceable"><code>delimiter_character</code></em>' ]
714 [ WITH NULL AS '<em class="replaceable"><code>null_string</code></em>' ]
715 </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="27.4.3. COPY Progress Reporting">Section 27.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 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE ACCESS METHOD</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob