Hey there, let's break down the problem you're facing first—your original COPY command isn't working because it's a server-side operation, not a client-side one. When you run COPY (SELECT * FROM Table_Name) TO 'C:\somedirectory\file.csv', PostgreSQL tries to create that file on the server's filesystem, not your local client machine. That's why you're getting the "relative path not allowed" error (and even if it didn't throw that, the server can't access your local C:\ drive anyway!).

Here are two straightforward solutions to get the data onto your client machine:

1. Use \copy (psql Client Command - Recommended) #

\copy is a psql-specific command that runs on your local client. It pulls the data from the server first, then writes it directly to your local file system.

Just run this in your psql terminal (not a server-side SQL editor like pgAdmin's query tool):

\copy (SELECT * FROM Table_Name) TO 'C:\somedirectory\file.csv' DELIMITER ',' CSV HEADER;

A few quick notes:

• Make sure the C:\somedirectory folder exists on your client machine before running this—psql won't create it for you.
• If you're on Linux/macOS, swap the path to a Unix-style one, like /home/your-username/somedirectory/file.csv.

2. Redirect Server COPY Output to Local File (Great for Scripts) #

If you want to automate this without opening a psql shell, you can use the server's COPY TO STDOUT command and redirect the output to a local file from your client's command line.

On Windows Command Prompt:

psql -U your-db-username -d your-database-name -c "COPY (SELECT * FROM Table_Name) TO STDOUT DELIMITER ',' CSV HEADER" > C:\somedirectory\file.csv

On Linux/macOS Terminal:

psql -U your-db-username -d your-database-name -c "COPY (SELECT * FROM Table_Name) TO STDOUT DELIMITER ',' CSV HEADER" > /home/your-username/somedirectory/file.csv

This skips the interactive psql session and dumps the data straight to your local file—perfect for batch jobs or scripts.

Quick Recap on Why Your Original Command Failed #

To clarify once more:

• Server-side COPY ... TO 'file-path' creates the file on the PostgreSQL server's machine, not yours. It also requires the PostgreSQL service account to have write permissions to that server-side path.
• If you really wanted to use server-side COPY, you'd have to save the file to a directory on the server first, then transfer it to your client via FTP/SCP—but that's way more work than using \copy.

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