PostgreSQL - Import CSV File Into Table
Last Updated :
20 Aug, 2024
Importing data from a CSV (Comma-Separated Values) file into a PostgreSQL database is a common task for database administrators and developers. PostgreSQL provides the COPY command, which is a powerful and efficient way to bulk load data directly from a CSV file into a table.
In this article, we will discuss the process of importing a '.csv' file into a PostgreSQL table.
Steps to Import CSV Data into PostgreSQL
Let us look at the steps of Importing CSV Data into a PostgreSQL Table.
Step 1: Creating a Target Table in PostgreSQL
To do so we will require a table which can be obtained using the below command:
CREATE TABLE persons
(
id serial NOT NULL,
first_name character varying(50),
last_name character varying(50),
dob DATE,
email character varying(255),
CONSTRAINT persons_pkey PRIMARY KEY (id)
);
This table will store personal information such as 'first_name', 'last_name', 'dob' (date of birth), and 'email'.
Step 2: Preparing the CSV File
Now we create a '.csv' file in our sheet manager (eg: MS Excel or notepad) as shown below. The structure of your CSV file should match the columns in the 'persons' table.
Our file is located as 'persons.csv' at 'C:\Users\Raju'.
Step 3: Importing the CSV File into PostgreSQL
To import the CSV file into the 'persons' table, you can use the 'COPY' statement. This statement reads the data from the CSV file and inserts it into the specified table.
COPY persons(first_name, last_name, dob, email)
FROM 'C:\Users\Raju' DELIMITER ', ' CSV HEADER;
Step 4: Verifying the Data Import
After running the 'COPY' statement, you can verify that the data has been imported successfully by querying the 'persons' table:
SELECT * FROM persons;
It will lead to the below Output:
Important Considerations When Importing CSV Files into PostgreSQL
- It is important to put the CSV file path after the FROM keyword. Because CSV file format is used, you need to mention the DELIMITER as well as 'CSV' keywords.
- The HEADER keyword indicates that the CSV file comprises a header line with column names.
- When importing data, PostgreSQL neglects the first line as they are the header line of the file. The file must be read directly by the PostgreSQL server and not by the client application. Therefore, it must be accessible to the PostgreSQL server machine.
- Also, you can execute the COPY statement successfully if you have superusers access.
Similar Reads
PostgreSQL - Export PostgreSQL Table to CSV file In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine. For Server-Side Export: Use the below syntax to copy a PostgreSQL table from the server itself: Syntax: COPY Table_Name TO 'Path/fil
2 min read
PostgreSQL - Create Tables in Python Creating tables in PostgreSQL using Python is an essential skill for developers working with databases. This article will explore the process of creating new tables in the PostgreSQL database using Python.Why Create PostgreSQL Tables with Python?Using Python to create PostgreSQL tables is beneficial
4 min read
PostgreSQL - INSERT PostgreSQL INSERT statement is one of the fundamental SQL commands used to add new rows to a specified table within a PostgreSQL database. This command allows users to insert data efficiently, whether for a single record or multiple records at once. With the PostgreSQL INSERT INTO clause, we can spe
4 min read
How to Insert Multiple Rows to a Table in PostgreSQL? Inserting multiple rows into a table in PostgreSQL is a common and efficient operation, especially when handling large datasets. By executing a single SQL query, multiple rows can be added simultaneously, reducing overhead and enhancing performance. This method is particularly valuable in scenarios
5 min read
PostgreSQL - Create table using Python Creating tables in a PostgreSQL database using Python is a common task for developers working with databases. This process involves defining the structure of your data and ensuring that your database is optimized for efficient storage and retrieval. In this article, we will walk through the steps of
3 min read
How to Dump and Restore PostgreSQL Database? PostgreSQL remains among the most efficient and widely applied open-source relational database management systems. It provides the superior function of saving, configuring, and extracting information most effectively. In the process of migrating data, creating backups, or transferring databases betw
6 min read
PostgreSQL - Insert Multiple Values in Various Rows PostgreSQL, one of the most popular relational database management systems (RDBMS), is widely used for storing structured data in a tabular format, much like MySQL. In relational databases, data is stored in tables where each row represents a record and each column represents an attribute. One of th
3 min read
How to Migrate a PostgreSQL Database to MySQL Moving a database from one platform to another can be tough, but with careful planning and execution, it can be done smoothly. In this article, we'll go over how to migrate a PostgreSQL database to MySQL, which are both popular RDBMS. We'll cover preparation, schema conversion, data migration, and t
5 min read
How to Migrate a MySQL Database to PostgreSQL using pgloader? Database migration is a common task in software development when switching between different database management systems (DBMS). In this article, we'll explore how to migrate a MySQL database to PostgreSQL using a powerful tool called pgloader. We'll cover the concepts involved, and the steps requir
6 min read
How To Connect and run SQL Queries to a PostgreSQL Database from Python In this PostgreSQL Python tutorial, we will explain how to connect to a PostgreSQL database using Python and execute SQL queries. Using the powerful psycopg2 library, we can seamlessly interact with our PostgreSQL database from Python, making it easy to perform tasks like inserting, updating, and re
4 min read