psycopg2.ProgrammingError

[qin4zhang]

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

0.22.1

Expected results

World's Bank Data of examples can be seen

Actual results

Traceback (most recent call last):

  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/superset/connectors/sqla/models.py", line 569, in query
    df = self.database.get_df(sql, self.schema)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/superset/models/core.py", line 673, in get_df
    df = pd.read_sql(sql, eng)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/pandas/io/sql.py", line 416, in read_sql
    chunksize=chunksize)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/pandas/io/sql.py", line 1087, in read_query
    result = self.execute(*args)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/pandas/io/sql.py", line 978, in execute
    return self.connectable.execute(*args, **kwargs)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2064, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 939, in execute
    return self._execute_text(object, multiparams, params)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1097, in _execute_text
    statement, parameters
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/usr/local/python-2.7.9/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: (psycopg2.ProgrammingError) column "sp_pop_totl" does not exist
LINE 3:        SUM(SP_POP_TOTL) AS "sum__SP_POP_TOTL"
                   ^
 [SQL: 'SELECT region AS region,\n       country_code AS country_code,\n       SUM(SP_POP_TOTL) AS "sum__SP_POP_TOTL"\nFROM wb_health_population\nWHERE year >= \'1960-01-01 00:00:00\'\n  AND year <= \'2018-03-15 10:59:49\'\nGROUP BY region,\n         country_code\nORDER BY "sum__SP_POP_TOTL" DESC\nLIMIT 50000']
2018-03-15 10:59:49,133:INFO:root:Caching for the next 86400 seconds

Steps to reproduce

1. In config.py SQLALCHEMY_DATABASE_URI, change it into postgres.
2. click dashboard and click World's Bank Data, then error is coming.

[teewhyli]

@qin4zhang was this resolved? im having the same issue.

EDIT: I just wanted to add i was able to 'fix' this issue by migrating my main db into mysql and its working as intended now.

I'm unsure sure whether the recent version freeze raised by #4953 has affected the way sqlalchemy processes psql or whether psql's strict pure sql approach renders certain data columns incompatible.

regardless, if anyone encounters this error i would suggest them to switch to mysql, atleast for now.

[qin4zhang]

@TianyangLi I have changed into MySQL for superset metadata.

[sohelsarder]

My superset version info is : { "version": "0.25.6"
I tried first with postgres 9.6 as a meta storage and experienced the same issue.
then switched into MySQL 5.7 and it works perfectly.

[andy-clapson]

This is a Postgres-specific issue related to column names - Postgres converts all column names to lower case unless they are quoted. So, expected behaviour, but something we can account for. I'm working on a PR for this.

[ali-bahjati]

The problem still persists. @andy-clapson did you open a PR for this?