This tutorial will show you how to import data from a CSV file into a PostgreSQL database table.
What is a CSV File
A CSV (Comma Separated Values) file is a text file that contains tabulated information. The rows in this data each take up a line in the text file, while the values in each column on each row are separated by a comma – hence, comma-separated.
CSV files are versatile. Because the data within is stored as plain text, they can be opened on pretty much any device and read by any program or script. They are easy to write, easy to parse through in your programming code, and are also simple to generate programmatically.
CSV files do come in a few different flavours – some forgo the comma in place of a semicolon or other character, and wrap text in quotes, among other formatting differences depending on the data present, but they are still called CSV files.
What is PostgreSQL
PostgreSQL is a relational database server. Data is stored in tables in rows and columns. This data can then be queried using SQL syntax. PostgreSQL is one of the most popular database solutions, and is used as the record-storage for countless eCommerce, productivity, and other tools.
Importing a CSV File Into a PostgreSQL Table
To import data from a CSV file into a PostgreSQL table, the table structure must match the CSV file – the same number of columns must be present, and the type of data in them should match. If this is not the case, errors will be encountered.
The below example uses the following table:
CREATE TABLE employees (NAME varchar, AGE integer);
…and the following CSV file named employees.csv:
Fred,23 Anne,32 Meghan,60
Note that the name and age in the PostgreSQL table definition are using the varchar (a short string of characters) and integer data types, and that those corresponding values are present in the CSV file in the same order.
The data from the CSV file can be imported into the PostgreSQL table using the following command:
COPY employees FROM 'employees.csv' CSV;
If your CSV file has headers (column names at the top of the file), or uses a delimiter that is not a comma, these can be specified:
Example CSV file with header and semicolon delimiter:
name;age Fred;23 Anne;32 Meghan;60
This would be imported using the command:
COPY employees FROM 'employees.csv' DELIMITER ';' CSV HEADER;
