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'
                | ROWS_PER_TRANSACTION integer
                | DISABLE_FK_CHECK
                | REPLACE
                | SKIP integer

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. Critically, the file must be located server-side on the local filesystem of the YB-TServer that you connect to.

To work with files that reside on the client, nominate stdin as the argument for FROM or stdout as the argument for TO.

Alternatively, you can use the \copy metacommand in ysqlsh.

stdin and stdout

Critically, these input and output channels are defined client-side in the environment of the client where you run ysqlsh or your preferred programming language. These options request that the data transmission goes via the connection between the client and the server.

If you execute the COPY TO or COPY FROM statements from a client program written in a language like Python, then you cannot use ysqlsh features. Rather, you must rely on your chosen language's features to connect stdin and stdout to the file that you nominate.

However, if you execute COPY FROM using ysqlsh, you have the further option of including the COPY invocation at the start of the file that you start as a .sql script. Create a test table thus:

drop table if exists t cascade;
create table t(c1 text primary key, c2 text, c3 text);

And prepare t.sql thus:

copy t(c1, c2, c3) from stdin with (format 'csv', header true);
c1,c2,c3
dog,cat,frog
\.

Notice the \. terminator. You can simply execute \i t.sql at the ysqlsh prompt to copy in the data.

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;

Performance tips for large tables

When importing a very large table, Yugabyte recommends using many smaller transactions (rather than one large transaction). This can be achieved natively by using the ROWS_PER_TRANSACTION option.

yugabyte=# COPY large_table FROM '/home/yuga/Desktop/large_table.csv'
               WITH (FORMAT CSV, HEADER, ROWS_PER_TRANSACTION 1000);

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

Additionally, the following copy options may help to speed up copying, or allow for faster recovery from a partial state:

• DISABLE_FK_CHECK skips the foreign key check when copying new rows to the table.
• REPLACE replaces the existing row in the table if the new row's primary/unique key conflicts with that of the existing row.
• SKIP n skips the first n rows of the file. n must be a nonnegative integer.