pgPedia

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Top / C

• Browse categories
• PostgreSQL releases
  • PostgreSQL 14 (dev)
  • PostgreSQL 13 (latest)
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6
  • PostgreSQL 9.5 (EOL)

Version charts

SQL commands
System catalog tables
System catalog views
System statistics views
File system functions

Progress reporting views
Contrib modules
Storage parameters (relopts)

Useful overviews

xlog renaming in Pg 10
Online tools for PostgreSQL

Useful links

PostgreSQL documentation
PostgreSQL feature matrix
PostgreSQL versioning policy
PostgreSQL latest versions

@postgrespedia

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
  • 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)

References

Categories

See also