How to insert in postgres Array Column using python Psycopg2 and parameter binding?

Question

Following is my table structure in postgresql:

CREATE TYPE lang AS ENUM ('english','hindi','marathi');
CREATE TABLE assignment (
    title varchar(100),
    ids lang[]
);

I want to insert a record in assignment table using parameter binding in python. I tried below code:

cursor.execute('insert into assignment (title, ids) values (%s, %s)',["person1",["english","hindi"]])

But it generates invalid query like:

insert into assignment (title, ids) values ('person1', ARRAY['english','hindi'])

And hence I get syntax error in query.

Correct query is:

insert into assignment (title, ids) values ('person1', '{"english","hindi"}')

Can someone let me know way to generate correct query using parameter binding?

ERROR: column "ids" is of type lang[] but expression is of type text[] — Branel Moro, Apr 03 '17 at 19:13

score 4 · Answer 1 · answered Apr 03 '17 at 19:09

4

Cast the passed array to lang[]:

cursor.execute('''
    insert into assignment (title, ids) 
    values (%s, %s::lang[])
    ''', ["person1",["english","hindi"]])
)

answered Apr 03 '17 at 19:09

Clodoaldo Neto

1 Answers1