19

I want to execute this sql via psyopg2:

select indexname from pg_indexes where (tablename, indexname) in ( 
      ('tab1', 'index1'),
      ('tab2', 'index2') 
);

Here is the code:

cursor.execute(
'select tablename, indexname from pg_indexes where (tablename, indexname) IN %s;', [
    [('tab1', 'col1'), ('tab2', 'col2')],
               ])

I get this exception:

ProgrammingError: syntax error at or near "ARRAY"
LINE 1: ...e from pg_indexes where (tablename, indexname) IN ARRAY[('ta...

How to pass a list of tuples to PostgreSQL vis psyopg2?

guettli
  • 25,042
  • 81
  • 346
  • 663

1 Answers1

22

If you pass a tuple instead a list, it works:

cursor.execute(
'select tablename, indexname from pg_indexes where (tablename, indexname) IN %s;', [
    tuple([('tab1', 'col1'), ('tab2', 'col2')]),
               ])

Don't ask my why it fails if you pass a list.

guettli
  • 25,042
  • 81
  • 346
  • 663
  • 2
    A Python list is adapted by Psycopg to a Postgresql array as you saw in the error message. A tuple is adapted to a record. – Clodoaldo Neto Oct 29 '15 at 10:57