psql is PostgreSQL’s universal command-line client and a precise tool for learning, administration, scripts, and incident response. A safe workflow proves the target, inspects before changing, wraps experiments in transactions, stops scripts on errors, records evidence, and separates psql meta-commands from SQL understood by the server.
The SignalDesk foundation schema and seed row must exist in signaldesk_learning.
psql has two languages:
The prompt communicates transaction state. A prompt ending in =* indicates an open transaction; =! indicates an aborted one. Read it before typing the next command.
Use \d+ to discover what PostgreSQL actually created instead of relying on memory.
Inspect, change in a transaction, verify, then choose rollback or commit:
The transaction reduces risk but is not magic: some commands have external effects, long transactions retain old row versions, and an accidental COMMIT is final. Always inspect the target predicate first.
Use savepoints to recover within a larger transaction:
Create migration or lab files in version control and execute them with error stopping:
-X avoids user startup-file surprises. ON_ERROR_STOP prevents later statements from running after a failure. In CI, also record the client and server versions.
For identifiers or values supplied externally, prefer application parameters or carefully reviewed psql variable quoting:
psql substitution is not the same as server-side bind parameters and must not become an internet-facing query API.
Inspect the file for secrets before sharing or committing it. Query output can contain personal or tenant data even when SQL text does not.
Start a transaction, change one known ticket with RETURNING, verify the new value, roll back, and prove the original value returned. Save the before/change/after evidence with connection identity.
Then run a script containing one valid statement, one invalid statement, and another valid statement using ON_ERROR_STOP. Prove the third statement did not execute. Use only disposable objects in the learning schema.
An AI assistant can help draft a diagnostic query, but the human workflow must supply target identity, least privilege, timeouts, result limits, transaction boundaries, and independent verification. Captured evidence also becomes a better incident and evaluation artifact than a chat transcript that claims a command succeeded.
Useful session guards for reviewed maintenance:
Confirm these values fit the task; indiscriminate timeouts can interrupt necessary operations.
Write a psql runbook that changes the priority of exactly one ticket and aborts if the intended ticket does not exist. It must show target identity, before state, changed state, and after state, then roll back.
Acceptance criterion: the runbook exits on errors, affects one row during the transaction, leaves zero durable changes, and produces non-sensitive evidence.