COPY FROM STDIN

Overview

The COPY FROM STDIN command imports data directly from the client into a table. It simplifies the copy process by eliminating the need to transfer files to the server.

Syntax

The basic syntax for using COPY FROM STDIN is:

COPY table_name FROM stdin;

Parameters:

• table_name: The table where the data will be imported
• stdin: Indicates that the data comes from the standard input (client application)

- Format: Only .csv is supported
- Delimiter: For CSV format, the default delimiter is a comma (,)

Additional Options

1. Listing Column Names You can specify the columns into which the data should be imported.

COPY table_name (column1, column2) FROM stdin;

2. Options You can include additional options following FROM stdin to customize the import process.

COPY table_name FROM STDIN WITH (FORMAT csv, DELIMITER ',');

Examples

Importing Data Manually

1. Ensure the table exists in your database. If it doesn’t, create one using the following command:

CREATE TABLE country (
    code TEXT,
    name TEXT
);

2. You should see the output:

CREATE

3. Initiate the import operation by running the following command:

COPY country FROM stdin;

4. You will be prompted to enter your data. There will be a message as shown below:

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 

5. Paste the data directly from your CSV file into the prompt:

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> FR,FRANCE
DE,GERMANY
IT,ITALY
ES,SPAIN
GB,UNITED KINGDOM
NL,NETHERLANDS
BE,BELGIUM
CH,SWITZERLAND
AT,AUSTRIA
SE,SWEDEN

6. To end the import process, enter \. or an EOF signal.

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> FR,FRANCE
DE,GERMANY
IT,ITALY
ES,SPAIN
GB,UNITED KINGDOM
NL,NETHERLANDS
BE,BELGIUM
CH,SWITZERLAND
AT,AUSTRIA
SE,SWEDEN
>> >> >> >> >> >> >> >> >> >> \.

An EOF (End of File) signal is used to indicate the end of input data and to terminate the input process. On Unix-like systems, press Ctrl + D.

7. If the import is successful, you will see IMPORT 0 at the end of the line.

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> FR,FRANCE
DE,GERMANY
IT,ITALY
ES,SPAIN
GB,UNITED KINGDOM
NL,NETHERLANDS
BE,BELGIUM
CH,SWITZERLAND
AT,AUSTRIA
SE,SWEDEN
>> >> >> >> >> >> >> >> >> >> IMPORT 0

The IMPORT 0 message doesn’t reflect the number of rows imported. This feature is planned for future development.

8. Verify the imported data by querying the table:

SELECT * FROM country;

9. The output should show that the data has been successfully imported into the table:

 code |      name      
------+----------------
 FR   | FRANCE
 DE   | GERMANY
 IT   | ITALY
 ES   | SPAIN
 GB   | UNITED KINGDOM
 NL   | NETHERLANDS
 BE   | BELGIUM
 CH   | SWITZERLAND
 AT   | AUSTRIA
 SE   | SWEDEN
(10 rows)

Importing a CSV File Directly

To import a CSV file directly into Oxla, use the following steps. This method bypasses the need to manually enter data by reading the file and importing it directly into Oxla.

1. Create the Table (if not already created):

CREATE TABLE country (
    code TEXT,
    name TEXT
);

2. Exit psql:

\q

3. Import the CSV File:

cat myfile.csv | psql -c "COPY country FROM STDIN WITH (FORMAT csv, DELIMITER ',');"

This command reads the contents of myfile.csv and passes it directly to the COPY command.

4. Re-enter psql and verify the table contents:

SELECT * FROM country;