manage.py inspectdb: All redshift tables fail with unable to inspect table - function unnest(int2vector) does not exist

[davidthewatson]

I have:

17:51 $ pip freeze | grep redshift
django-redshift-backend==0.5

and:

17:59 $ pip freeze | grep Django
Django==1.10.3

I am running the following command:

python manage.py inspectdb

And I get the following output on every table in my redshift schema:

# Unable to inspect table 'dogs'
# The error was: function unnest(int2vector) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type     casts.

It seems like the driver is falling back to postgres 9.x despite my database settings:

DATABASES = {
    'default': {
        'ENGINE': 'django_redshift_backend',
        'NAME': 'dogs',
        'USER': 'user',
        'PASSWORD': 'password',
        'HOST': 'hostname',
        'PORT': '5439',
    }
}

[shatzistogias]

Same issue here

[dohq]

same problem

[Leekyungsoo]

same problem

[gauravnexus]

Any update on this issue?

[epineda]

I'm getting:

# The error was: function unnest(smallint[]) does not exist

I'm using django-redshift-backend version 0.9.1

Please, any news on this issue?

[Surgo]

Django currently supports PostgreSQL >= 9.4 and Redshift's API are 8.x based.
inspectdb run unnest(c.conkey) but that not supported by Redshift.

Related info

• Fixed #28626 -- Dropped support for PostgreSQL 9.3. django/django#9135
• Broken from django/django@d6b9aab

Notes

• supports_aggregate_filter_clause is True >= 9.4

[hlodver]

Is there any workaround for this issue?

[hlodver]

Based on detailed information from @Surgo (thanx!) I used a workaround by using older django version to do the inspection. I have A LOT of tables (and am lazy) so here it is:

mkdir tmp && cd tmp
virtualenv -p python2 venv
source venv/bin/activate
pip install django==1.4.0 psycopg2
django-admin.py startproject djangold
cd djangold
export PYTHONPATH=$(pwd)
export DJANGO_SETTINGS_MODULE=djangold.settings
# Update your settings to include 
# DATABASES = {
#    'default': {
#        'ENGINE': 'django.db.backends.postgresql_psycopg2',
#        'NAME': 'db_name',
#        'USER': 'username',
#        'PASSWORD': 'pwd',
#        'HOST': 'host',
#        'PORT': '5439',
#        'OPTIONS': {
#            'options': '-c search_path=schema_name',  # if using schema
#        }
#    },
#}
# Voila! Now it works
django-admin.py inspectdb

I hope this helps: @epineda @gauravnexus

[aroxby-kinnek]

Thanks for the above solution! For anyone getting:

TypeError: sequence index must be integer, not 'slice'

I was able to get around this by going all the way back to Django 1.2

[RicoChivela1]

Unable to inspect table 'auth_group' - Can anyone help with this mistake? Django...

[aroxby-kinnek]

@RicoChivela1, this message typically indicates that the table is missing. Did you run ./manage.py migrate or ./manage.py syncdb? If you're unfamiliar with Django I strongly recommend working through the tutorial.

[utapyngo]

I am getting

# Unable to inspect table 'table_name'
# The error was: syntax error at or near "WITH"
LINE 6:                     FROM unnest(c.conkey) WITH ORDINALITY co...

on

Django==2.2.5
django-redshift-backend==1.1.0

[miaoz2001]

Same issue here...Based on @hlodver 's solution (thx!) and I tested that the last working version of django is 1.7.11 which is slightly better than django==1.4 for me, in the way that 1.7.11 added one extra line

class Meta:
        managed = False

just for anyone who needs this...

[shimizukawa]

fixed by #90 . Thanks to @MattFisher !

[shimizukawa]

It has been released https://pypi.org/project/django-redshift-backend/3.0.0/