Postgres error due to the use of '%' for pattern matching in the parameterized query

Question

I have a parameterized query to be executed on a Postgres database:

SELECT
    DISTINCT ON (customer_id) customer_id,
    COALESCE('Terminated') AS note,
    TO_CHAR((DATE_TRUNC('DAY', note_date)), 'YYYY-mm-dd') AS note_date
FROM
    notes
WHERE
    note LIKE 'Terminated.%'
    AND note_date = %s
LIMIT
    1

I'm running the query in python:

def execute_query(creds, sql_file_path, param1):
    with create_db_conn(creds) as cursor:
        sql_file = open(sql_file_path, "r")
        query = sql_file.read()
        try:
            cursor.execute(query, (param1,))
        except psycopg2.DatabaseError as error:
            print(f"Query failed to execute: {error}")
        # Fetch all results
        data = cursor.fetchall()
    return data

When I run this query, I get the following error message:

Traceback (most recent call last):
  File "/__main__.py", line 64, in execute_query
    cursor.execute(query, (param1,))
IndexError: tuple index out of range

The query runs successfully when I remove the pattern match subclause note LIKE 'Terminated.%'. How do I fix this error while keeping the pattern match subclause in the query? The psycopg2 docs don't describe alternative placeholders other than %s. Thanks in advance for any input or resources.

Trouble Bucket · Accepted Answer · 2022-08-15 21:33:14Z

1

Escape the % using a second % symbol in the query like this:

SELECT
    DISTINCT ON (customer_id) customer_id,
    COALESCE('Terminated') AS note,
    TO_CHAR((DATE_TRUNC('DAY', note_date)), 'YYYY-mm-dd') AS note_date
FROM
    notes
WHERE
    note LIKE 'Terminated.%%'
    AND note_date = %s
LIMIT
    1

Also the psycopg2 docs address this:

The typical example is with single quotes in strings: in SQL single quotes are used as string literal delimiters, so the ones appearing inside the string itself must be escaped, whereas in Python single quotes can be left unescaped if the string is delimited by double quotes.

answered Aug 15, 2022 at 21:33

Trouble Bucket

3532 gold badges4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres error due to the use of '%' for pattern matching in the parameterized query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related