psycopg/psycopg2 · error · ValueError

unsupported format character: '%s'

Error message

unsupported format character: '%s'

What it means

Raised by _split_sql() (lib/extras.py:1329-1330) when the query passed to execute_values contains a percent-format token whose character is neither 's' (placeholder) nor '%' (literal). For example '%d', '%f', '%(', '%y'. psycopg2's mogrify only supports '%s' and named '%(name)s' for execute_values templates; other printf-style conversions are unsupported.

Source

Thrown at lib/extras.py:1329

    """
    curr = pre = []
    post = []
    tokens = _re.split(br'(%.)', sql)
    for token in tokens:
        if len(token) != 2 or token[:1] != b'%':
            curr.append(token)
            continue

        if token[1:] == b's':
            if curr is pre:
                curr = post
            else:
                raise ValueError(
                    "the query contains more than one '%s' placeholder")
        elif token[1:] == b'%':
            curr.append(b'%')
        else:
            raise ValueError("unsupported format character: '%s'"
                % token[1:].decode('ascii', 'replace'))

    if curr is pre:
        raise ValueError("the query doesn't contain any '%s' placeholder")

    return pre, post


# ascii except alnum and underscore
_re_clean = _re.compile(
    '[' + _re.escape(' !"#$%&\'()*+,-./:;<=>?@[\\]^`{|}~') + ']')

View on GitHub (pinned to 3a6d9d6ddc)

Solutions

1. Escape every literal '%' as '%%' in the execute_values query string.
2. Replace printf-style format tokens (%d, %f) with '%s' and pass properly typed values, or use psycopg2.sql composition.
3. Audit LIKE clauses: 'WHERE name LIKE 'a%%' instead of 'WHERE name LIKE 'a%'.

Example fix

// before
execute_values(cur, "INSERT INTO log(msg) VALUES %s WHERE ts > 100%%", data)
// after
execute_values(cur, "INSERT INTO log(msg) VALUES %s", data)

import re
bad = re.findall(r'%(?!s|%\?)', sql)  # crude check for unsupported tokens
# Better: ensure only %s and %% appear:
assert all(t in ('%s', '%%') for t in re.findall(r'%.', sql)), 'unsupported format token in query'

def query_uses_only_valid_tokens(sql: str) -> bool:
    import re
    return all(t == '%s' or t == '%%' for t in re.findall(r'%.', sql))

try:
    execute_values(cur, sql, data)
except ValueError as e:
    if 'unsupported format character' in str(e):
        sql = sql.replace('%', '%%')  # escape literals, then re-add one %s
    else: raise

When it happens

Trigger: Passing a query like execute_values(cur, "INSERT INTO t VALUES %d", data) or any query containing a stray '%' followed by an unsupported character. Even a literal '%' in a LIKE clause (e.g. 'LIKE %foo%') triggers it if not doubled.

Common situations: Including a literal percent sign in a default/LIKE/string without escaping it as '%%'. Borrowing a query from a logging/printf context that uses %d/%f. The error message substitutes the offending character for the '%s' in the message itself.

Related errors

• the query contains more than one '%s' placeholder
• the query doesn't contain any '%s' placeholder
• bound flags not valid: {bounds!r}
• invalid name: {name!r}
• expected string or None as name, got {name!r}

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