psql - save results of command to a file

Question

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

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

See also http://stackoverflow.com/questions/3939329/putting-output-from-postgres-i-input-to-a-file. — Matt Solnit, Mar 16 '11 at 21:06

score 671 · Accepted Answer · edited Nov 23 '21 at 08:17

671

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

edited Nov 23 '21 at 08:17

Haili Sun

621
8
13

answered Mar 16 '11 at 20:50

jhwist

15,201
3
40
47

2

do i call this prior to the \dt, or in combination? please include syntax thanks. – pstanton Mar 16 '11 at 20:59
180

Typing `\o` again turns it off. – Carl G Oct 28 '15 at 22:17
3

Sadly, the output of `\?` does not go to the file. :( – blitzen9872 Feb 10 '17 at 18:47
for your kind perusual permission denied says try to run psql as administrator – Ajay Takur Nov 08 '17 at 13:39
(windows) worked only as admin and filespec without path. File ended up in C:\windows\system32. Ugh – Jan Feb 26 '19 at 22:25
1

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. – hygull May 02 '19 at 05:14
@blitzen9872 Yes, same here. I used `psql -c '\?' > psql_help.yml` to have the output of `\?` inside the file psql_help.yml – Asme Just Jan 20 '20 at 20:03
`psql -af file.sql > file.out` for getting input from a sql file and -a to show commands in output too. redirect all to file.out – Andrei-Niculae Petre Sep 16 '21 at 20:48

intgr · Answer 2 · 2022-09-26T13:01:29.687

123

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

edited Sep 26 '22 at 13:01

answered Mar 16 '11 at 21:49

intgr

19,834
5
59
69

I agree @helvete, the approach presented here provides more power for the user to configure output in a more customizing way – nate Apr 24 '19 at 12:57
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. – Tim V Sep 25 '22 at 05:55
@TimV Maybe you missed it, but my answer already stated that: "NB! This requires superuser privileges" – intgr Sep 25 '22 at 11:11
1

@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. – Tim V Sep 25 '22 at 16:05
1

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. – jwadsack Dec 05 '22 at 22:53

score 59 · Answer 3 · answered Jan 08 '20 at 09:10

59

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

answered Jan 08 '20 at 09:10

Yavuz

1,257
1
16
32

2

Thanks, was looking for a version that could be sent from CL. – otocan Dec 09 '21 at 08:51
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) – Peter L Jul 01 '22 at 00:41
2

This is the best answer in my opinion – Arka Mukherjee Dec 10 '22 at 02:17

score 35 · Answer 4 · edited Oct 08 '18 at 16:18

35

\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;

edited Oct 08 '18 at 16:18

John Clements

16,895
3
37
52

answered Jul 08 '16 at 21:36

Aakash Gupta

716
6
11

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 Jul 21 '16 at 14:24
Tom, I think it was a typo. Edited the post with the one that works on my installation pgsql 8.5ver – Aakash Gupta Jul 23 '16 at 12:12
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. – whoasked May 25 '18 at 06:46
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. – raphael75 Mar 19 '20 at 16:10

score 22 · Answer 5 · edited Apr 09 '23 at 17:59

22

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;

edited Apr 09 '23 at 17:59

Henry Ecker

34,399
18
41
57

answered Sep 24 '19 at 09:03

Er.Ankit H Gandhi

578
4
7

score 7 · Answer 6 · edited Sep 29 '20 at 13:54

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

score 4 · Answer 7 · edited Sep 29 '20 at 13:55

4

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

this command is used to store the entire table as csv

edited Sep 29 '20 at 13:55

Deepak Mahakale

22,834
10
68
88

answered Oct 12 '17 at 07:40

Stephen

834
7
13

In postgres COPY is better replaced by \COPY to avoid need of db admin. In windows this puts the file in C:\tmp – Jan Feb 27 '19 at 10:18

score 1 · Answer 8 · answered Mar 16 '11 at 20:55

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.

answered Mar 16 '11 at 20:55

hlovdal

26,565
10
94
165

score 1 · Answer 9 · answered Jan 24 '20 at 07:47

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

answered Jan 24 '20 at 07:47

Roman Rhrn Nesterov

3,538
1
28
16

score 0 · Answer 10 · answered Jan 14 '20 at 15:44

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

psql - save results of command to a file

10 Answers10

Linked