PostgreSQL导入CSV文件遇权限拒绝错误，寻求解决帮助

Got it, let's sort out this permission error you're facing. Here's why it's happening and exactly how to fix it:

Why the Error Occurs #

The COPY command you're using tells the PostgreSQL server process to read the file directly. But your CSV is stored in a Windows user directory (C:\Users\lenovo\Downloads\), and the PostgreSQL server runs under a dedicated system account (usually named postgres on Windows). This account doesn't have permission to access files in your personal user folder—hence the "Permission denied" message.

The hint in the error is spot-on: you need a client-side tool like psql's \copy instead.

Solution 1: Use psql's \copy (Recommended) #

\copy works differently—it lets your local psql client read the file and send the data to the server, so you don't have to mess with server-side permissions. Here's how:

1. Open the psql command-line tool and connect to your target database.
2. Run this command (adjust the table name and options to match your CSV):

   \copy owners_table FROM 'C:\Users\lenovo\Downloads\Owners.csv' WITH (FORMAT csv, HEADER, DELIMITER ',');
   

   • Add HEADER only if your CSV has a header row with column names.
   • Change DELIMITER ',' if your CSV uses a different separator (like ;).

Solution 2: Adjust File Permissions (Less Convenient) #

If you really want to use the server-side COPY command, you can grant the PostgreSQL server access to the file:

• Option A: Move the Owners.csv file to a directory the postgres account can access (e.g., the PostgreSQL data directory, or a new folder you create specifically for imports).
• Option B: On Windows, right-click the CSV file → Properties → Security → Edit → Add. Search for the postgres user (click "Advanced" → "Find Now" if it doesn't show up), then grant it Read permissions. After that, your original COPY command should work.

Quick Note #

Make sure your PostgreSQL table's columns match the order and data types in your CSV—otherwise you might run into additional errors after fixing the permission issue.

内容的提问来源于stack exchange，提问作者Om Kumar