COPY
Attention
This page documents an earlier version. Go to the latest (v2.3) version.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.
Note
TheCOPY
statement can be used with files residing locally to the YB-TServer that you connect to. To work with files that reside on the client, use \copy
in ysqlsh
cli.
Syntax
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'
copy_from
copy_to
copy_option
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.