CODEWITHSUNDEEP
pythondjangopostgresql
Grigoriy Mikhalkin
Grigoriy Mikhalkin

Reputation: 5573

Django migrations. How to check if table exists in migrations?

I'm currently working on app built on Django 1.8 and Postgres. This app is installed in several environments, in some of them there old tables in DB from which i need to delete records.

I wrote migration with following SQL query:

IF EXISTS (
    SELECT relname FROM pg_class WHERE relname=tablename
) THEN 
    DELETE FROM tablename END IF;

But Django throws error at this query:

django.db.utils.ProgrammingError: syntax error at or near "IF"

Can i somehow check, in migration, that table exists, and only then execute query, like DROP FROM tablename ?

Upvotes: 8

Views: 10537

Answers (2)

mrts
mrts

Reputation: 18925

The easiest way to check if a table exists is to use django.db.connection.introspection.table_names():

from django.db import connection

...

all_tables = connection.introspection.table_names()
old_tables = set('old_table_1', 'old_table_2')
existing_old_tables = old_tables.union(all_tables)
# clean tables in existing_old_tables with migrations.RunSQL() as suggested above

Upvotes: 13

Grigoriy Mikhalkin
Grigoriy Mikhalkin

Reputation: 5573

Solved it using django.db.connection. Code:

from django.db import migrations
from django.db import connection


class Migration(migrations.Migration):
    db_cursor = connection.cursor()
    check_exists_query = "SELECT relname FROM pg_class WHERE relname=%s;"
    base_query = "DELETE FROM {table} WHERE condition;"
    tables = [tables]
    existing_tables = []

    for table in tables:
        db_cursor.execute(check_exists_query, [table])
        result = db_cursor.fetchone()
        if result:
            existing_tables.append(table)

    operations = [
        migrations.RunSQL(base_query.format(table=existing_table)) for existing_table in existing_tables
    ]

Upvotes: 1

Related Questions