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
  • PostgreSQL 9.4 (EOL)

Useful articles

System catalog tables
System catalog views
System statistics views
Progress reporting views

Contrib modules
Storage parameters (relopts)

xlog renaming in Pg 10

Useful links

PostgreSQL documentation
PostgreSQL feature matrix
PostgreSQL versioning policy
PostgreSQL latest versions

explain.depesz.com/

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

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

References

Categories

See also