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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  o out txt db  gt  dt db  gt  q

User · Answer

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  lt  lt   HEREDOC   COMMAND CONTENT   HEREDOC echo -n   FILE CONTENT   gt  sqlcmd   EXAMPLE  read -r -d    FILE CONTENT  lt  lt   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   gt  sqlcmd    Run the command   MODEL  sudo -u postgres psql  some db  -c    cat sqlcmd    gt  gt sqlop 2 gt  amp 1  EXAMPLE  sudo -u postgres psql some db -c    cat sqlcmd    gt  gt sqlop 2 gt  amp 1   View track your command output   cat sqlop  Done  Thanks   D

User · Answer

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

User · Answer

If you got the following error ufgtoolspg  gt  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  quot COPY  SELECT foo  bar FROM baz  TO STDOUT  format csv  delimiter      quot    gt  baz csv

User · Answer

Approach for docker  via psql command   docker exec -i  containerid  psql -U  user  -c   dt   gt  tables txt   or query from sql file  docker exec -i  containerid  psql -U  user   lt  file sql  gt  data txt

User · Answer

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

User · Answer

COPY tablename TO   tmp output csv  DELIMITER     CSV HEADER   this command is used to store the entire table as csv

[postgresql] psql - save results of command to a file

Examples related to postgresql

Examples related to psql