COPY

An SQL command for bulk import/export of data

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

Examples

Basic usage example for COPY:

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

Categories

DML, SQL command

See also

file_fdw, pg_bulkload