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.

Take a look at example Here under -c command to see if that will work. — Adrian Klaver
– Adrian Klaver, Commented Jul 29, 2020 at 18:34

Mike Organek · Accepted Answer · 2020-07-29 18:41:24Z

8

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.

edited Jul 29, 2020 at 18:41

answered Jul 29, 2020 at 18:36

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pipe multiple SQL commands to psql CLI

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related