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?

Answer 1

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
db=>\q

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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' cvs header;

Hope this helps you.

Answer 6

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

Answer 7

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

this command is used to store the entire table as csv

Answer 8

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.

Answer 9

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

Answer 10

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