• Browse categories
• Random entry
• PostgreSQL releases
  • PostgreSQL 16 (dev)
  • PostgreSQL 15 (latest)
  • PostgreSQL 14
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10 (EOL)

Version charts

Useful overviews

Useful links

COPY

COPY is a DML command for bulk import/export of data to/from a file or (from PostgreSQL 9.3) a program.

COPY has always been present in PostgreSQL.

Limitations

COPY is restricted to operations permitted to the operating system user under which PostgreSQL runs on the host operating system.

Prior to PostgreSQL 11, usage of COPY was restricted to superusers. Beginning with PostgreSQL 11, COPY is available to superusers and members of the following predefined roles:

• pg_read_server_files
• pg_write_server_files
• pg_execute_server_program

Progress reporting

From PostgreSQL 14, the progress reporting view pg_stat_progress_copy provides information about the progress of a COPY operation.

psql

psql provides the slash command \copy, which enables COPY actions to/from the client system, overcoming the local-server-only limitation of the SQL COPY command.

For more details, see the psql documentation: \copy.

Change history

Work-in-progress

• PostgreSQL 16
  • batch insertion into foreign tables enabled (commit 97da4824)
• PostgreSQL 15
  • HEADER option now available for FORMAT option text (commit 43f33dc0)
  • option HEADER MATCH for COPY FROM added (commit 072132f0)
• PostgreSQL 14
  • performance improvements through improved buffering (commit 0a0727cc)
  • progress tracking via pg_stat_progress_copy (commit 8a4f618e)
• PostgreSQL 12
  • WHERE clause can now be added to COPY FROM, making it possible to filter incoming data (commit 31f38174)
  • insertion into partitioned tables speeded up through use of multi-inserts (commit 0d5f05cd)
• PostgreSQL 11
  • COPY available for foreign tables and foreign partitions, if the underlying foreign data wrapper provides support (commit 3d956d95)
  • COPY can also be executed by members of the default roles pg_read_server_files, pg_write_server_files and pg_execute_server_program (commit 0fdc8495)
• PostgreSQL 10
  • COPY view FROM can now insert into views defined with INSTEAD OF INSERT triggers (commit 279c439c)
• PostgreSQL 9.6
  • COPY can now copy the output of an INSERT/UPDATE/DELETE ... RETURNING query (commit 92e38182)
• PostgreSQL 9.4
  • FORCE NULL option added (commit 3b5e03dc)
  • COPY operations into tables with DEFAULT nextval() speeded up (commit 4d1e2aeb)
• PostgreSQL 9.3
  • ability to read from / write to a program added (COPY ... [FROM | TO] PROGRAM) added (commit 3d009e45)
  • COPY FREEZE option added (commit 8de72b66)
  • number of rows processed by COPY now available via SPI (commit 7ae18159)
  • number of rows processed by COPY now available in PL/pgSQL with GET DIAGNOSTICS x = ROW_COUNT (commit 7ae18159)
• PostgreSQL 9.1
  • option ENCODING added (commit 3cba8240)
• PostgreSQL 8.2
  • COPY (SELECT ...) TO ... syntax added (commit 85188ab8)
• PostgreSQL 8.0
  • CSV option added (commit 862b20b3)
• PostgreSQL 7.4
  • clean exit from COPY operations now possible (commit ea20397b)
  • \r and \r\n termination for COPY files now permitted (commit c3b7d2d5)
  • files containing escaped \r and \r\n sequences can now be processed (commit c3b7d2d5)
• PostgreSQL 7.3
  • option handling standardized via WITH clause (commit c2c2fd57)
  • individual table columns can be specified for input and output operations (commit a90db34b)
  • an error will be raised if no table columns are specified for input operations and the source data does not contain all table colums (commit 874148fe)
  • COPY TO outputs embedded carriage returns and newlines as \r and \n (commit 9832a235)
  • DELIMITER string can be an 8-bit character (commit 21f9a654)
• PostgreSQL 7.2
  • COPY TO/COPY FROM no longer possible for views (commit b6564c44)
  • DELIMITER string must be exactly 1 character (commit 969cc16c)
• PostgreSQL 7.1
  • new file format for binary copy mode (commit 676cf18c)
• PostgreSQL 7.0
  • USING keyword made optional (commit 60f3e6b3)
  • WITH NULL AS 'null string'  option added (commit bcaabc56)
• PostgreSQL 6.3
  • line number reported on failure (commit deea69b9)

Examples

postgres=# COPY foo FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1
>> 2
>> 3
>> 4
>> \.
COPY 4

References

Categories

See also