psycopg/psycopg2 · error · ValueError

no format specification supported by SQL

Error message

no format specification supported by SQL

What it means

Raised by SQL.format() (lib/sql.py:230) when a placeholder in the SQL template carries a format specification, i.e. the part after a colon like {0:<10} or {0:d}. psycopg2 intentionally implements only a restricted subset of str.format: it uses placeholders purely to splice Composable objects, so alignment/width/type specs are meaningless and rejected. The whole template is parsed up front via string.Formatter().parse() and any non-empty spec triggers this ValueError before any composition happens.

Source

Thrown at lib/sql.py:230

        Example::

            >>> print(sql.SQL("select * from {} where {} = %s")
            ...     .format(sql.Identifier('people'), sql.Identifier('id'))
            ...     .as_string(conn))
            select * from "people" where "id" = %s

            >>> print(sql.SQL("select * from {tbl} where {pkey} = %s")
            ...     .format(tbl=sql.Identifier('people'), pkey=sql.Identifier('id'))
            ...     .as_string(conn))
            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(

View on GitHub (pinned to 3a6d9d6ddc)

Solutions

1. Remove the ':spec' portion from every placeholder in the SQL template (e.g. {0:<10} -> {0}, {n:>20} -> {n}).
2. If padding/transform is genuinely needed, apply it to the rendered string after as_string() returns, never inside the template.
3. Render Python values as SQL literals with sql.Literal and do any Python-side formatting before wrapping them.

Example fix

// before
query = sql.SQL("select {0:<10} from tbl").format(sql.Identifier('col'))
// after
query = sql.SQL("select {} from tbl").format(sql.Identifier('col'))

import string

def sql_template_is_clean(template: str) -> bool:
    """True if no placeholder carries a format spec or conversion."""
    for _pre, _name, spec, conv in string.Formatter().parse(template):
        if spec or conv:
            return False
    return True

# use before building the SQL:
assert sql_template_is_clean(tpl), f'bad SQL template: {tpl!r}'
q = sql.SQL(tpl).format(...)

When it happens

Trigger: Calling sql.SQL(...).format(...) where the template string contains a colon-specifier, e.g. sql.SQL("select {0:<10}").format(sql.Identifier('x')), sql.SQL("{n:>20}").format(n=...), or any placeholder of the form {name:spec} / {idx:spec}.

Common situations: Copying a Python f-string or str.format template that used padding/alignment or numeric formatting (logging, report code) and pasting it into sql.SQL(...) without stripping the spec. Misunderstanding that SQL.format is a deliberately limited version of str.format.

Related errors

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

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