COPY TO
Overview
The COPY TO
statement is used to export tables, specific columns, or results of select queries into .csv files. It allows you to copy data from a table or query result and save it to a specified file.
Syntax
The syntax for COPY TO
is as follows:
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO 'filename' [( option [, ...] ) ];
Parameters in the syntax include:
-
table_name
: Table with the data to export. -
column_name
: Optional. Specify columns for export. -
query
: ASELECT
statement for exporting specific results. -
filename
: File name for saving the exported data. -
option
: Optional parameters for customization.
Example
Step #1: Create a Table
- Before creating The table, check for duplicate tables using the following statement:
DESCRIBE DATABASE
- You will receive a list of existing tables in Oxla:
namespace_name | name
----------------+----------------
public | client
public | distance_table
public | weight
public | product
- Now, let’s create a table for exporting data to a CSV file. Here, we’ll create a ”salary” table:
CREATE TABLE salary (
empid int,
empname string,
empdept string,
empaddress string,
empsalary int
);
INSERT INTO salary
(empid, empname, empdept, empaddress, empsalary)
VALUES
(2001,'Paul','HR', 'California', null ),
(2002,'Brandon','Product', 'Norway', 15000),
(2003,'Bradley','Marketing', 'Texas', null),
(2004,'Lisa','Marketing', 'Houston', 10000),
(2005,'Emily','Marketing', 'Texas', 20000),
(2006,'Bobby','Finance', 'Seattle', 20000),
(2007,'Parker','Project', 'Texas', 45000);
- The table and data were created successfully.
COMPLETE
INSERT 0 7
Step #2: Copy the Table into the CSV File
- By default, the
COPY TO
command overwrites the CSV file if it already exists. - Please ensure that the directory where you save the file has the necessary write permissions.
Option 1: Exporting all columns from a table
Copy all columns in the table to the specified CSV file:
COPY salary TO '/path/to/exportsalary.csv';
You will get the following successful result:
--
(0 rows)
The data from the table will be exported to the CSV file.
Option 2: Exporting specific columns from a table
Copy only specific columns by specifying the column names in the query:
COPY salary (empid, empname, empsalary) TO 'exportsalary.csv';
You will get the following successful result:
--
(0 rows)
The data from the specified columns will be exported to the CSV file.
Option 3: Exporting results of a SELECT statement
In the example below, copy data only from the Marketing department using the SELECT
statement and WHERE
clause:
COPY (SELECT * FROM salary WHERE empdept = 'Marketing') TO 'exportsalary.csv';
You will get the following successful result:
--
(0 rows)
Data exported to CSV file is only from the Marketing department.