2

I went to the psql commandline mode and entered the correct database and I can list all the tables.

Now, I tried the following commands:

copy some_table_name1 to '/var/lib/pgsql/csv_exports/some_table_name1.csv' csv header
copy some_table_name2 to '/var/lib/pgsql/csv_exports/some_table_name2.csv' csv header

And so on...

There was no error messages or anything after the commands and I used tab-button to ensure that I was always referring to correct table names.

After doing this to all the tables I went to the directory and there were no files at all.

Am I doing something wrong?

EDIT: I should clarify that I was looking to that directory, by using putty and WINSCP, on the server machine. The same where I ran the psql commands in.

Steve Waters
  • 3,348
  • 9
  • 54
  • 94
  • no error message? Normally this command should do: `COPY table TO '/tmp/table.csv' DELIMITER ',' CSV HEADER` Also, make sure you're not looking into `/var/lib/` in the client machine ;-) in order for you to use `COPY` to have the file in the client you have to use the standard output. Let me know if it is what you're looking for. – Jim Jones Mar 27 '18 at 14:24
  • what do you get using this approach? `$ psql yourdb -c "COPY yourtable TO STDOUT DELIMITER ',' CSV HEADER" > output.csv` – Jim Jones Mar 28 '18 at 10:48

3 Answers3

0

The files are written to that directory on the server machine, not the client.

Use COPY ... TO STDOUT to send the data to the client.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I updated the question. I created the folder to the same machine that I was connected to and where I ran the psql commands in. – Steve Waters Mar 28 '18 at 07:38
  • Then my answer doesn't hit it and I will delete it. What you describe cannot happen (unless something goes and removes the files). Is this some weird file system that swallows files? – Laurenz Albe Mar 28 '18 at 07:45
0

Using psql in the console you can use the following command to get your data in the client machine:

$ psql yourdb -c "COPY yourtable TO STDOUT DELIMITER ',' CSV HEADER" > output.csv

If you're wondering about how to do the other way around (import), take a look at this question.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
0

Assuming that what you really want to do is to output the file to someplace on your local machine (ie your developer workstation), I suggest that you use the "\copy" command, instead of the "COPY" command.

psql -c "\copy (SELECT * FROM account) to '/tmp/account.csv' with csv;"

or

psql -c "\copy account TO '/tmp/account.csv' DELIMITER ',' CSV HEADER;

Otherwise, unless you explicitly redirect the command to stdout, as @LaurenzAlbe suggested, the "COPY"command will check and see if you are authorized to write the files out to the actual database server. Normally, this is not the behavior that you and, and it requires accesses and permissions greater than most developers have.

A. Rick
  • 649
  • 5
  • 11