Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-copy.html648 linesDownload Raw Back to html
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 &gt; /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>
codekingpro/portable-devtools · Team Ai