Windows系统下pgAdmin4中psql的\copy命令执行失败求助

Got it, let's break down how to get your CSV import working with \copy on Windows—since the shell script approach you saw is for Linux/macOS, but Windows has straightforward alternatives:

Method 1: Use the Built-in SQL Shell (Easiest Option) #

This is the simplest way because it's pre-configured to work with PostgreSQL out of the box:

• Open your Windows Start Menu, search for SQL Shell (psql) and launch it.
• You'll be prompted for connection details:
  • Server: Just press Enter (defaults to localhost)
  • Database: Type the name of the database you want to import into, then press Enter
  • Username: Enter your PostgreSQL username (e.g., postgres), press Enter
  • Port: Press Enter (defaults to 5432)
  • Password: Type your database password (it won't show on screen), then press Enter
• Once you're logged in (you'll see a prompt like your_database=#), run your \copy command. For example:

  \copy your_target_table FROM 'C:\Users\YourUsername\Desktop\your_data.csv' WITH (FORMAT csv, HEADER, DELIMITER ',');
  

  Quick tips for the command:
  • Replace your_target_table with the actual name of your table
  • Use single quotes around the file path—Windows backslashes (\) work fine, or you can use forward slashes (/) to avoid any escape character headaches
  • Add HEADER if your CSV has a header row; adjust the DELIMITER if your file uses something other than commas (e.g., tabs would be DELIMITER E'\t')

Method 2: Use CMD or PowerShell #

If you prefer using Windows' native command line tools:

1. Locate your psql executable: It's typically in C:\Program Files\PostgreSQL\<your_postgres_version>\bin (e.g., C:\Program Files\PostgreSQL\15\bin).
2. Run psql directly:
   • Open CMD or PowerShell, then run psql with your connection details (wrap the path in quotes if it has spaces):

     "C:\Program Files\PostgreSQL\15\bin\psql.exe" -h localhost -d your_database -U your_username
     

     Enter your password when prompted to log in.
   • Alternatively, add the PostgreSQL bin folder to your system's Path environment variable so you can run psql from anywhere:
     • Right-click "This PC" → "Properties" → "Advanced System Settings" → "Environment Variables"
     • Under "System Variables", find Path, click "Edit", then add C:\Program Files\PostgreSQL\<your_version>\bin
     • Restart CMD/PowerShell, then just run psql -h localhost -d your_database -U your_username to log in
3. Once logged in, execute the same \copy command as above.

Key Things to Remember #

• Unlike the server-side COPY command (which failed due to permission issues), \copy is a client-side command. It uses your Windows user's file permissions, so make sure you can access the CSV file (avoid saving it in restricted folders like C:\ root without admin rights).
• If your file path has spaces, always wrap it in single quotes (e.g., 'C:\Users\Your Full Name\Documents\data.csv').
• If you still get errors, double-check:
  • That your table's columns match the CSV's columns in order and data type
  • The file's encoding matches your database (add ENCODING 'UTF8' to the WITH clause if needed)
  • You didn't mistype the file path or table name

内容的提问来源于stack exchange，提问作者J.Delannoy