VARCHAR versus TEXT when using psychopg2, sqlalchemy and redshift

From: Christopher Brooks <brooksch(at)umich(dot)edu>
To: psycopg(at)postgresql(dot)org
Subject: VARCHAR versus TEXT when using psychopg2, sqlalchemy and redshift
Date: 2015-08-30 18:59:13
Message-ID: CAJQ6OJMr+nnmzFP4Tt-f41+sysGZ_aTd_h7hEoRBddsTPwaHEQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: psycopg

I'm using SQLAlchemy backed by psychopg2 to interact with my data inside of
AWS Redshift. I'm trying to insert data and am getting a perplexing error
with respect to text vs varchar data. Here is a source snipit:

conn = get_db_connection()
keys=["a","b"]
values=["aa","bb"]

tbl_qualtrics_dim = Table('qualtrics_dim', metadata,
Column('k', String(1024), primary_key=True),
Column('v', String(1024), nullable=False)
)
metadata.create_all(conn, checkfirst=True)
ins = tbl_qualtrics_dim.insert().values(k=keys, v=values)
conn.execute(ins)

This creates the table just fine as being two columns of varchar(1024).
AWS doesn't support clob types. The last line throws an error though when
trying to insert data:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "k" is
of type character varying but expression is of type text[]
HINT: You will need to rewrite or cast the expression.
[SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (%(k)s, %(v)s)']
[parameters: {'v': ['aa', 'bb'], 'k': ['a', 'b']}]

A cast as I understand it doesn't work, e.g. I change this line:

ins = tbl_qualtrics_dim.insert().values(k=cast(keys, String(1024)),
v=values)

And I get a casting error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast
type text[] to character varying
[SQL: 'INSERT INTO qualtrics_dim (k, v) VALUES (CAST(%(param_1)s AS
VARCHAR(1024)), %(v)s)'] [parameters: {'v': ['aa', 'bb'], 'param_1': ['a',
'b']}]

Any hints on what might be going wrong here?

Regards,

Chris

Responses

Browse psycopg by date

  From Date Subject
Next Message Christophe Pettus 2015-08-30 19:01:58 Re: VARCHAR versus TEXT when using psychopg2, sqlalchemy and redshift
Previous Message Gregory Arenius 2015-08-27 19:21:31 Re: How do I convert numpy NaN objects to SQL nulls?