Saving The Output From psql

 
Occasionally, you will need to capture data when working with a database. You can cut-n-paste or use a shell command like script. In typical PostgreSQL fashion, psql has two ways to save your output.

The First Way

You must invoke psql with the --log-file-<filename> or -L <filename> option. The entire session will be recorded.

stoker@ThinkPad:~$ psql --log-file=/tmp/psqllog demo
Password for user stoker:
psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2))
Type "help" for help.
demo=# \d ledger
                   Table "public.ledger"
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 id             | integer |           |          |
 tx_id          | integer |           |          |
 total_register | money   |           |          |
 misc           | integer |           |          |
 status         | text    |           |          |
demo=# \q
stoker@ThinkPad:~$ cat /tmp/psqllog
                   Table "public.ledger"
     Column     |  Type   | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
 id             | integer |           |          |
 tx_id          | integer |           |          |
 total_register | money   |           |          |

The Second Way

The other choice is to use -o <file> or --output=<filename> when you start psql. The differences are that the SQL command is not recorded

and the output does not show on your screen.

stoker@ThinkPad:~$ psql -o /tmp/out demo
Password for user stoker:
psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2))
Type "help" for help.
demo=# \dt         # note the lack of output compared to the above
demo=# \q
stoker@ThinkPad:~$ cat /tmp/out
          List of relations
 Schema |   Name    | Type  | Owner
--------+-----------+-------+--------
 public | customers | table | stoker
 public | ex01      | table | stoker
 public | ex02      | table | stoker
 public | ex03      | table | stoker
 public | foo       | table | stoker
 public | ledger    | table | stoker
 public | register  | table | stoker
 public | two       | table | stoker
 public | v         | table | stoker
 public | z         | table | stoker
 public | zz        | table | stoker
(11 rows)

Summary

These two little options can save you a lot of grief when you need to save the output from psql.  You will find them very handy every so often.

Comments

Post a Comment

Popular posts from this blog

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more

How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...
Read more

PostgreSQL's COPY and \COPY

PostgreSQL is equivalent to a Swiss Army Knife in the database world. There are things in PostgreSQL that are very simple to use, while in another database, they take many more steps to accomplish. But sometimes, the knife has too many blades, which can cause confusion. This is one of those cases. COPY and \COPY I needed a quick dump of a table as part of an ETL (Extract, Transform, and Load - official definition, Exorcism, Trauma, and Lost-sleep - unofficially) to port some data. The COPY command is a quick way to output and write the data to a file.  The two copy commands below illustrate adding a delimiter and then writing the output to a table. demo=# select * from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=# copy z to stdout (DELIMITER ','); 1,10,100 2,20,200 3,30,300 4,40,400 demo=# copy z to '/tmp/z' (DELIMITER ','); COPY 4 demo=#  The trick is that  you must...
Read more