Pipe multiple SQL commands to psql CLI

Question

I have a set of linux applications that are being composed together via pipes

some_application | awk '{print "INSERT INTO my_table VALUES (\x27" $2 "\x27," $3 ")"'

The output of which will be a series of SQL INSERT commands:

INSERT INTO my_table VALUES ('foo',42)
INSERT INTO my_table VALUES ('bar',43)

How can these multiple commands be piped into psql?

There is a similar question which answers how to execute a single command using the -c parameter for psql. But that answer is not ideal for my scenario because it requires establishing a new connection for each command; I would prefer to setup a single connection and then pipe all commands through that one session.

Thank you in advance for your consideration and response.

Answer 1

You can pipe it into psql directly, but you need semicolon terminators for each line.

some_application | awk '{print "INSERT INTO my_table (\'" $2 "\'," $3 ");"' | psql <your connection switches>

You can test your connection parms by doing something innocuous like so:

echo "select count(*) from pg_tables;" | psql <your parms>

You will see the result of the query if the connection information is correct.