COPY FROM statement
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 importedstdin
: 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 (,)
- Delimiter: For CSV format, the default delimiter is a comma (,)
Additional Options
- Listing Column Names You can specify the columns into which the data should be imported.
COPY table_name (column1, column2) FROM stdin;
- 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
- Ensure the table exists in your database. If it doesn’t, create one using the following command:
CREATE TABLE country (
code TEXT,
name TEXT
);
- You should see the output:
CREATE
- Initiate the import operation by running the following command:
COPY country FROM stdin;
- 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.
>>
- 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
- 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
.- 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.- Verify the imported data by querying the table:
SELECT * FROM country;
- 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.
- Create the Table (if not already created):
CREATE TABLE country (
code TEXT,
name TEXT
);
- Exit
psql
:
\q
- 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.
- Re-enter
psql
and verify the table contents:
SELECT * FROM country;