psycopg/psycopg2 · error · ValueError

cannot switch from automatic field numbering to manual

Error message

cannot switch from automatic field numbering to manual

What it means

Raised by SQL.format() (lib/sql.py:241) when a template mixes automatic ({}) and manual ({0}) field numbering, specifically when an auto-numbered placeholder was already seen (autonum counter > 0) and then a manual index appears. Mirrors the rule enforced by built-in str.format: a single template must use one style throughout. The library tracks the auto counter and, once started, forbids explicit indices to avoid ambiguous argument binding.

Source

Thrown at lib/sql.py:241

            select * from "people" where "id" = %s

        """
        rv = []
        autonum = 0
        for pre, name, spec, conv in _formatter.parse(self._wrapped):
            if spec:
                raise ValueError("no format specification supported by SQL")
            if conv:
                raise ValueError("no format conversion supported by SQL")
            if pre:
                rv.append(SQL(pre))

            if name is None:
                continue

            if name.isdigit():
                if autonum:
                    raise ValueError(
                        "cannot switch from automatic field numbering to manual")
                rv.append(args[int(name)])
                autonum = None

            elif not name:
                if autonum is None:
                    raise ValueError(
                        "cannot switch from manual field numbering to automatic")
                rv.append(args[autonum])
                autonum += 1

            else:
                rv.append(kwargs[name])

        return Composed(rv)

    def join(self, seq):
        """

View on GitHub (pinned to 3a6d9d6ddc)

Solutions

Make all placeholders automatic ({}) and pass positional args in the right order.
Or make all placeholders explicitly numbered ({0}, {1}, ...) consistently across the whole template.
Best for readability: use named placeholders ({tbl}, {col}) with keyword arguments.

Example fix

// before
q = sql.SQL("select {} from {0}").format(sql.Identifier('c'), sql.Identifier('t'))
// after
q = sql.SQL("select {0} from {1}").format(sql.Identifier('c'), sql.Identifier('t'))

Defensive patterns

Strategy: validation

Validate before calling

import string

def template_numbering_is_consistent(template: str) -> bool:
    seen_auto = seen_manual = False
    for _pre, name, _spec, _conv in string.Formatter().parse(template):
        if not name:
            continue
        if name.isdigit():
            seen_manual = True
        else:
            seen_auto = True
        if seen_auto and seen_manual:
            return False
    return True

assert template_numbering_is_consistent(tpl)

Prevention

Pick one numbering style per template and stick to it.
Prefer named placeholders ({tbl}, {col}) to avoid the auto/manual pitfall entirely.
When editing a template, re-read all placeholders rather than just the one you changed.

When it happens

Trigger: A template like sql.SQL("select {} from {0}").format(a, b) - the first {} starts automatic mode, then {0} is manual and trips the check. Also sql.SQL("{} {} {1}").format(...).

Common situations: Refactoring or partially editing a template that ended up mixing styles; concatenating fragments built with different conventions; copy-pasting one placeholder from another query.

Related errors

AI-assisted analysis of psycopg/psycopg2@3a6d9d6ddc (2026-08-04). Data as JSON: /data/errors/58bf7aa91a5322bd.json. Report an issue: GitHub.