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 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 14
- performance improvements through improved buffering (commit 0a0727cc)
- progress tracking via
pg_stat_progress_copy
(commit 8a4f618e)
- PostgreSQL 12
- 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 rolespg_read_server_files
,pg_write_server_files
andpg_execute_server_program
(commit 0fdc8495)
- PostgreSQL 10
COPY view FROM
can now insert into views defined withINSTEAD OF INSERT
triggers (commit 279c439c)
- PostgreSQL 9.6
COPY
can now copy the output of anINSERT
/UPDATE
/DELETE ... RETURNING
query (commit 92e38182)
- PostgreSQL 9.4
- 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)
- ability to read from / write to a program added (
- PostgreSQL 9.1
- option
ENCODING
added (commit 3cba8240)
- option
- PostgreSQL 8.2
COPY (SELECT ...) TO ...
syntax added (commit 85188ab8)
- PostgreSQL 8.0
CSV
option added (commit 862b20b3)
References
- PostgreSQL documentation: COPY