Exporting a Table to YAML From PostgreSQL With Python

You sometimes need a table as text you can read, diff and commit: a reference table that belongs in the repository, a handful of rows for a bug report, a fixture captured from a real database. YAML is a good fit when the rows are few and people will read them. The export is a short script, and it fails on the first row that holds a UUID or a decimal. This post gets past those failures, decides how exact types should look in the file, and shows a one-line alternative that needs no Python. It is the reverse of loading YAML seed data into PostgreSQL, and it belongs to the cluster on using YAML with databases.

The table

create table orders (
    id     int primary key,
    ref    uuid,
    total  numeric(10,2),
    placed timestamptz,
    note   text,
    meta   jsonb
);
insert into orders values
  (1, '9f1c2f7e-0a3b-4c55-9d1e-7a7b1e2f3a4b', 19.90, '2026-10-27 08:00:00+00', 'line1
line2', '{"gift": true}'),
  (2, null, 0.10, '2026-10-27 09:30:00+00', 'no', null);

Each column is a type that Python and YAML represent differently, which is why this table makes a good test.

Fetch rows as dictionaries

psycopg returns tuples by default. A row factory returns dictionaries, which is the shape safe_dump wants:

import psycopg
from psycopg.rows import dict_row

with psycopg.connect("", row_factory=dict_row) as conn:
    rows = conn.execute("select * from orders order by id").fetchall()
print(rows[0])

{'id': 1, 'ref': UUID('9f1c2f7e-0a3b-4c55-9d1e-7a7b1e2f3a4b'), 'total': Decimal('19.90'), 'placed': datetime.datetime(2026, 10, 27, 8, 0, tzinfo=zoneinfo.ZoneInfo(key='Etc/UTC')), 'note': 'line1\nline2', 'meta': {'gift': True}}

ref arrives as a UUID, total as a Decimal, placed as a timezone-aware datetime and meta as a plain dict (psycopg parsed the jsonb). Dumping that:

import yaml
yaml.safe_dump(rows)

RepresenterError ('cannot represent an object', UUID('9f1c2f7e-0a3b-4c55-9d1e-7a7b1e2f3a4b'))

safe_dump is deliberately strict: it writes only the basic types and refuses the rest, which is the same property that makes safe_load safe. The unsafe yaml.dump would write a !!python/object tag that only Python can read back, and that is not what you want in a file other tools consume. Teach the safe dumper what to do instead.

Choose a text form for each type

Registering a representer per type is three lines each:

import datetime, decimal, uuid

yaml.SafeDumper.add_representer(decimal.Decimal, lambda d, v: d.represent_str(str(v)))
yaml.SafeDumper.add_representer(uuid.UUID, lambda d, v: d.represent_str(str(v)))
yaml.SafeDumper.add_representer(datetime.datetime, lambda d, v: d.represent_str(v.isoformat()))
print(yaml.safe_dump(rows, sort_keys=False, allow_unicode=True))

- id: 1
  ref: 9f1c2f7e-0a3b-4c55-9d1e-7a7b1e2f3a4b
  total: '19.90'
  placed: '2026-10-27T08:00:00+00:00'
  note: 'line1

    line2'
  meta:
    gift: true
- id: 2
  ref: null
  total: '0.10'
  placed: '2026-10-27T09:30:00+00:00'
  note: 'no'
  meta: null

The choices behind it:

Decimals as strings. numeric(10,2) has an exact scale. If you wrote total: 19.90 unquoted, a YAML 1.1 loader reads the float 19.9 and the trailing zero is gone, and larger values lose digits the way floats do. The quoted '19.90' survives any loader, at the cost that a consumer must parse it. For money, exactness wins. If the consumer wants a number, convert with Decimal on load.

UUIDs as strings, which YAML has no native type for. The unquoted form would work, since it is not ambiguous, but the dumper quotes by its own rules, and here it left it plain.

Timestamps as ISO strings with offsets. PyYAML can write an aware datetime natively: we checked, and it wrote 2026-10-27 08:00:00+00:00 and loaded it back as an aware datetime. A loader that does not resolve YAML timestamps hands back plain text, though, so the explicit ISO string is the form that behaves the same in every consumer. The cost is that loading the file back gives str, not datetime, so your loader has to parse it. If every consumer is PyYAML, the native form is fine.

**meta stays a mapping**, which is the best case: jsonb converts to nested YAML with no loss. A null jsonb is a YAML null.

Text with a newline became a single-quoted scalar with a blank line, which is correct and hard to read. For readability you can ask for a literal block:

def str_presenter(dumper, data):
    style = "|" if "\n" in data else None
    return dumper.represent_scalar("tag:yaml.org,2002:str", data, style=style)

yaml.SafeDumper.add_representer(str, str_presenter)

With that, the note is written as note: |- followed by the two lines indented underneath, the form people expect, and 'no' stays quoted. We checked both in a run of the representer on a two-line string. Note that no was quoted to 'no' automatically; the dumper knows which strings would load as something else, a point covered in converting JSON to YAML without losing types.

Reading the file back with yaml.safe_load and listing types gave {'id': 'int', 'ref': 'str', 'total': 'str', 'placed': 'str', 'note': 'str', 'meta': 'dict'}. Every column that is not a basic type came back as str. That is the cost of text: the file carries values, and the program must know the column types, ideally from the table itself.

One command, no Python

PostgreSQL can build JSON, and yq can turn JSON into YAML:

psql -At -c "select jsonb_agg(to_jsonb(o) order by id) from orders o" | yq -P

- id: 1
  ref: 9f1c2f7e-0a3b-4c55-9d1e-7a7b1e2f3a4b
  meta:
    gift: true
  note: |-
    line1
    line2
  total: 19.90
  placed: "2026-10-27T08:00:00+00:00"
- id: 2
  ref: null
  meta: null
  note: "no"
  total: 0.10
  placed: "2026-10-27T09:30:00+00:00"

This gives readable block scalars for free, ISO timestamps and a short pipeline. It also changes two things. The keys come out in jsonb's internal order (shorter keys first, then alphabetical), not column order, because to_jsonb builds a jsonb object. And total: 19.90 is a bare YAML number: 19.90 is the exact text, and a YAML 1.1 loader reads it as the float 19.9. If exact decimals matter, cast in SQL (total::text) so the value is quoted. Choose the Python route when you need control of types and the shell route when you want a quick readable dump. In both, -At in psql turns off alignment and headers so only the data prints. The shell route also composes with the rest of a pipeline: add where created_at > now() - interval '1 day' to the query, redirect to a file in a scheduled job, and you have a daily snapshot of a small reference table that reads well in a diff.

Filtering and ordering

Export what you mean. Add an order by on the primary key so repeated exports diff cleanly, and a where for a subset. Exclude columns that change on every run or hold secrets (updated_at, tokens, password hashes). A stable column order matters as much, and sort_keys=False with a dict row factory keeps the table's order. For repeated exports to a repository, produce byte-identical output when nothing changed, so git shows no diff; that is covered in sorting keys for stable YAML output.

When a YAML export is not the right dump

You need a backup. An export holds values only. It omits indexes, constraints, sequences, ownership and permissions. Use pg_dump for backups and restores.

The table is large. Millions of rows as YAML are slow to write, huge and slow to read. Use COPY ... TO for CSV, or Parquet through DuckDB.

Binary columns. bytea values need base64, and YAML's !!binary tag is understood by few consumers outside Python.

The consumer needs exact types. YAML carries a handful of them. A schema or a typed format such as Avro or Parquet is better for round trips.

The relevant references are the psycopg documentation on row factories and the PostgreSQL JSON functions, including to_jsonb and jsonb_agg. To view the result in a readable layout, the YAML formatter is useful.