1COPY(7)                          SQL Commands                          COPY(7)
2
3
4

NAME

6       COPY - copy data between a file and a table
7
8

SYNOPSIS

10       COPY tablename [ ( column [, ...] ) ]
11           FROM { 'filename' | STDIN }
12           [ [ WITH ]
13                 [ BINARY ]
14                 [ OIDS ]
15                 [ DELIMITER [ AS ] 'delimiter' ]
16                 [ NULL [ AS ] 'null string' ]
17                 [ CSV [ HEADER ]
18                       [ QUOTE [ AS ] 'quote' ]
19                       [ ESCAPE [ AS ] 'escape' ]
20                       [ FORCE NOT NULL column [, ...] ]
21
22       COPY { tablename [ ( column [, ...] ) ] | ( query ) }
23           TO { 'filename' | STDOUT }
24           [ [ WITH ]
25                 [ BINARY ]
26                 [ OIDS ]
27                 [ DELIMITER [ AS ] 'delimiter' ]
28                 [ NULL [ AS ] 'null string' ]
29                 [ CSV [ HEADER ]
30                       [ QUOTE [ AS ] 'quote' ]
31                       [ ESCAPE [ AS ] 'escape' ]
32                       [ FORCE QUOTE column [, ...] ]
33
34

DESCRIPTION

36       COPY  moves  data  between  PostgreSQL  tables and standard file-system
37       files. COPY TO copies the contents of a table to  a  file,  while  COPY
38       FROM copies data from a file to a table (appending the data to whatever
39       is in the table already). COPY TO can also copy the results of a SELECT
40       query.
41
42       If  a list of columns is specified, COPY will only copy the data in the
43       specified columns to or from the file.  If there are any columns in the
44       table  that  are  not  in  the  column  list, COPY FROM will insert the
45       default values for those columns.
46
47       COPY with a file name instructs the PostgreSQL server to directly  read
48       from  or write to a file. The file must be accessible to the server and
49       the name must be specified from the viewpoint of the server. When STDIN
50       or  STDOUT is specified, data is transmitted via the connection between
51       the client and the server.
52

PARAMETERS

54       tablename
55              The name (optionally schema-qualified) of an existing table.
56
57       column An optional list of columns to be copied. If no column  list  is
58              specified, all columns of the table will be copied.
59
60       query  A SELECT [select(7)] or VALUES [values(7)] command whose results
61              are to be copied.  Note that parentheses are required around the
62              query.
63
64       filename
65              The  absolute  path  name  of  the input or output file. Windows
66              users might need to use an E''  string  and  double  backslashes
67              used as path separators.
68
69       STDIN  Specifies that input comes from the client application.
70
71       STDOUT Specifies that output goes to the client application.
72
73       BINARY Causes  all  data  to  be stored or read in binary format rather
74              than as text. You cannot specify the  DELIMITER,  NULL,  or  CSV
75              options in binary mode.
76
77       OIDS   Specifies  copying  the OID for each row. (An error is raised if
78              OIDS is specified for a table that does not have OIDs, or in the
79              case of copying a query.)
80
81       delimiter
82              The  single  ASCII  character that separates columns within each
83              row (line) of the file. The default is a tab character  in  text
84              mode, a comma in CSV mode.
85
86       null string
87              The  string  that  represents  a  null  value. The default is \N
88              (backslash-N) in text mode, and an unquoted empty string in  CSV
89              mode.  You  might  prefer  an empty string even in text mode for
90              cases where you don't  want  to  distinguish  nulls  from  empty
91              strings.
92
93              Note:  When  using  COPY  FROM,  any data item that matches this
94              string will be stored as a null value, so you should  make  sure
95              that you use the same string as you used with COPY TO.
96
97
98       CSV    Selects Comma Separated Value (CSV) mode.
99
100       HEADER Specifies that the file contains a header line with the names of
101              each column in the file. On output, the first line contains  the
102              column  names  from  the  table, and on input, the first line is
103              ignored.
104
105       quote  Specifies the  ASCII  quotation  character  in  CSV  mode.   The
106              default is double-quote.
107
108       escape Specifies  the ASCII character that should appear before a QUOTE
109              data character value in CSV mode.   The  default  is  the  QUOTE
110              value (usually double-quote).
111
112       FORCE QUOTE
113              In  CSV COPY TO mode, forces quoting to be used for all non-NULL
114              values in each specified column.  NULL output is never quoted.
115
116       FORCE NOT NULL
117              In CSV COPY FROM mode, process each specified column  as  though
118              it  were quoted and hence not a NULL value. For the default null
119              string in CSV mode (''), this causes missing values to be  input
120              as zero-length strings.
121

OUTPUTS

123       On  successful  completion, a COPY command returns a command tag of the
124       form
125
126       COPY count
127
128       The count is the number of rows copied.
129

NOTES

131       COPY can only be used with plain tables, not with views.  However,  you
132       can write COPY (SELECT * FROM viewname) TO ....
133
134       The  BINARY key word causes all data to be stored/read as binary format
135       rather than as text. It is somewhat faster than the normal  text  mode,
136       but  a binary-format file is less portable across machine architectures
137       and PostgreSQL versions.  Also, the binary format  is  very  data  type
138       specific;  for  example  it  will not work to output binary data from a
139       smallint column and read it into an integer column,  even  though  that
140       would work fine in text format.
141
142       You  must  have  select privilege on the table whose values are read by
143       COPY TO, and insert privilege  on  the  table  into  which  values  are
144       inserted  by  COPY  FROM. It is sufficient to have column privileges on
145       the column(s) listed in the command.
146
147       Files named in a COPY command are  read  or  written  directly  by  the
148       server,  not  by the client application. Therefore, they must reside on
149       or be accessible to the database server machine, not the  client.  They
150       must  be  accessible to and readable or writable by the PostgreSQL user
151       (the user ID the server runs as), not the client. COPY naming a file is
152       only allowed to database superusers, since it allows reading or writing
153       any file that the server has privileges to access.
154
155       Do not confuse COPY with the psql instruction \copy. \copy invokes COPY
156       FROM  STDIN  or  COPY  TO STDOUT, and then fetches/stores the data in a
157       file accessible to the psql client. Thus, file accessibility and access
158       rights depend on the client rather than the server when \copy is used.
159
160       It  is  recommended that the file name used in COPY always be specified
161       as an absolute path. This is enforced by the server in the case of COPY
162       TO,  but  for  COPY  FROM you do have the option of reading from a file
163       specified by a relative path. The path will be interpreted relative  to
164       the  working  directory  of  the server process (normally the cluster's
165       data directory), not the client's working directory.
166
167       COPY FROM will invoke any triggers and check constraints on the  desti‐
168       nation table. However, it will not invoke rules.
169
170       COPY  input  and output is affected by DateStyle. To ensure portability
171       to other PostgreSQL installations that might use non-default  DateStyle
172       settings,  DateStyle  should  be set to ISO before using COPY TO. It is
173       also a good idea to  avoid  dumping  data  with  IntervalStyle  set  to
174       sql_standard,  because negative interval values might be misinterpreted
175       by a server that has a different setting for IntervalStyle.
176
177       Input data is interpreted according to the current client encoding, and
178       output  data is encoded in the the current client encoding, even if the
179       data does not pass through the client but is read from or written to  a
180       file.
181
182       COPY  stops operation at the first error. This should not lead to prob‐
183       lems in the event of a COPY TO, but the target table will already  have
184       received earlier rows in a COPY FROM. These rows will not be visible or
185       accessible, but they still occupy disk space. This might  amount  to  a
186       considerable  amount  of wasted disk space if the failure happened well
187       into a large copy operation. You might wish to invoke VACUUM to recover
188       the wasted space.
189

FILE FORMATS

191   TEXT FORMAT
192       When  COPY  is used without the BINARY or CSV options, the data read or
193       written is a text file with one line per table row.  Columns in  a  row
194       are separated by the delimiter character.  The column values themselves
195       are strings generated by the output  function,  or  acceptable  to  the
196       input  function,  of  each  attribute's  data  type. The specified null
197       string is used in place of columns that are null.  COPY FROM will raise
198       an  error  if any line of the input file contains more or fewer columns
199       than are expected.  If OIDS is specified, the OID is read or written as
200       the first column, preceding the user data columns.
201
202       End  of  data can be represented by a single line containing just back‐
203       slash-period (\.). An end-of-data marker is not necessary when  reading
204       from  a file, since the end of file serves perfectly well; it is needed
205       only when copying data to or from  client  applications  using  pre-3.0
206       client protocol.
207
208       Backslash  characters  (\)  can  be used in the COPY data to quote data
209       characters that might otherwise be taken as row or  column  delimiters.
210       In particular, the following characters must be preceded by a backslash
211       if they appear as part of a column value:  backslash  itself,  newline,
212       carriage return, and the current delimiter character.
213
214       The  specified  null string is sent by COPY TO without adding any back‐
215       slashes; conversely, COPY FROM  matches  the  input  against  the  null
216       string before removing backslashes. Therefore, a null string such as \N
217       cannot be confused with the actual data value \N (which would be repre‐
218       sented as \\N).
219
220       The  following special backslash sequences are recognized by COPY FROM:
221       SequenceRepresents\bBackspace (ASCII 8)\fForm feed (ASCII  12)\nNewline
222       (ASCII  10)\rCarriage  return  (ASCII  13)\tTab (ASCII 9)\vVertical tab
223       (ASCII 11)\digitsBackslash followed by one to three octal digits speci‐
224       fies  the  character with that numeric code\xdigitsBackslash x followed
225       by one or two hex digits specifies the character with that numeric code
226       Presently,  COPY  TO  will  never emit an octal or hex-digits backslash
227       sequence, but it does use the other sequences listed  above  for  those
228       control characters.
229
230       Any  other backslashed character that is not mentioned in the above ta‐
231       ble will be taken to represent itself. However, beware of adding  back‐
232       slashes  unnecessarily,  since that might accidentally produce a string
233       matching the  end-of-data  marker  (\.)  or  the  null  string  (\N  by
234       default).  These  strings will be recognized before any other backslash
235       processing is done.
236
237       It is strongly recommended that applications generating COPY data  con‐
238       vert  data  newlines  and  carriage  returns to the \n and \r sequences
239       respectively. At present it is possible to represent  a  data  carriage
240       return by a backslash and carriage return, and to represent a data new‐
241       line by a backslash and newline.  However, these representations  might
242       not be accepted in future releases.  They are also highly vulnerable to
243       corruption if the COPY file is transferred  across  different  machines
244       (for example, from Unix to Windows or vice versa).
245
246       COPY  TO  will  terminate  each row with a Unix-style newline (``\n'').
247       Servers  running  on  Microsoft   Windows   instead   output   carriage
248       return/newline (``\r\n''), but only for COPY to a server file; for con‐
249       sistency across platforms, COPY TO STDOUT always sends  ``\n''  regard‐
250       less  of  server platform.  COPY FROM can handle lines ending with new‐
251       lines, carriage returns, or carriage  return/newlines.  To  reduce  the
252       risk  of  error due to un-backslashed newlines or carriage returns that
253       were meant as data, COPY FROM will complain if the line endings in  the
254       input are not all alike.
255
256   CSV FORMAT
257       This  format  is  used  for importing and exporting the Comma Separated
258       Value (CSV) file format used by many other programs,  such  as  spread‐
259       sheets.  Instead  of  the  escaping  used by PostgreSQL's standard text
260       mode, it produces and recognizes the common CSV escaping mechanism.
261
262       The values in each record are separated by the DELIMITER character.  If
263       the  value  contains  the delimiter character, the QUOTE character, the
264       NULL string, a carriage return, or line feed character, then the  whole
265       value  is  prefixed and suffixed by the QUOTE character, and any occur‐
266       rence within the value of a QUOTE character or the ESCAPE character  is
267       preceded  by  the  escape  character.   You can also use FORCE QUOTE to
268       force quotes when outputting non-NULL values in specific columns.
269
270       The CSV format has no standard way to distinguish a NULL value from  an
271       empty  string.   PostgreSQL's  COPY handles this by quoting.  A NULL is
272       output as the NULL parameter string and is not quoted, while a non-NULL
273       value  matching  the NULL parameter string is quoted. For example, with
274       the default settings, a NULL is written as an  unquoted  empty  string,
275       while  an  empty  string data value is written with double quotes ("").
276       Reading values follows similar rules. You can use  FORCE  NOT  NULL  to
277       prevent NULL input comparisons for specific columns.
278
279       Because backslash is not a special character in the CSV format, \., the
280       end-of-data marker, could also appear as a data  value.  To  avoid  any
281       misinterpretation, a \.  data value appearing as a lone entry on a line
282       is automatically quoted on output, and on  input,  if  quoted,  is  not
283       interpreted  as  the end-of-data marker. If you are loading a file cre‐
284       ated by another application that has a single unquoted column and might
285       have  a  value  of  \., you might need to quote that value in the input
286       file.
287
288              Note: In CSV mode, all  characters  are  significant.  A  quoted
289              value  surrounded  by  white space, or any characters other than
290              DELIMITER, will include those characters. This can cause  errors
291              if  you import data from a system that pads CSV lines with white
292              space out to some fixed width. If such a  situation  arises  you
293              might  need  to  preprocess  the CSV file to remove the trailing
294              white space, before importing the data into PostgreSQL.
295
296
297              Note: CSV mode will both recognize and produce  CSV  files  with
298              quoted  values  containing  embedded  carriage  returns and line
299              feeds. Thus the files are not strictly one line  per  table  row
300              like text-mode files.
301
302
303              Note:  Many  programs  produce strange and occasionally perverse
304              CSV files, so the file format is more a convention than a  stan‐
305              dard.  Thus  you  might  encounter  some  files  that  cannot be
306              imported using this mechanism, and COPY might produce files that
307              other programs cannot process.
308
309
310   BINARY FORMAT
311       The file format used for COPY BINARY changed in PostgreSQL 7.4. The new
312       format consists of a file header, zero or more  tuples  containing  the
313       row  data, and a file trailer. Headers and data are now in network byte
314       order.
315
316   FILE HEADER
317       The file header consists of 15 bytes of fixed  fields,  followed  by  a
318       variable-length header extension area. The fixed fields are:
319
320       Signature
321              11-byte sequence PGCOPY\n\377\r\n\0 — note that the zero byte is
322              a required part of the signature. (The signature is designed  to
323              allow  easy  identification  of files that have been munged by a
324              non-8-bit-clean transfer. This signature will be changed by end-
325              of-line-translation  filters,  dropped  zero bytes, dropped high
326              bits, or parity changes.)
327
328       Flags field
329              32-bit integer bit mask to denote important aspects of the  file
330              format.  Bits  are  numbered from 0 (LSB) to 31 (MSB). Note that
331              this field is stored in network  byte  order  (most  significant
332              byte first), as are all the integer fields used in the file for‐
333              mat. Bits 16-31 are reserved  to  denote  critical  file  format
334              issues;  a reader should abort if it finds an unexpected bit set
335              in this range. Bits 0-15 are reserved to  signal  backwards-com‐
336              patible  format  issues; a reader should simply ignore any unex‐
337              pected bits set in this range. Currently only one  flag  bit  is
338              defined, and the rest must be zero:
339
340              Bit 16 if 1, OIDs are included in the data; if 0, not
341
342       Header extension area length
343              32-bit  integer,  length  in  bytes  of remainder of header, not
344              including self.  Currently, this is zero, and  the  first  tuple
345              follows  immediately.  Future  changes to the format might allow
346              additional data to be present in the  header.  A  reader  should
347              silently  skip  over  any header extension data it does not know
348              what to do with.
349
350       The header extension area is envisioned to contain a sequence of  self-
351       identifying  chunks.  The  flags  field is not intended to tell readers
352       what is in the extension area. Specific design of header extension con‐
353       tents is left for a later release.
354
355       This  design allows for both backwards-compatible header additions (add
356       header extension chunks, or set low-order flag bits) and non-backwards-
357       compatible  changes  (set  high-order flag bits to signal such changes,
358       and add supporting data to the extension area if needed).
359
360   TUPLES
361       Each tuple begins with a 16-bit integer count of the number  of  fields
362       in  the  tuple.  (Presently,  all  tuples in a table will have the same
363       count, but that might not always be  true.)  Then,  repeated  for  each
364       field in the tuple, there is a 32-bit length word followed by that many
365       bytes of field data. (The length word does not include itself, and  can
366       be  zero.) As a special case, -1 indicates a NULL field value. No value
367       bytes follow in the NULL case.
368
369       There is no alignment padding or any other extra data between fields.
370
371       Presently, all data values in a COPY BINARY file are assumed to  be  in
372       binary format (format code one). It is anticipated that a future exten‐
373       sion might add a header field that allows per-column format codes to be
374       specified.
375
376       To  determine  the  appropriate binary format for the actual tuple data
377       you should consult the PostgreSQL source, in particular the  *send  and
378       *recv  functions for each column's data type (typically these functions
379       are found in the src/backend/utils/adt/ directory of the source distri‐
380       bution).
381
382       If OIDs are included in the file, the OID field immediately follows the
383       field-count word. It is a normal field except that it's not included in
384       the  field-count.  In particular it has a length word — this will allow
385       handling of 4-byte vs. 8-byte OIDs without  too  much  pain,  and  will
386       allow OIDs to be shown as null if that ever proves desirable.
387
388   FILE TRAILER
389       The  file trailer consists of a 16-bit integer word containing -1. This
390       is easily distinguished from a tuple's field-count word.
391
392       A reader should report an error if a field-count word is neither -1 nor
393       the  expected  number  of columns. This provides an extra check against
394       somehow getting out of sync with the data.
395

EXAMPLES

397       The following example copies a table to the client using  the  vertical
398       bar (|) as the field delimiter:
399
400       COPY country TO STDOUT WITH DELIMITER '|';
401
402
403       To copy data from a file into the country table:
404
405       COPY country FROM '/usr1/proj/bray/sql/country_data';
406
407
408       To copy into a file just the countries whose names start with 'A':
409
410       COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';
411
412
413       Here is a sample of data suitable for copying into a table from STDIN:
414
415       AF      AFGHANISTAN
416       AL      ALBANIA
417       DZ      ALGERIA
418       ZM      ZAMBIA
419       ZW      ZIMBABWE
420
421       Note that the white space on each line is actually a tab character.
422
423       The  following  is the same data, output in binary format.  The data is
424       shown after filtering through the Unix utility od  -c.  The  table  has
425       three  columns;  the  first has type char(2), the second has type text,
426       and the third has type integer. All the rows have a null value  in  the
427       third column.
428
429       0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
430       0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
431       0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
432       0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
433       0000100   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
434       0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
435       0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 377 377
436       0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
437       0000200   M   B   A   B   W   E 377 377 377 377 377 377
438
439

COMPATIBILITY

441       There is no COPY statement in the SQL standard.
442
443       The  following  syntax  was  used  before PostgreSQL version 7.3 and is
444       still supported:
445
446       COPY [ BINARY ] tablename [ WITH OIDS ]
447           FROM { 'filename' | STDIN }
448           [ [USING] DELIMITERS 'delimiter' ]
449           [ WITH NULL AS 'null string' ]
450
451       COPY [ BINARY ] tablename [ WITH OIDS ]
452           TO { 'filename' | STDOUT }
453           [ [USING] DELIMITERS 'delimiter' ]
454           [ WITH NULL AS 'null string' ]
455
456
457
458
459SQL - Language Statements         2011-09-22                           COPY(7)
Impressum