Importing CSV file data into a table in PostgreSQL
A CSV file is .csva text file with a .CSV extension and the contents are separated by commas. This file can achieve different goals, such as loading data into database tables and importing data into Google and Excel spreadsheets.
When working with spreadsheets, you can also export the data to a CSV file and use that data in other functions.
This article demonstrates how to populate a table in a PostgreSQL database using a CSV file.
Step-by-step guide to importing CSV file data into a PostgreSQL table
Log in to the PostgreSQL server using the following command. Type your password in the prompt and press Enter.
david@david-HP-ProBook-6470b:~$ psql -U postgres
Password for user postgres:
Create a database where we will place the data from the CSV file.
postgres=# create database csv_db;
Connect to the database csv_db.
postgres=# \c csv_db;
You are now connected to database "csv_db" as user "postgres".
Create a table called product with the columns id, product_name, product_type, and product_price.
csv_db=# CREATE table product(
csv_db(# id SERIAL UNIQUE NOT NULL,
csv_db(# product_name varchar(50),
csv_db(# product_type varchar(50),
csv_db(# product_price integer,
csv_db(# PRIMARY KEY(id));
CREATE TABLE
Create a CSV file and create some instances of the products table. You can name the file data.csv or anything you like.
Iphone 7, 500, phone
HP probook, 8000, computer
Canon pixma, 3000, printer
To copy the data from the CSV file to the products table, use the copy command with the absolute path to the CSV file and the delimiter that separates the columns. Since the id is automatically generated, we can specify product_name, product_price, and product_type as the only fields we want to insert into the database.
csv_db=# \copy product(product_name, product_price, product_type) FROM '/home/david/Documents/work/upwork/jhinku-tutorials/data.csv' DELIMITER ',' CSV;
COPY 3
Execute the following query to confirm that we have successfully inserted data into the products table.
csv_db=# select * from product;
Output:
id | product_name | product_type | product_price
----+--------------+--------------+---------------
1 | Iphone 7 | phone | 500
2 | HP probook | computer | 8000
3 | Canon pixma | printer | 3000
(3 rows)
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
Killing a process ID in PostgreSQL
Publish Date：2025/04/27 Views：191 Category：PostgreSQL
-
Today, we will learn how to kill or stop a running query in the background when working with PostgreSQL database. This may happen if the frontend stops working but a background process is still running. In this case, you may want to kill th
How to install and deploy PostgreSQL as a Docker container
Publish Date：2025/04/27 Views：50 Category：PostgreSQL
-
PostgreSQL , also known as Postgres, is a leading object-relational database system. It is popular because it is highly compliant with the SQL standard and includes additional features that simplify processing complex data sets at scale. Po
Selecting whether a string contains a substring match in PostgreSQL
Publish Date：2025/04/27 Views：186 Category：PostgreSQL
-
Today, we will learn how to find a value in a PostgreSQL string and select it if it matches certain conditions. Suppose you have a string carabc and want to see if it contains a value car . Therefore, you will try to use a function that tel
Add unique constraint after creating table in PostgreSQL
Publish Date：2025/04/27 Views：63 Category：PostgreSQL
-
Today we will learn how to add constraints after the rows in a table have been created UNIQUE . The UNIQUE constraint guarantees that the data in a row is unique in that column. So if the column ID exists, all rows will have unique values,
Creating a Schema in PostgreSQL
Publish Date：2025/04/27 Views：196 Category：PostgreSQL
-
This article will discuss creating schemas in PostgreSQL using SQL queries or psql. CREATE SCHEMA Use the statement to create a pattern in SQL query To create a new schema, execute the following command. CREATE SCHEMA test_schema To view al
Changing User Password in Postgres
Publish Date：2025/04/27 Views：108 Category：PostgreSQL
-
In this article, we will change the user password in Postgres. Changing User Passwords in Postgres Using Windows Open from the menu or search bar SQL Shell (psql) . Connect to the default database using the default port. If you set it up wi
Changing column types in Postgres
Publish Date：2025/04/27 Views：91 Category：PostgreSQL
-
This article shows how to change a column type to another data type in Postgres. ALTER TABLE To change the column type in Postgres, use the command ALTER TABLE table_name ALTER COLUMN column_name [ SET DATA ] TYPE new_type ; Use 表名 , , 列
Importing SQL files in PostgreSQL
Publish Date：2025/04/27 Views：129 Category：PostgreSQL
-
This article discusses how to import SQL files in PostgreSQL. psql Import SQL files in PostgreSQL using command To import the SQL file, run the following command: psql - U dbuser - h localhost databasename filename. sql If the file is locat
Create a table if it does not exist in PostgreSQL
Publish Date：2025/04/27 Views：197 Category：PostgreSQL
-
PostgreSQL is an object-relational database system, which means it can support more complex data types than its competitor MySQL. Apart from the above differences, when writing queries for PostgreSQL and MySQL or other database systems, the
