Import CSV Into Postgres
[#importing-data-into-existing-table]Importing Data into an Existing Table from a CSV file[#importing-data-into-existing-table]
[#using-psql-to-copy-data]Using the [.inline-code]psql[.inline-code] command-line tool to copy data from a CSV[#using-psql-to-copy-data]
One option for importing a CSV file in PostgreSQL is to use the [.inline-code]psql[.inline-code] command-line tool combined with the [.inline-code]-c[.inline-code] option flag, which allows you to execute a given command string in line:
Where:
- [.inline-code]host_name[.inline-code] is the address of the server the database runs on
- [.inline-code]user_name[.inline-code] is the name of the PostgreSQL user you want to execute the command as.
- [.inline-code]database_name[.inline-code] is the name of the database you want to connect to.
- [.inline-code]table_name[.inline-code] is the name of the table you want to import the data into.
- [.inline-code]file_path[.inline-code] is the path to the CSV file you want to load.
For example:
Note that if you want to import a file on a remote database server, you will need to upload the file on the target machine using the [.inline-code]scp[.inline-code] command first:
If you want to use this same approach starting from a SQL file instead of a CSV, you can read our post on how to import a SQL file in PostgreSQL.
[#importing-csv-using-postgres]Importing a CSV file using the Postgres interface[#importing-csv-using-postgres]
Another option for importing a CSV file in PostgreSQL is to connect to the database using the [.inline-code]psql[.inline-code] command:
Then use the [.inline-code]\copy[.inline-code] meta-command:
Where:
- [.inline-code]table_name[.inline-code] is the name of the table you want to import the data into.
- [.inline-code]file_path[.inline-code] is the path to the CSV file you want to import.
For example:
Note that the [.inline-code]WITH[.inline-code] clause is optional, but it allows you to specify the format of the file ([.inline-code]CSV[.inline-code]), whether the first row contains header information ([.inline-code]HEADER true[.inline-code]), and the delimiter used in the file ([.inline-code]DELIMITER ','[.inline-code]).
[#importing-csv-pgAdmin]Importing a CSV file using pgAdmin[#importing-csv-pgAdmin]
It is also possible to import a CSV file in PostgreSQL using GUI tools such as pgAdmin, a popular open-source management tool for PostgreSQL. It allows you to import data into a table by going to the "Import/Export" option, selecting the file to import, specifying the format, delimiter, encoding, and the column names.
[#common-errors]Common errors[#common-errors]
Importing a CSV file might fail for various reasons. Here is a list of the most common errors you might encounter, and that you will need to resolve before the file can successfully be imported.
[#file-not-found][.inline-code]undefined_file[.inline-code]: File not found[#file-not-found]
The [.inline-code]undefined_file[.inline-code] error indicates that the file you are trying to import doesn’t exist. Make sure that the file actually exists, and that the provided file path is valid.
[#incorrect-format-or-delimiter][.inline-code]syntax_error[.inline-code]: Incorrect format or delimiter[#incorrect-format-or-delimiter]
The [.inline-code]syntax_error[.inline-code] indicates that the format of the CSV file or the delimiter used in the file does not match the specifications provided in the import command, which may result in the import failing or the data being corrupted.
[#incorrect-data-types][.inline-code]datatype_mismatch[.inline-code]: Incorrect data types[#incorrect-data-types]
The [.inline-code]datatype_mismatch[.inline-code] error indicates that the data types of the columns in the CSV file do not match the data types of the columns in the target table.
[#incorrect-encoding][.inline-code]invalid_parameter_value[.inline-code]: Incorrect encoding[#incorrect-encoding]
The [.inline-code]invalid_parameter_value[.inline-code] error could indicate that the CSV file is not encoded in UTF-8, which may result in the import failing or the data being corrupted.
[#not-null-violation][.inline-code]null_value_not_allowed[.inline-code]: Not null violation[#not-null-violation]
The [.inline-code]null_value_not_allowed[.inline-code] error indicates that the CSV file contains null values and the import is trying to insert that into the table which has a not null constraint.
[#creating-postgres-table-from-csv]Creating a Postgres Table from a CSV file[#creating-postgres-table-from-csv]
Unfortunately, PostgreSQL doesn't provide a native way to create a new table from a CSV file. However, you can use the `csvsql` command-line tool instead, which can be downloaded using the [.inline-code]pip[.inline-code] command:
To create a table from a CSV file and import the data it contains all at once, you can run the following command:
Where:
- [.inline-code]user_name[.inline-code] is the database user you want to execute the command as.
- [.inline-code]user_password[.inline-code] is the password of the user you want to connect as.
- [.inline-code]host_name[.inline-code] is the name of the host the PostgreSQL server runs on.
- [.inline-code]port_number[.inline-code] is the port the PostgreSQL server listens to.
- [.inline-code]database_name[.inline-code] is the name of the database you want to create a table in.
Note that the [.inline-code]csvsql[.inline-code] command will automatically create the table based on the provided file name (e.g. [.inline-code]users.csv[.inline-code] will create the table [.inline-code]users[.inline-code]) and assume the column types based on the values contained in the CSV file.
Written by
