Use psql Safely for Queries, Inspection and Repeatable Scripts

You will finish with a reliable routine for connecting to PostgreSQL, finding your way around an unfamiliar database, producing machine-readable output, and making batch commands report failure. The examples target psql 16.15 from PostgreSQL client package 16.15-0ubuntu0.24.04.1, installed on this machine.

Allow about 20 minutes. You need the PostgreSQL client and a database account with access to a test database. The examples are ordinary user commands. They inspect data unless marked otherwise; do not paste write queries into a production database until you have checked the target and transaction behaviour.

1. Confirm the client before connecting

Check the binary and ask it for its built-in option help:

$ psql --version
psql (PostgreSQL) 16.15
$ psql --help=commands | sed -n '1,12p'

The version output identifies the client, not the server. The installed manual says this client works best with a server of the same or an older major version. SQL execution usually remains useful against a newer server, but backslash commands are more likely to differ.

Checkpoint: if psql --version fails, install or enable the PostgreSQL client supplied by your operating system. You do not need database administrator privileges merely to run the client.

2. Connect using explicit details

Give the database name, host and user when defaults are not certain:

$ psql -d exampledb -h db.example.test -U reporting
Password for user reporting:
psql (16.15)
Type "help" for help.

exampledb=>

Replace every placeholder. Omitting -h normally selects a local Unix socket on Unix systems. The default port is normally 5432, and the default database user is your operating-system user. Once that user is chosen, it also becomes the default database name. These defaults are convenient, but they are a frequent cause of connecting to the wrong database.

For repeatable connection defaults, use PGDATABASE, PGHOST, PGPORT and PGUSER. Do not put a password on the command line, where it can leak through shell history or process inspection. Use the PostgreSQL password file when appropriate, and protect its permissions according to PostgreSQL's requirements.

Checkpoint: inspect the session before doing anything else:

SELECT current_database(), current_user, inet_server_addr(), inet_server_port();

Record the result if you are working across several environments. To leave the session, enter \q.

3. Learn the two command languages

SQL is sent to the server and normally runs when a semicolon ends the statement. A newline alone does not execute a multi-line statement:

exampledb=> SELECT current_database(),
exampledb->        current_user;
 current_database | current_user
------------------+--------------
 exampledb        | reporting
(1 row)

A line beginning with an unquoted backslash is a psql meta-command handled by the client. Start with these read-only commands:

exampledb=> \conninfo
exampledb=> \dt
exampledb=> \d+ public.example_table
exampledb=> \q

Use \conninfo to verify the connection, \dt to list tables visible in the current search path, and \d+ to inspect a relation. If a name contains capitals or unusual characters, quote the SQL identifier exactly as it was created.

4. Run one checked query from the shell

Use -c for a one-off SQL query. Quote the SQL for the shell and keep the output quiet when another command will consume it:

$ psql -X -q -d exampledb -U reporting \
    -c "SELECT count(*) AS rows FROM public.example_table;"
 rows
------
  42
(1 row)

The -X option skips both the system and user start-up files. This is useful for automation because psql otherwise reads psqlrc and ~/.psqlrc after connecting. Those files can alter formatting, variables or server settings and can make a script behave differently between accounts.

A -c value is either SQL that the server can parse or one psql meta-command. It cannot mix SQL and a meta-command in the same -c value. Use repeated -c options when that separation is clear:

$ psql -X -d exampledb \
    -c '\x' \
    -c 'SELECT * FROM public.example_table LIMIT 1;'

5. Make exported output predictable

For a shell pipeline, request CSV and remove the heading and row-count footer:

$ psql -X -q -d exampledb -U reporting \
    --csv --tuples-only \
    -c "SELECT id, email FROM public.example_table ORDER BY id;"
1,[email protected]
2,[email protected]

CSV is safer than scraping aligned tables. If a consumer needs a different delimiter, use unaligned mode with -A and set a deliberate field separator with -F. Be careful with data containing newlines, commas or quotes: let a CSV-aware tool parse the result.

To save query output, use -o /path/to/output.csv. That changes where query output is written, so verify the file and its permissions before treating it as disposable. The -L option writes a copy while retaining normal output.

6. Run a file without hiding errors

Put reviewed SQL in a file and run it with -f, which gives useful line numbers for errors:

$ psql -X -d exampledb -U reporting -f report.sql
 id | total
----+-------
  1 | 12.50
(1 row)

For a script that must stop at the first SQL error, set ON_ERROR_STOP with -v and inspect the exit status:

$ psql -X -v ON_ERROR_STOP=1 -d exampledb -f report.sql
$ status=$?
$ printf 'psql exit status: %s\n' "$status"
psql exit status: 0

Exit status 0 means normal completion. Status 1 is a fatal client error, status 2 means a lost connection in a non-interactive session, and status 3 means a script error when ON_ERROR_STOP is enabled. A non-zero status must stop the surrounding deployment or reporting job.

7. Protect deliberate changes with a transaction

When all input should succeed or none of it should apply, combine -1 with ON_ERROR_STOP:

$ psql -X -1 -v ON_ERROR_STOP=1 \
    -d exampledb -f reviewed-change.sql

-1 wraps the supplied -c and -f work in one transaction. With ON_ERROR_STOP, a failed command causes a rollback. Do not add your own BEGIN, COMMIT or ROLLBACK inside that input and expect the wrapper to retain its intended boundaries. Some commands cannot run inside a transaction at all.

There is no universal undo for a committed SQL change. Recovery is a tested reverse migration, a restore, or an application-specific repair. If the command has already committed and you did not prepare one of those paths, stop and preserve the evidence rather than guessing with another write.