Issue: Postgres: db.create_tables() throws table already exists error
Author: dhrumilp31
State: closed
Created: 2021-11-03 18:22:09

I am trying to load schema from existing SQLite database and using those definitions, would like to create three new tables in Postgres db. Following is the code that I am using:
````
from peewee import SqliteDatabase, PostgresqlDatabase, Model
from playhouse.reflection import generate_models, print_model, print_table_sql

# Load existing prodigy database
sqlite_db = SqliteDatabase("prodigy.db")
models = generate_models(sqlite_db)
print('Models: {}'.format(models))

# Connect to a Postgres database and create tables
db = PostgresqlDatabase('prodigy_db', user='usr', password='pwd', host='host', port=5432)
db.create_tables(list(models.values()), safe=False)
print('DB: {}'.format(db.get_tables()))
db.close()
```` 

Schema:

````
dataset
  id AUTO PK
  name VARCHAR
  created INT
  meta BLOB
  session INT

index(es)
  name UNIQUE

example
  id AUTO PK
  input_hash INT
  task_hash INT
  content BLOB

link
  id AUTO PK
  example INT FK: example.id
  dataset INT FK: dataset.id

index(es)
  example_id
  dataset_id
````

Which creates the models successfully, but fails to create new tables in Postgres based on those models.
```

Models: {'dataset': <Model: dataset>, 'example': <Model: example>, 'link': <Model: link>}

Traceback (most recent call last):
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 3160, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: table "dataset" already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "setup_db.py", line 15, in <module>
    db.create_tables(list(models.values()), safe=False)
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 3348, in create_tables
    model.create_table(**options)
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 6721, in create_table
    cls._schema.create_all(safe, **options)
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 5828, in create_all
    self.create_table(safe, **table_options)
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 5683, in create_table
    self.database.execute(self._create_table(safe=safe, **options))
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 3173, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 3167, in execute_sql
    self.commit()
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 2933, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 191, in reraise
    raise value.with_traceback(tb)
  File "/home/ubuntu/prodigy/venv/lib/python3.6/site-packages/peewee.py", line 3160, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: table "dataset" already exists

```

The error says table 'dataset' already exists, but here is the describe table command on prodigy_db:

<img width="709" alt="Screen Shot 2021-11-03 at 2 14 28 PM" src="https://user-images.githubusercontent.com/87986360/140168892-5ff90270-72f9-4599-be92-7ffec2f1f211.png">


Am I missing something here? Thanks in advance.


You need to re-bind the models to the postgres database in order for them to be created on the Pg database:

```python
db = PostgresqlDatabase('prodigy_db', user='usr', password='pwd', host='host', port=5432)
db.bind(models.values())
db.create_tables(list(models.values()), safe=False)
```

Thanks @coleifer, it worked. Appreciate the quick response.