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

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 documentation: COPY

Categories

DML, SQL command

See also

file_fdw, pg_bulkload