# Examples
## Output
```sql
-- Normal output
database=> select * from settings where key like 'fhir.%';
id ┆ created_at ┆ updated_at ┆ deleted_at ┆ key ┆ value ┆ facility_id ┆ scope ┆ updated_at_
┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ sync_tick
══════════════╪══════════════╪═════════════╪════════════╪═════════════╪═════════════╪═════════════╪════════╪═════════════
37fa67a4-0c4 ┆ 2025-09-16T0 ┆ 2025-09-16T ┆ NULL ┆ fhir.worker ┆ "1 minute" ┆ NULL ┆ global ┆ 0
c-4cfe-9afe- ┆ 6:33:09.865Z ┆ 06:33:09.86 ┆ ┆ .heartbeat ┆ ┆ ┆ ┆
0c32046b6658 ┆ ┆ 5Z ┆ ┆ ┆ ┆ ┆ ┆
╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌
30410735-12f ┆ 2025-09-16T0 ┆ 2025-09-16T ┆ NULL ┆ fhir.worker ┆ "10 ┆ NULL ┆ global ┆ 0
3-4733-a39f- ┆ 6:33:09.865Z ┆ 06:33:09.86 ┆ ┆ .assumeDrop ┆ minutes" ┆ ┆ ┆
dce7ea1b3635 ┆ ┆ 5Z ┆ ┆ pedAfter ┆ ┆ ┆ ┆
(2 rows, took 56.381ms)
-- all queries will print how long they took and a total count
-- \g alone is equivalent to ;
database=> select * from settings where key like 'fhir.%' \g
-- (output same as above)
-- Expanded output
database=> select * from settings where key like 'fhir.%' limit 1 \gx
-[ RECORD 1 ]-
id ┆ 37fa67a4-0c4c-4cfe-9afe-0c32046b6658
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
created_at ┆ 2025-09-16T06:33:09.865Z
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
updated_at ┆ 2025-09-16T06:33:09.865Z
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
deleted_at ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
key ┆ fhir.worker.heartbeat
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
value ┆ "1 minute"
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
facility_id ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
scope ┆ global
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
updated_at_sync_tick ┆ 0
(1 row, took 57.375ms)
-- compared to native psql, you will notice that nulls are explicit here
-- JSON output prints one object per row
database=> select id, key, facility_id from settings where key like 'fhir.%' \gj
{"facility_id":null,"id":"37fa67a4-0c4c-4cfe-9afe-0c32046b6658","key":"fhir.worker.heartbeat"}
{"facility_id":null,"id":"30410735-12f3-4733-a39f-dce7ea1b3635","key":"fhir.worker.assumeDroppedAfter"}
(2 rows, took 57.717ms)
-- the object key order is arbitrary and cannot be relied upon
-- Expanded JSON output pretty-prints an array of objects
database=> select id, created_at, key, value from settings where key like 'fhir.%' \gjx
[
{
"created_at": "2025-09-16T06:33:09.865Z",
"id": "37fa67a4-0c4c-4cfe-9afe-0c32046b6658",
"key": "fhir.worker.heartbeat",
"value": "1 minute"
},
{
"created_at": "2025-09-16T06:33:09.865Z",
"id": "30410735-12f3-4733-a39f-dce7ea1b3635",
"key": "fhir.worker.assumeDroppedAfter",
"value": "10 minutes"
}
]
(2 rows, took 56.093ms)
-- Output to file in one-object per line JSON format
database=> select id, created_at, key, value from settings where key like 'fhir.%' \gjo file.json
(2 rows, took 56.619ms)
-- note how this still prints the count and time taken here, and not in your file
```
## Write Mode
```sql
-- By default, the session is in read-only mode. SELECTs will work:
database=> select id, display_name, role from users limit 1;
id ┆ display_name ┆ role
══════════════════════════════════════╪══════════════╪════════
00000000-0000-0000-0000-000000000000 ┆ System ┆ system
(1 row, took 56.338ms)
-- But INSERTS and other write operations will be refused:
database=> insert into users (email, role) values ('admin@bes.au', 'admin');
2025-11-01T01:35:43.708916Z ERROR bestool_psql::repl::execute: db error
╰─▶ ERROR: cannot execute INSERT in a read-only transaction
-- You instead need to switch into write mode, either
-- by providing --write/-W on the CLI, or interactively:
database=> \W
OTS?
-- At this point, it will prompt you for an "OTS", and refuse to proceed without.
-- You can hit "up arrow" to go through the OTS history, or type your own.
-- This OTS ("Over The Shoulder") will be recorded in the audit log for every
-- query done in this write mode session; it's meant to record who is supervising
-- you as you're working in a database potentially destructively.
-- Let's provide "demo" for now:
database=> \W
OTS? demo
AUTOCOMMIT IS OFF -- REMEMBER TO `COMMIT;` YOUR WRITES
database=>
-- In the actual output, the prompt will now be bold green. Legend:
-- normal white -- read-only mode
-- bold green -- write mode, idle transaction (initially or after committing)
-- bold blue -- write mode, active transaction (queries have been executed)
-- bold red -- write mode, failed transaction (you should issue a ROLLBACK)
database=> insert into users (email, role, display_name) values ('admin@bes.au', 'admin', 'Admin');
(no rows)
database=*>
-- Along with being bold blue, in an active transaction the prompt will have a *
database=*> commit;
(no rows)
database=>
-- COMMIT or ROLLBACK to return to a bold green idle state
-- note that a new idle transaction has been automatically opened
-- Once you're done writing, return to read-only mode
-- This will refuse to work when you're in an active transaction
-- to avoid losing work -- you should either continue, or COMMIT/ROLLBACK
database=> \W
SESSION IS NOW READ ONLY
```
## Snippets
```sql
-- Run a query
database=> select id, created_at, key, value from settings where key like 'fhir.%';
-- (output omitted)
-- Save the last query (so the one above here) to a snippet
database=> \snip save fhir_settings
Snippet saved to /home/.local/share/snippets/fhir_settings.sql
-- snippets are saved as SQL files but contain the exact query run,
-- so if you use query modifiers, they will be included too
-- Run a snippet
database=> \run fhir_settings
(2 rows, took 52.163ms)
-- In your history (and in the audit log) you will now have two things:
-- 1. The snippet invocation
-- 2. The actual snippet contents
-- This means you can hit "up arrow" to edit the snippet contents,
-- and it also prohibits "smuggling" queries past the audit log.
```
## Variables
Variables have a different syntax and command set than in native psql.
Also unlike native psql, there are no variables that affect the tool itself,
nor variables that are set by the tool: it's all your content.
```sql
-- Set a variable manually
database=> \set my_var Hello
-- Print a variable
database=> \get my_var
Hello
-- List variables
database=> \vars
Name ┆ Value
════════╪═══════
my_var ┆ Hello
-- Variables are set literally, with the variable name being a single word
database=> \set my_var Hello, World
database=> \set my_var2 'Hello, World'
database=> \vars
Name ┆ Value
═════════╪═════════════════
my_var ┆ Hello, World!
╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
my_var2 ┆ 'Hello, World!'
-- Use variables in queries using ${name}
database=> select '${my_var}' as message;
message
═══════════════
Hello, World!
(1 row, took 56.483ms)
-- Note again how variables are literal, and substitution also is literal
-- There's no type conversion or anything. Remember what my_var2 is set to?
database=> select ${my_var2} as message;
message
═══════════════
Hello, World!
(1 row, took 56.483ms)
database=> select 123 as "${my_var}";
Hello, World!
═══════════════
123
(1 row, took 56.032ms)
-- You can even have variables that contain arbitrary parts of queries
database=> \set count select count(*) from
database=> ${count} settings;
count
═══════
7
(1 row, took 59.619ms)
-- To use a literal ${foo} string, double the braces:
database=> select '${{foo}}';
?column?
══════════
${foo}
(1 row, took 55.336ms)
-- (using three braces outputs two, and so on)
-- Alternatively, use verbatim mode:
database=> select '${foo}' \gv
?column?
══════════
${foo}
(1 row, took 55.336ms)
-- You can use \gset to extract variables from the output of a query
database=> select id from settings limit 1 \gset
id
══════════════════════════════════════
37fa67a4-0c4c-4cfe-9afe-0c32046b6658
(1 row, took 55.586ms)
-- unlike native psql, the output is still printed to screen (or wherever)
-- You can add a prefix to the variable names generated
database=> select id, key from settings limit 1 \gjset set_
{"id":"37fa67a4-0c4c-4cfe-9afe-0c32046b6658","key":"fhir.worker.heartbeat"}
(1 row, took 55.653ms)
-- notice how you can still use JSON output for the printout
-- If a snippet uses a variable, it will be taken from the environment, or
-- you can provide a value when calling the snippet (\i works the same):
database=> \snip run my_snip my_var=123
message
═══════════════
123
(1 row, took 52.812ms)
-- And finally, you can filter the \vars output:
database=> \vars set_*
Name ┆ Value
═════════╪══════════════════════════════════════
set_id ┆ 37fa67a4-0c4c-4cfe-9afe-0c32046b6658
╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
set_key ┆ fhir.worker.heartbeat
```
## Database Exploration
```sql
-- List all tables in the default schema
database=> \list table
Schema ┆ Name ┆ Size
════════╪═════════════════════════════════════════╪═════════
public ┆ SequelizeMeta ┆ 120 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌
public ┆ administered_vaccines ┆ 48 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌
public ┆ appointment_schedules ┆ 32 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌
public ┆ appointments ┆ 48 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌
public ┆ assets ┆ 32 kB
-- etc
-- Search for tables by pattern with the alias
database=> \dt sync*
Schema ┆ Name ┆ Size
════════╪═════════════════════╪════════
public ┆ sync_device_ticks ┆ 24 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌
public ┆ sync_lookup ┆ 31 MB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌
public ┆ sync_lookup_ticks ┆ 24 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌
public ┆ sync_queued_devices ┆ 80 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌
public ┆ sync_sessions ┆ 48 kB
-- Search within other schemas and show more detail
database=> \dt+ pg_catalog.pg_ts*
Schema ┆ Name ┆ Size ┆ Owner ┆ Persistence ┆ ACL
════════════╪══════════════════╪═══════╪══════════╪═════════════╪════════════════════════════
pg_catalog ┆ pg_ts_config ┆ 72 kB ┆ postgres ┆ permanent ┆ postgres=arwdDxtm/postgres
┆ ┆ ┆ ┆ ┆ =r/postgres
╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
pg_catalog ┆ pg_ts_config_map ┆ 88 kB ┆ postgres ┆ permanent ┆ postgres=arwdDxtm/postgres
┆ ┆ ┆ ┆ ┆ =r/postgres
╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
pg_catalog ┆ pg_ts_dict ┆ 80 kB ┆ postgres ┆ permanent ┆ postgres=arwdDxtm/postgres
┆ ┆ ┆ ┆ ┆ =r/postgres
╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
pg_catalog ┆ pg_ts_parser ┆ 72 kB ┆ postgres ┆ permanent ┆ postgres=arwdDxtm/postgres
┆ ┆ ┆ ┆ ┆ =r/postgres
╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
pg_catalog ┆ pg_ts_template ┆ 72 kB ┆ postgres ┆ permanent ┆ postgres=arwdDxtm/postgres
┆ ┆ ┆ ┆ ┆ =r/postgres
-- Listing views shows both regular and materialised views
database=> \list view
Schema ┆ Name ┆ Type ┆ Size
════════╪════════════════════════════════════╪═══════════════════╪═════════
public ┆ materialized_upcoming_vaccinations ┆ materialized view ┆ 16 kB
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌
public ┆ upcoming_vaccinations ┆ view ┆ 0 bytes
-- Describing a view shows the columns in the view output, and the + includes its definition
database=> \describe+ upcoming_vaccinations
View "public.upcoming_vaccinations"
Column ┆ Type ┆ Storage
══════════════════════╪════════════════════════╪══════════
patient_id ┆ character varying(255) ┆ extended
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌
scheduled_vaccine_id ┆ character varying(255) ┆ extended
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌
vaccine_category ┆ character varying(255) ┆ extended
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌
vaccine_id ┆ character varying(255) ┆ extended
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌
due_date ┆ date ┆ plain
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌
days_till_due ┆ integer ┆ plain
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌
status ┆ text ┆ extended
View definition:
WITH vaccine_settings AS (
SELECT s.value AS thresholds,
1 AS priority
FROM settings s
WHERE s.deleted_at IS NULL AND s.key = 'upcomingVaccinations.thresholds'::text
UNION
-- snip
-- Listing functions
database=> \df setting*
Schema ┆ Name ┆ Result data type ┆ Argument data types ┆ Type
════════╪═════════════╪══════════════════╪═══════════════════════════════════════════════╪══════════
public ┆ setting_get ┆ jsonb ┆ path text, facility character varying DEFAULT ┆ function
┆ ┆ ┆ NULL::character varying ┆
╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌
public ┆ setting_on ┆ boolean ┆ path text, facility character varying DEFAULT ┆ function
┆ ┆ ┆ NULL::character varying ┆
-- Using \d+ with functions shows their source (where available)
database=> \d+ setting_get
Function "public.setting_get"
sql, stable, parallel safe
Returns: jsonb
Argument name ┆ Type
═══════════════╪═══════════════════
path ┆ text
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
facility ┆ character varying
Definition:
CREATE OR REPLACE FUNCTION public.setting_get(path text, facility character varying DEFAULT NULL::character varying)
RETURNS jsonb
LANGUAGE sql
STABLE PARALLEL SAFE
AS $function$
SELECT value
FROM settings
WHERE true
AND key = path
AND deleted_at IS NULL
AND (facility_id IS NULL OR facility_id = facility)
ORDER BY facility_id DESC LIMIT 1 -- prefer facility-specific setting when both matched
$function$
```