Run a SQL statement from the shell

A script sends two statements in one psql -c and, when the second fails, the first has silently not happened.

psql -c "CREATE TABLE t1 (id int); SELECT 1/0"
psql -c '\dt t1'
echo ---
psql -c "CREATE TABLE t2 (id int)" -c "SELECT 1/0"
psql -c '\dt t2'
psql -c 'DROP TABLE t2'

CREATE TABLE
ERROR:  division by zero
Did not find any relation named "t1".
---
CREATE TABLE
ERROR:  division by zero
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t2   | table | postgres
(1 row)

DROP TABLE

A -c string is sent to the server as one simple query message, and the server runs a message with several statements as one implicit transaction, so the error in SELECT 1/0 rolled back the CREATE TABLE before it, even though psql printed the CREATE TABLE tag first and the check afterwards finds no table. With a separate -c for each statement, psql sends them one at a time and the table is created before the error stops the run. This is described under the -c option in the psql reference. A -c string must be a complete SQL command or a single backslash command, and the two cannot be mixed in it.

Repeated -c options need PostgreSQL 9.6 or later. Before psql 15, a string with several statements printed only the last result as well. For a script with many statements, use -f or a here-document on standard input, add -v ON_ERROR_STOP=1 and wrap it in --single-transaction when it must be all or nothing. Use -X to skip the user's ~/.psqlrc.