Re: copy/dump database to text/csv files

William Nolf wrote on 24.07.2014 21:04:
> This is probably an easy one for most sql users but I don't use it
> very often.
>
> We have a postgres database that was used for an application we no
> longer use.  However, we would
>
> like to copy/dump the tables to files, text or csv so we can post
> them to sharepoint.
>
> Copy seems to be what I want.  But it only does one table at a time.
> There is 204 tables with a schemaname=public.  I need to be copy the
> tables with data to files.   I need something like a for
>
> loop which checks if the table is populated if so, copy it to
> tablename.csv file
>
> Any ideas?

If you are not constrained to psql you might want to have a look at my tool SQL Workbench/J
(http://www.sql-workbench.net/)which contains an export command to do just that: export all tables of a schema (or
multipleschemas) in a defined format,  see here: http://www.sql-workbench.net/manual/command-export.html 

Using that, this would be as easy as:

WbExport -outputDir=/some/export/directory
          -type=text
          -header=true
          -delimiter=','
          -sourceTable=public.*;

You also have more control over the format of the exported data than you would have with psql I dare say.

Disclosure: I am the author of that tool.

Regards
Thomas