# 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
{"id":"37fa67a4-0c4c-4cfe-9afe-0c32046b6658","key":"fhir.worker.heartbeat","facility_id":null}
{"id":"30410735-12f3-4733-a39f-dce7ea1b3635","key":"fhir.worker.assumeDroppedAfter","facility_id":null}
(2 rows, took 57.717ms)
-- Expanded JSON output pretty-prints an array of objects
database=> select id, created_at, key, value from settings where key like 'fhir.%' \gjx
[
{
"id": "37fa67a4-0c4c-4cfe-9afe-0c32046b6658",
"created_at": "2025-09-16T06:33:09.865Z",
"key": "fhir.worker.heartbeat",
"value": "1 minute"
},
{
"id": "30410735-12f3-4733-a39f-dce7ea1b3635",
"created_at": "2025-09-16T06:33:09.865Z",
"key": "fhir.worker.assumeDroppedAfter",
"value": "10 minutes"
}
]
(2 rows, took 56.093ms)
-- Zero output (useful with \re)
database=> select * from patients \gz
(49 rows, took 56.927ms)
-- 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');
(inserted 1 row, took 2.345 ms)
database=*>
-- Along with being bold blue, in an active transaction the prompt will have a *
database=*> commit;
(committed, took 1.234 ms)
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 when you're in an active transaction to avoid losing work
-- you should either continue your work, or COMMIT/ROLLBACK to allow the action
-- Similarly, exiting will also be refused while in an active transaction.
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 you have the snippet invocation, but in the audit log
-- the actual contents of the snippet will also be saved, so you can't
-- "smuggle" a query past the log, but your history isn't polluted either.
```
You can use `\snip edit <name>` to load the contents of a snippet in the prompt,
as if you had typed it in. You can then edit and run the query as normal, and
subsequently you can save the edit as usual.
## Results
Have you ever done a query and then immediately after that, run it again
in a different display mode because the original was unreadable? With the
`\re` commands, you can re-print any of the last queries' results without
have to re-run them, in the same or a different format, and even apply
simple transformations.
```sql
-- Let's get a query's results in the buffer
database=> SELECT * FROM usersselect * from users;
id ┆ created ┆ updated ┆ deleted ┆ email ┆ passwo ┆ displa ┆ role ┆ displa ┆ visibi ┆ phone_ ┆ update ┆ device
┆ _at ┆ _at ┆ _at ┆ ┆ rd ┆ y_name ┆ ┆ y_id ┆ lity_s ┆ number ┆ d_at_s ┆ _regis
┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ tatus ┆ ┆ ync_ti ┆ tratio
┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ck ┆ n_quot
┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ a
═════════╪═════════╪═════════╪═════════╪═════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════
0000000 ┆ 2025-09 ┆ 2025-09 ┆ NULL ┆ system ┆ NULL ┆ System ┆ system ┆ NULL ┆ curren ┆ NULL ┆ 0 ┆ 0
0-0000- ┆ -16T06: ┆ -16T06: ┆ ┆ ┆ ┆ ┆ ┆ ┆ t ┆ ┆ ┆
0000-00 ┆ 33:12.9 ┆ 33:12.9 ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆
00-0000 ┆ 13596Z ┆ 13596Z ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆
0000000 ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆
0 ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆ ┆
╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌
88085a0 ┆ 2025-09 ┆ 2025-09 ┆ NULL ┆ admin@t ┆ $2b$12 ┆ Initia ┆ admin ┆ NULL ┆ curren ┆ NULL ┆ 3 ┆ 0
2-6f4d- ┆ -16T06: ┆ -16T06: ┆ ┆ amanu.i ┆ $JRZEj ┆ l ┆ ┆ ┆ t ┆ ┆ ┆
43ae-ae ┆ 36:33.3 ┆ 36:33.3 ┆ ┆ o ┆ eHQcqq ┆ Admin ┆ ┆ ┆ ┆ ┆ ┆
cb-6cd8 ┆ Z ┆ Z ┆ ┆ ┆ qg9bO0 ┆ ┆ ┆ ┆ ┆ ┆ ┆
fd603f1 ┆ ┆ ┆ ┆ ┆ xucR.z ┆ ┆ ┆ ┆ ┆ ┆ ┆
c ┆ ┆ ┆ ┆ ┆ xQZKOM ┆ ┆ ┆ ┆ ┆ ┆ ┆
┆ ┆ ┆ ┆ ┆ QntRrp ┆ ┆ ┆ ┆ ┆ ┆ ┆
┆ ┆ ┆ ┆ ┆ cFvMMy ┆ ┆ ┆ ┆ ┆ ┆ ┆
┆ ┆ ┆ ┆ ┆ l/NetX ┆ ┆ ┆ ┆ ┆ ┆ ┆
┆ ┆ ┆ ┆ ┆ 6K83EO ┆ ┆ ┆ ┆ ┆ ┆ ┆
╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌
(4 rows, took 84.806ms)
-- (not all rows shown)
-- That's pretty cramped. I should have used \gx...
-- List out the available results
database=> \re list
Past query results (1 of 1):
N ┆ When ┆ Took ┆ Size ┆ Rows ┆ Cols
═══╪═════════════════════╪══════════╪═════════╪══════╪══════
0 ┆ 2025-10-30 10:45:52 ┆ 84.81 ms ┆ 1.68 KB ┆ 4 ┆ 13
Total memory used: 1.68 KB
Memory limit: 1.00 GB
-- Now lets print the users again, but in JSON format:
database=> \re show format=json
{"created_at":"2025-09-16T06:33:12.913596Z","deleted_at":null,"device_registration_quota":0,"display_id":null,"display_name":"System","email":"system","id":"00000000-0000-0000-0000-000000000000","password":null,"phone_number":null,"role":"system","updated_at":"2025-09-16T06:33:12.913596Z","updated_at_sync_tick":0,"visibility_status":"current"}
{"created_at":"2025-09-16T06:36:33.3Z","deleted_at":null,"device_registration_quota":0,"display_id":null,"display_name":"Initial Admin","email":"admin@tamanu.io","id":"88085a02-6f4d-43ae-aecb-6cd8fd603f1c","password":"$2b$12$JRZEjeHQcqqqg9bO0xucR.zxQZKOMQntRrpcFvMMyl/NetX6K83EO","phone_number":null,"role":"admin","updated_at":"2025-09-16T06:36:33.3Z","updated_at_sync_tick":3,"visibility_status":"current"}
{"created_at":"2025-09-16T06:36:33.495Z","deleted_at":null,"device_registration_quota":0,"display_id":null,"display_name":"System: facility-a sync","email":"facility-a@tamanu.io","id":"4345bd44-d6e4-48fd-85c3-c006bbc786e3","password":"$2b$12$ehgYV1nSi.1UePwtS7T5pObCcjtP35rL/MVDnc/u9YwmjU2oFNGwG","phone_number":null,"role":"admin","updated_at":"2025-09-16T06:36:33.495Z","updated_at_sync_tick":3,"visibility_status":"current"}
{"created_at":"2025-11-01T11:45:21.392383Z","deleted_at":null,"device_registration_quota":0,"display_id":null,"display_name":"Admin","email":"admin@bes.au","id":"4bbfb6fd-a1e6-4093-9afd-acbeca759e09","password":null,"phone_number":null,"role":"admin","updated_at":"2025-11-01T01:45:21.392383Z","updated_at_sync_tick":31,"visibility_status":"current"}
-- Let's page through the results one at a time, in expanded format
database=> \re show format=expanded limit=1 offset=0
-[ RECORD 1 ]-
id ┆ 00000000-0000-0000-0000-000000000000
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
created_at ┆ 2025-09-16T06:33:12.913596Z
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
updated_at ┆ 2025-09-16T06:33:12.913596Z
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
deleted_at ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
email ┆ system
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
password ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
display_name ┆ System
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
role ┆ system
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
display_id ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
visibility_status ┆ current
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
phone_number ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
updated_at_sync_tick ┆ 0
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
device_registration_quota ┆ 0
database=> \re show format=expanded limit=1 offset=1
-[ RECORD 1 ]-
id ┆ 88085a02-6f4d-43ae-aecb-6cd8fd603f1c
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
created_at ┆ 2025-09-16T06:36:33.3Z
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
updated_at ┆ 2025-09-16T06:36:33.3Z
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
deleted_at ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
email ┆ admin@tamanu.io
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
password ┆ $2b$12$JRZEjeHQcqqqg9bO0xucR.zxQZKOMQntRrpcFvMMyl/NetX6K83EO
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
display_name ┆ Initial Admin
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
role ┆ admin
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
display_id ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
visibility_status ┆ current
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
phone_number ┆ NULL
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
updated_at_sync_tick ┆ 3
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
device_registration_quota ┆ 0
-- Lets only show the columns we're interested in
database=> \re show cols=id,email,role,display_name
id ┆ email ┆ role ┆ display_name
══════════════════════════════════════╪══════════════════════╪════════╪═════════════════════════
00000000-0000-0000-0000-000000000000 ┆ system ┆ system ┆ System
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
88085a02-6f4d-43ae-aecb-6cd8fd603f1c ┆ admin@tamanu.io ┆ admin ┆ Initial Admin
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
4345bd44-d6e4-48fd-85c3-c006bbc786e3 ┆ facility-a@tamanu.io ┆ admin ┆ System: facility-a sync
╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
4bbfb6fd-a1e6-4093-9afd-acbeca759e09 ┆ admin@bes.au ┆ admin ┆ Admin
-- Let's make another query (and for demo's sake, hide its output)
database=> select * from patients \gz
(49 rows, took 56.354ms)
database=> \re list
Past query results (2 of 2):
N ┆ When ┆ Took ┆ Size ┆ Rows ┆ Cols
═══╪═════════════════════╪══════════╪══════════╪══════╪══════
0 ┆ 2025-10-30 10:45:52 ┆ 84.81 ms ┆ 1.68 KB ┆ 4 ┆ 13
╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌┼╌╌╌╌╌╌
1 ┆ 2025-10-30 10:58:39 ┆ 66.08 ms ┆ 34.59 KB ┆ 49 ┆ 20
Total memory used: 36.27 KB
Memory limit: 1.00 GB
-- But how do we tell which query was which? With +
database=> \re list+
Past query results (2 of 2):
N ┆ When ┆ Took ┆ Size ┆ Rows ┆ Columns ┆ Query
═══╪═════════════════════╪══════════╪══════════╪══════╪═══════════════════════════════════════╪══════════════════════════
0 ┆ 2025-10-30 10:45:52 ┆ 84.81 ms ┆ 1.68 KB ┆ 4 ┆ id, created_at, updated_at, ┆ select * from users
┆ ┆ ┆ ┆ ┆ deleted_at, email, password, ┆
┆ ┆ ┆ ┆ ┆ display_name, role, display_id, ┆
┆ ┆ ┆ ┆ ┆ visibility_status, phone_number, ┆
┆ ┆ ┆ ┆ ┆ updated_at_sync_tick, ┆
┆ ┆ ┆ ┆ ┆ device_registration_quota ┆
╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌
1 ┆ 2025-10-30 10:58:39 ┆ 66.08 ms ┆ 34.59 KB ┆ 49 ┆ id, created_at, updated_at, ┆ select * from patients
┆ ┆ ┆ ┆ ┆ deleted_at, display_id, first_name, ┆
┆ ┆ ┆ ┆ ┆ middle_name, last_name, ┆
┆ ┆ ┆ ┆ ┆ cultural_name, email, date_of_birth, ┆
┆ ┆ ┆ ┆ ┆ sex, village_id, additional_details, ┆
┆ ┆ ┆ ┆ ┆ date_of_death, merged_into_id, ┆
┆ ┆ ┆ ┆ ┆ visibility_status, ┆
┆ ┆ ┆ ┆ ┆ date_of_birth_legacy, ┆
┆ ┆ ┆ ┆ ┆ date_of_death_legacy, ┆
┆ ┆ ┆ ┆ ┆ updated_at_sync_tick ┆
Total memory used: 36.27 KB
Memory limit: 1.00 GB
-- Now we can reformat the output of that second query:
database=> \re show limit=1 cols=id,last_name,first_name
id ┆ last_name ┆ first_name
══════════════════════════════════════╪═══════════╪════════════
3aa63a3c-bb1e-4f3c-a185-ad18b5218256 ┆ Milani ┆ Esther
-- And if we want, we can still re-display the users...
database=> \re show n=0 format=csv cols=id,created_at
id,created_at
00000000-0000-0000-0000-000000000000,2025-09-16T06:33:12.913596Z
88085a02-6f4d-43ae-aecb-6cd8fd603f1c,2025-09-16T06:36:33.3Z
4345bd44-d6e4-48fd-85c3-c006bbc786e3,2025-09-16T06:36:33.495Z
4bbfb6fd-a1e6-4093-9afd-acbeca759e09,2025-11-01T11:45:21.392383Z
```
### File-only formats
With `\re show` you can also export results as file-only formats.
These will error if you try using them without specifying `to=`.
```sql
-- Write an Excel spreadsheet (in XLSX format)
database=> \re show n=0 format=excel to=data.xlsx
Output written to /home/myself/data.xlsx
-- Write a SQLite database (data is in the table `results`)
database=> \re show n=0 format=sqlite to=/data/export.db
Output written to /data/export.db
```
## 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
-- Set a variable with a default value (only if not already set)
database=> \default my_default Goodbye
-- 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 use \gzset if you want to hide the output
-- 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$
```