4

I am using sqlalchemy(sqlalchemy-redshift) as engine with pandas, while writing to redshift with to_sql i get the following error:

sqlalchemy.exc.NotSupportedError: (psycopg2.NotSupportedError) SQL command "CREATE INDEX ix_western_union_answer_pivot_index ON western_union_answer_pivot (index)" not supported on Redshift tables.
 [SQL: 'CREATE INDEX ix_western_union_answer_pivot_index ON western_union_answer_pivot (index)'] (Background on this error at: http://sqlalche.me/e/tw8g)

While i understand the problem , How to create an Index in Amazon Redshift

I have two questions, 1. shouldn't sqlalchemy-redshift translate the create index to a redshift supporting sortkey statement? Thats the point of using ORM right?

  1. As a workaround can i stop to_sql from creating the db index?

UPDATE:

on setting index=False in to_sql, the above issue is solved but i endup with sqlalchemy.exc.DataError: (psycopg2.DataError) value too long for type character varying(256)

Is 256 the max size in redshift? Any solution to this apart from slicing the data to 256 and losing information?

codename_subho
  • 456
  • 8
  • 22
  • I don't know what pandas generated `CREATE TABLE` statement looks like, but in redshift you can create `VARCHAR(65536)` columns - there's no _dynamic_ string type like Postgres' `TEXT`. But also be aware that there's a performance penalty for using very large columns in Redshift. – botchniaque Apr 08 '19 at 13:59

0 Answers0