Use the psql -c
option.
[pol@UNKNOWN ~]$ psql --version
psql (PostgreSQL) 10.1
You can run a command like:
> psql -c 'SELECT * FROM bank_account;' test > bank_details.txt
(I have a database called test
) and then:
> more bank_details.txt
which gives:
account_id | first_name | last_name | address_number | address_street1 | address_street2 | address_town | address_county | balance
------------+------------+---------------+----------------+---------------------------------+---------------------------------+---------------------------------+----------------+---------
1 | 83fbc | 613f37db7f46 | 65 | a91fd | af5aae2642a7f418c306da | 02 | Dublin | 668
2 | cbca6 | c36eabe7b07f | 28 | 295301 | 45f260962b7eed | b4bdf5a03bf6ab | Dublin | 438
3 | 62a47 | 9ca84de | 66 | c7bdbafb606d5bd605e8254 | c6d2a6104f457f2 | ce8c579526c009ee50fd6c728da363 | Dublin | 358
4 | 371e | 27ce5752d5 | 21 | ec2401c9db6 | ed7d8a1507a | 1bcef72a619e7239215a | Dublin | 730
5 | 70bb6 | a767ad3 | 6 | fe76de803945b87a01aebded | 708193a5484239284fb15cf68d5d5 | 67face4e59f38f793ab10c5 | Dublin | 785
(not real data - needless to say :-) )
Edit:
Now, if you wish to run several commands one after the other and have data being written to various files, then you have to do something like this:
Create your script file pg.sh
- chmod 755
it.
[pol@UNKNOWN pgtest]$ more pg.sh
psql -c "\o mydata.txt" -c "SELECT * FROM bank_account;" -d test
psql -c "CREATE TABLE mytest(f1 INTEGER, f2 VARCHAR (50));" -d test
psql -c "INSERT INTO mytest VALUES (34, 'First record test...');" -d test
psql -c "INSERT INTO mytest VALUES (36, 'Second record test' );" -d test
psql -c "\o another_file.txt" -c "SELECT * FROM mytest;" -d test
psql -c "\i /home/pol/pgtest/test.sql" -d test
[pol@UNKNOWN pgtest]$
Then simply
> ./pg.sh
And then you have two new files - mydata.txt and another_file.txt with your data in them! Plus you've carried out manipulations in between time!
Also, input yet another sql script - test.sql - see below.
I also created a file called test.sql
[pol@UNKNOWN pgtest]$ more test.sql
SELECT * FROM bank_account;
SELECT * FROM mytest;
And run this using
psql -f sql.sh -d test
you can redirect to the file of your choice! Or run this script from insider another as per above!
You can also peruse the PostgreSQL manual pages (which are excellent!) here:
-c command
--command=command Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order
with the -f option. When either -c or -f is specified, psql does not
read commands from standard input; instead it terminates after
processing all the -c and -f options in sequence.
command must be either a command string that is completely parsable by
the server (i.e., it contains no psql-specific features), or a single
backslash command. Thus you cannot mix SQL and psql meta-commands
within a -c option. To achieve that, you could use repeated -c options
or pipe the string into psql, for example:
psql -c '\x' -c 'SELECT * FROM foo;' or
echo '\x \ SELECT * FROM foo;' | psql (\ is the separator
meta-command.)
Each SQL command string passed to -c is sent to the server as a single
query. Because of this, the server executes it as a single transaction
even if the string contains multiple SQL commands, unless there are
explicit BEGIN/COMMIT commands included in the string to divide it
into multiple transactions. Also, psql only prints the result of the
last SQL command in the string. This is different from the behavior
when the same string is read from a file or fed to psql's standard
input, because then psql sends each SQL command separately.
Because of this behavior, putting more than one command in a single -c
string often has unexpected results. It's better to use repeated -c
commands or feed multiple commands to psql's standard input, either
using echo as illustrated above, or via a shell here-document, for
example:
psql << EOF \x SELECT * FROM foo; EOF
So, it's good for integrating into your scripts - make sure that any passwords are stored securely and not visible using ps -ef
or similar command!