COPY

Synopsis

Use the COPY statement to transfer data between tables and files. COPY TO copies from tables to files. COPY FROM copies from files to tables. COPY outputs the number of rows that were copied.

Syntax

• Grammar
• Diagram

copy_from ::= COPY table_name [ ( column_name [ , ... ] ) ]  FROM 
              { 'filename' | PROGRAM 'command' | STDIN } 
              [ [ WITH ] ( option [ , ... ] ) ]

copy_to ::= COPY { table_name [ ( column_names ) ] | ( query ) }  TO 
            { 'filename' | PROGRAM 'command' | STDOUT } 
            [ [ WITH ] ( option [ , ... ] ) ]

copy_option ::= FORMAT format_name
                | OIDS [ boolean ]
                | FREEZE [ boolean ]
                | DELIMITER 'delimiter_character'
                | NULL 'null_string'
                | HEADER [ boolean ]
                | QUOTE 'quote_character'
                | ESCAPE 'escape_character'
                | FORCE_QUOTE { ( column_names ) | * }
                | FORCE_NOT_NULL ( column_names )
                | FORCE_NULL ( column_names )
                | ENCODING 'encoding_name'

Semantics

table_name

Specify the table, optionally schema-qualified, to be copied.

column_name

Specify the list of columns to be copied. If not specified, then all columns of the table will be copied.

query

Specify a SELECT, VALUES, INSERT, UPDATE, or DELETE statement whose results are to be copied. For INSERT, UPDATE, and DELETE statements, a RETURNING clause must be provided.

filename

Specify the path of the file to be copied. An input file name can be an absolute or relative path, but an output file name must be an absolute path.

Examples

The examples below assume a table like this:

yugabyte=# CREATE TABLE users(id BIGSERIAL PRIMARY KEY, name TEXT);
yugabyte=# INSERT INTO users(name) VALUES ('John Doe'), ('Jane Doe'), ('Dorian Gray');
yugabyte=# SELECT * FROM users;
 id |    name     
----+-------------
  3 | Dorian Gray
  2 | Jane Doe
  1 | John Doe
(3 rows)

Export an entire table

Copy the entire table to a CSV file using an absolute path, with column names in the header.

yugabyte=# COPY users TO '/home/yuga/Desktop/users.txt.sql' DELIMITER ',' CSV HEADER;

Export a partial table using the WHERE clause with column selection

In the following example, a WHERE clause is used to filter the rows and only the name column.

yugabyte=# COPY (SELECT name FROM users where name='Dorian Gray') TO '/home/yuga/Desktop/users.txt.sql' DELIMITER
 ',' CSV HEADER;

Import from CSV files

In the following example, the data exported in the previous examples are imported in the users table.

yugabyte=# COPY users FROM '/home/yuga/Desktop/users.txt.sql' DELIMITER ',' CSV HEADER;

• If the table does not exist, errors are raised.
• COPY TO can only be used with regular tables.
• COPY FROM can be used with tables, foreign tables, and views.