Monday, May 1, 2023

Using psql from command line to get data from postgresql

Sometimes we need to get some data from postgresql database, and we want the output to appear on the terminal so that we can further process the output.

Lets say, we want to get a list of actor from a database called dvdrental, we can simply use below command:
$ psql -U postgres -d dvdrental -c "select * from actor;"

The output will be in an interactive mode if the output is very long.

In order to run psql with output that is not interactive, we can use here-document method. The method comprise of a "<<" symbol followed by some text used as ending text for the here-document. For example, in order to get the same result as above using here-document method, we can use below command:
$ psql -U postgres -d dvdrental <<END
select * from actor;

The END keyword is a signal to end the here-document, thus executing the command. The output will be like below, which is not interactive, but easy to copy and paste:

No comments: