psycopg/psycopg2 · error · ValueError

cannot switch from manual field numbering to automatic

Error message

cannot switch from manual field numbering to automatic

What it means

Raised by SQL.format() (lib/sql.py:248) - the mirror of error [22]. It fires when a manually-numbered placeholder ({0}) was already seen (autonum set to None) and then an auto-numbered ({}) placeholder follows. The library forbids switching numbering style mid-template, exactly like str.format.

Source

Thrown at lib/sql.py:248

                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):
        """
        Join a sequence of `Composable`.

        :param seq: the elements to join.
        :type seq: iterable of `!Composable`

        Use the `!SQL` object's *string* to separate the elements in *seq*.
        Note that `Composed` objects are iterable too, so they can be used as

View on GitHub (pinned to 3a6d9d6ddc)

Solutions

1. Make all placeholders explicitly numbered ({0}, {1}, ...) consistently.
2. Or make all placeholders automatic ({}) and rely on positional arg order.
3. Use named placeholders ({tbl}, {col}) with kwargs to remove ambiguity entirely.

Example fix

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

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)

When it happens

Trigger: A template like sql.SQL("select {0} from {}").format(a, b) - {0} puts it in manual mode, then {} tries to resume automatic numbering. Also sql.SQL("{1} {}").format(...).

Common situations: Same as [22]: partial edits, merged fragments, copy-paste between queries with different placeholder conventions.

Related errors

• no format specification supported by SQL
• no format conversion supported by SQL
• cannot switch from automatic field numbering to manual
• invalid name: {name!r}
• Identifier cannot be empty

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