​
Overview

When you export data from a table to a CSV file using the COPY TO command, you can include or skip the header. Oxla provides three options for handling headers: HEADER OFF, HEADER ON, and HEADER MATCH.

​
Syntax

The syntax for COPY TO with HEADER is as follows:

COPY table_name TO 'file_path' (Header_Syntax);

Parameters in the syntax include:

  • table_name: The table containing the data to be exported.

  • file_path: The CSV file location where the data will be saved.

  • Header_Syntax: The specified header options.

​
Header Options

  • HEADER OFF

This option will not skip the header of the CSV file. The available syntax is: 

HEADER OFF
HEADER FALSE
HEADER 0
This option is a default behaviour if HEADER is not provided.
  • HEADER ON

This option skips the header of the CSV file and follows only the previously specified columns. The available syntax is: 

HEADER ON
HEADER TRUE
HEADER 1

​
Examples

First, create a “personal_details” table.

CREATE TABLE personal_details (
  id int,
  first_name string,
  last_name string,
  gender string
);
INSERT INTO personal_details 
    (id, first_name, last_name, gender) 
VALUES 
    (1,'Mark','Wheeler','M'),
    (2,'Tom','Hanks','M'),
    (3,'Jane','Hopper','F'),
    (4,'Emily','Byers','F'),
    (5,'Lucas','Sinclair','M');

The table and data were created successfully.

COMPLETE
INSERT 0 5

Now, let’s explore some cases of COPY TO with different header options:

​
Case 1: HEADER OFF

Please ensure that the directory where you save the file has a write permissions.
  1. Run the query below to export the table.
COPY personal_details TO '/home/acer/Documents/personalinfo.csv';
  1. You will get the following output, indicating that the table has successfully exported to the CSV file.
--
(0 rows)
  1. The data in the table is copied directly to the personalinfo file without considering the first row as a header.
1,'Mark','Wheeler','M'
2,'Tom','Hanks','M'
3,'Jane','Hopper','F'
4,'Emily','Byers','F'
5,'Lucas','Sinclair','M'
To include headers, use the HEADER ON option.

​
Case 2: HEADER ON

  1. Run the query below to export the table.
COPY personal_details TO '/home/acer/Documents/personalinfo.csv' (HEADER ON);
  1. You will get a successful output below.
--
(0 rows)
  1. In this case, the header from the table will be included in the CSV file. 
id,first_name,last_name,gender
1,'Mark','Wheeler','M'
2,'Tom','Hanks','M'
3,'Jane','Hopper','F'
4,'Emily','Byers','F'
5,'Lucas','Sinclair','M'
COPY TO with EndpointCOPY TO with NULL