how to write postgresql script to export data from table to csv file

Question

i Just want to write a script which finds the tables in a particular postgresql data and converts/exports whole data to individual csv files

help me in starting with sample scripts in postgresql

Answer 1

You can get tables by querying information_schema.tables view:

dwh=> \d information_schema.tables 
                       View "information_schema.tables"
            Column            |               Type                | Modifiers 
------------------------------+-----------------------------------+-----------
 table_catalog                | information_schema.sql_identifier | 
 table_schema                 | information_schema.sql_identifier | 
 table_name                   | information_schema.sql_identifier | 
 table_type                   | information_schema.character_data | 
 self_referencing_column_name | information_schema.sql_identifier | 
 reference_generation         | information_schema.character_data | 
 user_defined_type_catalog    | information_schema.sql_identifier | 
 user_defined_type_schema     | information_schema.sql_identifier | 
 user_defined_type_name       | information_schema.sql_identifier | 
 is_insertable_into           | information_schema.character_data | 
 is_typed                     | information_schema.character_data | 
 commit_action                | information_schema.character_data | 

and there is a similar view for columns: information_schema.columns. Moreover psql has option -E which shows hidden queries i.e. queries issued by psql commands like '\d', ...

Postgres has COPY command (http://www.postgresql.org/docs/8.4/interactive/sql-copy.html) but you have to be database superuser (postgres) to use it with files (you can use COPY ... TO STDOUT HEADER CSV).

quick & dirty shell script:

psql ... -A -t -U dwh -c "select '\\\copy ' || table_name || ' to ''' || table_name || '.csv'' csv header' from information_schema.tables" | psql ...

You have to replace '...' by your connection parameters