导入CSV格式的数据到PostgreSQL表中

最新推荐文章于 2023-04-27 18:48:54 发布

转载最新推荐文章于 2023-04-27 18:48:54 发布 · 2.2k 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://popsql.com/learn-sql/postgresql/how-to-import-a-csv-in-postgresql/

文章标签：

#import #csv #data #into #postgresql

PostgreSQL 专栏收录该内容

211 篇文章

订阅专栏

本文详细介绍了如何在PostgreSQL中导入CSV文件，包括创建表、使用copy或copy命令进行导入的过程，特别针对本地环境与远程数据库连接的情况给出了解决方案。

https://popsql.com/learn-sql/postgresql/how-to-import-a-csv-in-postgresql/

How to Import a CSV in PostgreSQL

Importing a CSV into PostgreSQL requires you to create a table first. Duplicating an existing table’s structuremight be helpful here too.

The commands you need here are copy (executed server side) or \copy (executed client side). The former requires your database to be able to access the CSV file, which is rarely going to work for you in a production environment like Amazon RDS because you’re not going to be uploading random CSV files to your database server. However, if you use psql on your local machine and you’re connected to your remote database, you can use the latter \copy command, assuming your machine can access the CSV file.

In your terminal, let’s open psql:

psql your_database_name # or postgres://username:password@amazonaws.com

Now it’s time to use the \copy command:

-- Assuming you have already created an imported_users table
-- Assuming your CSV has no headers
\copy imported_users from 'imported_users.csv' csv;

-- If your CSV does have headers, they need to match the columns in your table
\copy imported_users from 'imported_users.csv' csv header;

-- If you want to only import certain columns
\copy imported_users (id, email) from 'imported_users.csv' csv header;