What is pgclimb
If you need to create a report based on a database query in PostgreSQL, most people use PgAdmin3, PgAdmin4 or psql command. In order to create a CSV file in psql command, you may use spool command "\o [filename]". We have more better way to produce CSV file.pgclimb tool allows you to produce CSV or EXCEL file without using any spool command. It also supports various data formats such as JSON, XML. This is easy to use.
If you run a database query in psql command, you get the following result.
=> SELECT * FROM pg_language ; lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl ----------+---------+--------------+---------------+--------------+-------- internal | f | f | 0 | 2246 | c | f | f | 0 | 2247 | sql | f | t | 0 | 2248 | plpgsql | t | t | 148331 | 148332 | plperl | t | t | 1461356 | 1461357 | (5 rows)
In order to create a CSV file, you need to use "\o [filename]" and run query then "\o" to finalize every time. This is a bit pain, but pgclimb tool helps you.
=> \pset format unaligned Output format is unaligned. => \pset fieldsep ',' Field separator is ",". => \o result.csv => SELECT * FROM pg_language ; => \o
Install pgclimb tool
Let's download pgclimb tool into your Linux computer. In this case, I use Linux x86_64 OS (64 bits OS).$ wget -O pgclimb https://github.com/lukasmartinelli/pgclimb/releases/download/v0.2/pgclimb_linux_amd64 $ chmod +x pgclimb $ ./pgclimb --help
Run pgclimb command
Once you installed the pgclimb command, let's produce CSV and jsonlines formatted data.Here is how to produce CSV formatted data by using pgclimb.
$ ./pgclimb --host foobar -p 5432 -U pikachu -c "SELECT * FROM pg_language" csv internal,0,2246 c,0,2247 sql,0,2248
Below is how to produce jsonlines formatted data by using pgclimb.
$ ./pgclimb --host foobar -p 5432 -U pikachu -c "SELECT * FROM pg_language" jsonlines
{"lanacl":null,"lanispl":false,"lanname":"internal","lanplcallfoid":0,"lanpltrusted":false,"lanvalidator":2246}
{"lanacl":null,"lanispl":false,"lanname":"c","lanplcallfoid":0,"lanpltrusted":false,"lanvalidator":2247}
{"lanacl":null,"lanispl":false,"lanname":"sql","lanplcallfoid":0,"lanpltrusted":true,"lanvalidator":2248}
The above two examples displayed SQL result on screen, but you can write the formatted data into a file. Below is how to produce EXCEL file.
$ ./pgclimb --host foobar -p 5432 -U pikachu -c "SELECT * FROM pg_language" --output test.xlsx xlsx --sheet "pg_language_result"
As a result of that, "test.xlsx" file is created, and EXCEL sheet name will be named as "pg_language_result".
