website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

COPY FROM statement

19min

In this article, you will learn how to copy the data from a file into a table.

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.

The file must be accessible and able to be read and written to.

Syntax

The syntax for COPY FROM is as follows:

Syntax
|
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:

Syntax to import
|
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:

Import the file
|
aws s3 cp ~/Documents/feature2.csv s3://oxla-testdata/cayo/feature2.csv


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

File uploaded
|
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:

Connect to Oxla
|
psql -h buildfarm.oxla.com -p 6000


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

Connected to Oxla
|
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
|
DESCRIBE DATABASE


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

List of tables
|
+----------------------------+
| name                       |
+----------------------------+
| supplier_scale_1_no_index  |
| features                   |
| orders                     |
| features2                  |
| featurestable              |
| featurestable1             |
| featurestable10            |
+----------------------------+


Ensure you are not creating duplicate tables.

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
|
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 file
|
COPY featurelisttable FROM 's3://oxla-testdata/cayo/feature2.csv';


You will get the following successful result:

Copy file
|
--
(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:

Display the table
|
SELECT * FROM featurelisttable;


You will have the same data in the table as in the CSV file.

Output
|
+-----------------+----------+
| featurename     | version  | 
+-----------------+----------+
| create a table  | 1        |
| modify a table  | 1.2      |
| drop a table    | 2.2      |
| rename a table  | 2        |
+-----------------+----------+




Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
DESCRIBE statement
NEXT
COPY FROM with Delimiter
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Syntax
Example
Step #1: Create a CSV File
Step #2: Import FIle from Local to Server
Step #3: Connect to Oxla Server
Step #4: Create a Table
Step #5: Copy the CSV File Into Table
Step #6: Retrieve the Table

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2022 Oxla