Error when passing a list parameter to a query for Redshift

[dilekc]

Hi,

I am having trouble while passing list arguments to a query for Redshift. When I run the same query for an AWS RDS postgresql table it works but fails for a table in Redshift.

Below is the code I am running.

import os
import boto3
import pg8000 as ps

S3_CLIENT = boto3.client('s3')
ps.paramstyle = "pyformat"

def test_list_params_in_rds():

    db_connect = {
        'user': '',
        'database': 'postgres',
        'host': '',
        'port': '',
        'password': ''
    }

    conn = ps.connect(**db_connect)

    conn.autocommit = True
    cursor = conn.cursor()
    q1 = """
        SELECT *
        FROM mytable
        WHERE item_id = ANY(%(item_id)s);
        """

    item_list = [8517, 8555]
    cursor.execute(q1, {'item_id': item_list})
    print(cursor.fetchall())

def test_list_params_in_redshift():

    db_connect = {
        'user': '',
        'database': 'redshift',
        'host': '',
        'port': '',
        'password': ''
    }

    conn = ps.connect(**db_connect)

    conn.autocommit = True
    cursor = conn.cursor()
    q1 = """
        SELECT *
        FROM mytable
        WHERE item_id = ANY(%(item_id)s);
        """

    item_list = [8517, 8555]
    cursor.execute(q1, {'item_id': item_list})
    print(cursor.fetchall())

if __name__ == '__main__':

    test_list_params_in_rds()
    test_list_params_in_redshift()

And the error message I get from test_list_params_in_redshift() call:

  File "redshift_test.py", line 94, in <module>
    test_list_params_in_redshift()
  File "redshift_test.py", line 86, in test_list_params_in_redshift
    cursor.execute(q1, {'item_id': item_list})
  File "/Users/donal/envs/hp-qa-lambdas/lib/python3.6/site-packages/pg8000/core.py", line 852, in execute
    self._c.execute(self, operation, args)
  File "/Users/donal/envs/hp-qa-lambdas/lib/python3.6/site-packages/pg8000/core.py", line 1813, in execute
    self.handle_messages(cursor)
  File "/Users/donal/envs/hp-qa-lambdas/lib/python3.6/site-packages/pg8000/core.py", line 1879, in handle_messages
    raise self.error
"pg8000.core.ProgrammingError: ('ERROR', 'XX000', 'Assert', '\n  \n  error:  Assert\n  code:      1000\n  context:   IsA((Node*)arrayExpr->args->tail->data.ptr_value, Const)  \n  query:     2060467\n  location:  xen_execute.cpp:6057\n  process:   padbmaster [pid=28452]\n  \n', '/home/ec2-user/padb/src/sys/xen_execute.cpp', '6849', 'pg_throw')"

And versions

pg8000 (1.11.0)
python 3.6

Thanks!!!

[tlocke]

Hi @dilekc, sorry to sound unhelpful but pg8000 only supports PostgreSQL, and Redshift is different from PostgreSQL, even though Redshift was forked from PostgreSQL.