COPY

A DML command for bulk import/export of data to/from a file

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 default roles:

  • pg_read_server_files
  • pg_write_server_files
  • pg_execute_server_program

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 14
    • performance improvements through improved buffering (commit 0a0727cc)
  • 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
  • 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 documentation: COPY

Categories

DML, SQL command

See also

file_fdw, pg_bulkload