470

I'm using psql's \dt to list all tables in a database and I need to save the results.

What is the command to export the results of a psql command to a file?

Improve this question
1

10 Answers 10

Reset to default
736

From psql's help (\?):

\o [FILE] send all query results to file or |pipe

The sequence of commands will look like this:

[wist@scifres ~]$ psql db Welcome to psql 8.3.6, the PostgreSQL interactive terminal db=>\o out.txt db=>\dt

Then any db operation output will be written to out.txt. Enter '\o' to revert the output back to console.

db=>\o
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

do i call this prior to the \dt, or in combination? please include syntax thanks.
Typing \o again turns it off.
Sadly, the output of \? does not go to the file. :(
for your kind perusual permission denied says try to run psql as administrator
Yes, \o queries-output.txt redirects all the subsequent commands o/p to file named queries-output.txt and typing \o (on psql prompt again) reverts this redirection behavior.
|
136

The psql \o command was already described by jhwist.

An alternative approach is using the COPY TO command to write directly to a file on the server. This has the advantage that it's dumped in an easy-to-parse format of your choice -- rather than psql's tabulated format. It's also very easy to import to another table/database using COPY FROM.

NB! This requires superuser or pg_write_server_files privileges and will write to a file on the server.

Example: COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';')

Creates a CSV file with ';' as the field separator.

As always, see the documentation for details

Improve this answer

5 Comments

I agree @helvete, the approach presented here provides more power for the user to configure output in a more customizing way
Note! (in 12.12) I get this error message: "must be superuser or a member of the pg_write_server_files role to COPY to a file". But \copy (shown below) works.
@TimV Maybe you missed it, but my answer already stated that: "NB! This requires superuser privileges"
@intgr thanks and sorry yes I did miss it (dozy old git that I am!) though I guess pg_write_server_files might be a useful role for some.
If you change your psql settings with pset format csv then it will write the output as a CSV. One advantage is that \o in psql writes to your local system, rather than the server which is probably where you want the file.
75

Use o parameter of pgsql command.

-o, --output=FILENAME send query results to file (or |pipe)

psql -d DatabaseName -U UserName -c "SELECT * FROM TABLE" -o /root/Desktop/file.txt
Improve this answer

3 Comments

Thanks, was looking for a version that could be sent from CL.
I needed to add -h localhost and do read PGPASSWORD before hand to avoid password prompts (as I was looping over a list of table names)
This is the best answer in my opinion
40

\copy which is a postgres command can work for any user. Don't know if it works for \dt or not, but general syntax is reproduced from the following link Postgres SQL copy syntax

\copy (select * from tempTable limit 100) to 'filenameinquotes' with header delimiter as ','

The above will save the output of the select query in the filename provided as a csv file

EDIT:

For my psql server the following command works this is an older version v8.5

copy (select * from table1) to 'full_path_filename' csv header;
Improve this answer

4 Comments

Very handy, thanks. But the 'copy' in '... copy to ...' isn't required - in fact on the recent versions it causes the command to fail.
Tom, I think it was a typo. Edited the post with the one that works on my installation pgsql 8.5ver
Just a quick note to anyone attempting to paste in a multi-line statement after \copy and getting confusing syntax errors, as I just did. You need to continue on the same line as \copy.
Is there a way to turn the \copy off? I found that if I run a statement like in your example, and then I run, for example, select * from users; that it will append the results to the most-recent file I specified instead of outputting to the screen. Thank you.
30

Use the below query to store the result in a CSV file

\copy (your query) to 'file path' csv header;

Example

\copy (select name,date_order from purchase_order) to '/home/ankit/Desktop/result.csv' csv header;
Improve this answer

1 Comment

this does not work in the case of \d, as required by the user, for example: \copy (\d+ MY_VIEW;) to /my_path/file.txt ERROR: syntax error at or near "\" LINE 1: COPY ( \d+ MY_VIEW; ) TO STDOUT
8

If you got the following error

ufgtoolspg=> COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';'); ERROR: must be superuser to COPY to or from a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

you can run it in this way:

psql somepsqllink_or_credentials -c "COPY (SELECT foo, bar FROM baz) TO STDOUT (format csv, delimiter ';')" > baz.csv
Improve this answer

Comments

4 
COPY tablename TO '/tmp/output.csv' DELIMITER ',' CSV HEADER;

this command is used to store the entire table as csv

Improve this answer

1 Comment

In postgres COPY is better replaced by \COPY to avoid need of db admin. In windows this puts the file in C:\tmp
1

I assume that there exist some internal psql command for this, but you could also run the script command from util-linux-ng package:

DESCRIPTION Script makes a typescript of everything printed on your terminal.

Improve this answer

Comments

1

Approach for docker

via psql command

 docker exec -i %containerid% psql -U %user% -c '\dt' > tables.txt

or query from sql file

docker exec -i %containerid% psql -U %user% < file.sql > data.txt
Improve this answer

Comments

0

This approach will work with any psql command from the simplest to the most complex without requiring any changes or adjustments to the original command.

NOTE: For Linux servers.

  • Save the contents of your command to a file

MODEL

read -r -d '' FILE_CONTENT << 'HEREDOC' [COMMAND_CONTENT] HEREDOC echo -n "$FILE_CONTENT" > sqlcmd

EXAMPLE

read -r -d '' FILE_CONTENT << 'HEREDOC' DO $f$ declare curid INT := 0; vdata BYTEA; badid VARCHAR; loc VARCHAR; begin FOR badid IN SELECT some_field FROM public.some_base LOOP begin select 'ctid - '||ctid||'pagenumber - '||(ctid::text::point) [0]::bigint into loc from public.some_base where some_field = badid; SELECT file||' ' INTO vdata FROM public.some_base where some_field = badid; exception when others then raise notice 'Block/PageNumber - % ',loc; raise notice 'Corrupted id - % ', badid; --return; end; end loop; end; $f$; HEREDOC echo -n "$FILE_CONTENT" > sqlcmd
  • Run the command

MODEL

sudo -u postgres psql [some_db] -c "$(cat sqlcmd)" >>sqlop 2>&1

EXAMPLE

sudo -u postgres psql some_db -c "$(cat sqlcmd)" >>sqlop 2>&1

  • View/track your command output

cat sqlop

Done! Thanks! =D

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.