Skip to main content

​
Overview

COPY FROM statement is used to import data from a file into a table by reading from the file’s content directly. When using the COPY FROM, each field in the file is inserted sequentially into the specified column.
The file must be accessible and able to be read and written to

​
Syntax

The syntax for THE COPY FROM is as follows: 
COPY table_name FROM 'file_path';
where:
  • table_name: the table that will receive the data from the file
  • file_path: the link to the file location accessible from the server

​
Example

​
Creating CSV Files

Firstly, you should create a CSV file and store it on your local computer. Here, we make a file called “feature2.csv” that stores information about features with their versions:
create a table, 1.0 modify a table, 1.2 drop a table, 2.2 rename a table, 2.0

​
Importing Files from Local to Server

You can use the syntax and the example presented below for importing the file to the server: 
aws s3 cp ~/[file location on your local computer] s3://[server location]/[file name]

aws s3 cp ~/Documents/feature2.csv s3://oxla-testdata/test/feature2.csv
After a successful import, you will get the following result: 
upload: Documents/feature2.csv to s3://oxla-testdata/test/feature2.csv

​
Connecting to Oxla Server

Now that the file has been successfully uploaded to the server, you need to connect to Oxla using the command below: 
psql -h buildfarm.oxla.com -p 6000
Once you successfully connected to an Oxla server, you should get a similar output: 
psql (15.1 (Ubuntu 15.1-1.pgdg22.10+1), server Oxla 1.0)
WARNING: psql major version 15, server major version 0.0.
         Some psql features might not work.
Type "help" for help.

​
Creating a Table

Once you proceed to table creation stage, firstly it’s worth checking for duplicate tables, by executing the statement below: 
DESCRIBE DATABASE
In return, you will retrieve a list of all existing tables in Oxla: 
+----------------------------+
| name                       |
+----------------------------+
| supplier_scale_1_no_index  |
| features                   |
| orders                     |
| features2                  |
| featurestable              |
| featurestable1             |
| featurestable10            |
+----------------------------+
After that, you need to create a table to retrieve the data from the CSV file. Here, we will create a featurelist table: 
CREATE TABLE featurelist(featurename text, version float);

​
Copying the CSV File Into the Table

Now, you can copy the “feature2.csv” by executing the COPY FROM query, as shown below: 
COPY featurelisttable FROM 's3://oxla-testdata/cayo/feature2.csv';

​
Retrieving the Table

To verify that the data was imported correctly from the server, you can retrieve all the data using the SELECT statement: 
SELECT * FROM featurelisttable;
Now you should have the same data in the table as in the CSV file. 
+-----------------+----------+
| featurename     | version  | 
+-----------------+----------+
| create a table  | 1        |
| modify a table  | 1.2      |
| drop a table    | 2.2      |
| rename a table  | 2        |
+-----------------+----------+