COPY FROM

​

Overview

COPY FROM is used to import data from a file into a table by reading from the file directly. When using the COPY FROM, each field in the file is inserted sequentially into the specified column.

​

Syntax

The syntax for COPY FROM is as follows:

COPY table_name FROM 'file_path';

Two parameters need to be specified in the syntax:

• table_name: the table that will receive data from the file.

• file_path: a link to the file location accessible from the server.

​

Example

​

Step #1: Create a CSV File

First, you should create a CSV file and store it on your local computer. Here, we make a file called “feature2.csv” that stores features with its version.

create a table, 1.0 modify a table, 1.2 drop a table, 2.2 rename a table, 2.0

​

Step #2: Import FIle from Local to Server

You can use the syntax below for importing the file to the server:

aws s3 cp ~/[file location on your local computer] s3://[server location]/[file name]

Next, import the file to the server using the above syntax as shown below:

aws s3 cp ~/Documents/feature2.csv s3://oxla-testdata/cayo/feature2.csv

If it’s successfully imported, you will get the following result:

upload: Documents/feature2.csv to s3://oxla-testdata/cayo/feature2.csv

​

Step #3: Connect to Oxla Server

The file has been successfully uploaded to the server ✅. Now, connect to the Oxla server using the command below:

psql -h buildfarm.oxla.com -p 6000

If you get the output below, you are currently in the Oxla environment.

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.

​

Step #4: Create a Table

Before creating a table, check for duplicate tables with the statement below:

DESCRIBE DATABASE

In return, you will retrieve a list of existing tables in the Oxla.

+----------------------------+
| name                       |
+----------------------------+
| supplier_scale_1_no_index  |
| features                   |
| orders                     |
| features2                  |
| featurestable              |
| featurestable1             |
| featurestable10            |
+----------------------------+

We need to create a table to retrieve the data from the CSV file. Here we will create a “featurelisttable” table. Using the same data type as described in the file is recommended.

CREATE TABLE featurelisttable (featurename string, version float);

​

Step #5: Copy the CSV File Into Table

Now, copy “feature2.csv” by executing the COPY FROM query below:

COPY featurelisttable FROM 's3://oxla-testdata/cayo/feature2.csv';

You will get the following successful result:

--
(0 rows)

​

Step #6: Retrieve the Table

To verify that the data was imported correctly from the server, retrieve all the data using the SELECT statement:

SELECT * FROM featurelisttable;

You will 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        |
+-----------------+----------+