CODEWITHSUNDEEP
bashpostgresqlpsql
johnlemon
johnlemon

Reputation: 21449

How to get psql result in bash?

I'm trying to get the psql result in bash

echo 'SELECT * FROM fictive_table LIMIT 1;' >> /tmp/x.sql
psql --single-transaction -d dbname -f /tmp/x.sql
echo $?

This will fail but the result I get in bash is 0. Is there a problem with the logic/code?

From the manual

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own (out of memory, file not found) occurs

Update: @Andrea Spadaccini is right. This is not fatal. How can I catch it ?

Upvotes: 3

Views: 3378

Answers (3)

mmccarn
mmccarn

Reputation: 499

[I know... this is old...]

I discovered that this works for my use case for running psql commands from bash:

$ echo "command-to-run" |(sudo -u postgres psql -t -d <dbname> -f -)

I'm using psql on my nextcloud server - here's a sample command:

$ echo "select uid from oc_users where uid like '%thd%';" |(sudo -u postgres psql -t -d nextcloud -f -)
 [email protected]
 [email protected]
 [email protected]
 [email protected]

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Use the option -o to write results of a query from psql (the command line terminal) to a file.
Per documentation:

-o filename
--output=filename

Put all query output into file filename. This is equivalent to the command \o.

Upvotes: 0

Andrea Spadaccini
Andrea Spadaccini

Reputation: 12651

Maybe the kind of error triggered by the query is not a fatal one like the ones mentioned in the manual, i.e., you don't run out of memory and it does not get a file not found error.

Reading a bit further in the manual, I read that:

psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own (out of memory, file not found) occurs, 2 if the connection to the server went bad and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set.

To catch your error you can try to:

  • set the ON_ERROR_STOP variable; or,
  • change approach, saving the query output to a file and then working with that file.

Upvotes: 3

Related Questions