Issue: reserved word user table on postgresql?
Author: davidthewatson
State: closed
Created: 2011-12-20 00:17:02

I have the following which I copied from some sample code:

```
from flask_peewee.auth import BaseUser
import datetime
from peewee import *
from app import db

class User(db.Model, BaseUser):
    username = CharField(unique=True)
    password = CharField()
    email = CharField()
    join_date = DateTimeField(default=datetime.datetime.now)
    active = BooleanField(default=True)
    admin = BooleanField(default=False)

def __unicode__(self):
    return self.username
```

and the following config.py:

```
# Configuration for PostgreSQL
DATABASE = {
    'name': 'whatever',
    'engine': 'peewee.PostgresqlDatabase',
    'user': 'me',
    'password': 'mine'
} 
```

but when I run this against Postgresql on Fedora Core 15, I get:

```
Traceback (most recent call last):
File "run_mine.py", line 7, in <module>
  main.create_tables()
File "/home/watson/mine/main.py", line 15, in create_tables
  User.create_table(fail_silently=True)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 1770, in create_table
  cls._meta.database.create_table(cls)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 287, in create_table
  self.execute(query, commit=True)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 252, in execute
  res = cursor.execute(sql, params or ())
psycopg2.ProgrammingError: syntax error at or near "user"
LINE 1: CREATE TABLE user (username VARCHAR(255) NOT NULL, admin SMA...
```

It turns out that postgresql defines a user table by default and apparently its a reserved word. It was already there with my postgresql user already created in it before I even ran my flask app. 

```
mine=> select * from user;
current_user 
--------------
me
(1 row)
```

I'd recommend discouraging the use of "user" in any documentation relating to postgresql tables in peewee and also perhaps catching it and throwing a warning or error before it gets to the database driver. I tried making a simple change to my code to use users in place of user, but it was still blowing up in postgresql, though I'm not sure why. I'm open to advice here if you have any - I'm still intending to use peewee but Postgresql is a requirement.

Thanks!


I'd suggest trying the following:

```
class User(db.Model, BaseUser):
    # whatever fields

    class Meta:
        db_table = 'users' # <-- set explicitly right here
```


Thanks for your response.

That gets it past the creation stage, but then it dies further down in peewee. It's not clear to me that my application code is implicated here, i.e. it doesn't appear in the stack anywhere.

```
Traceback (most recent call last):
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask/app.py", line 1518, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask/app.py", line 1506, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask/app.py", line 1504, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask/app.py", line 1264, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask/app.py", line 1260, in full_dispatch_request
    rv = self.preprocess_request()
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask/app.py", line 1387, in preprocess_request
    rv = func()
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask_peewee/auth.py", line 187, in load_user
    g.user = self.get_logged_in_user()
  File "/home/watson/mine/env/lib/python2.7/site-packages/flask_peewee/auth.py", line 144, in get_logged_in_user
    return self.User.select().where(active=True).get(id=session.get('user_pk'))
  File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 978, in get
     obj = self.where(*args, **kwargs).paginate(1, 1).execute().next()
  File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 1082, in execute
    self._qr = QueryResultWrapper(self.model, self.raw_execute())
  File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 825, in raw_execute
    return self.database.execute(query, params, self.requires_commit)
  File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 252, in execute
    res = cursor.execute(sql, params or ())
ProgrammingError: column "active" does not exist
LINE 1: SELECT * FROM user WHERE active = 1 AND id = 1 LIMIT 1
```

My user class now looks like:

```
class User(db.Model, BaseUser):
    username = CharField(unique=True)
    password = CharField()
    email = CharField()
    join_date = DateTimeField(default=datetime.datetime.now)
    active = BooleanField(default=True)
    admin = BooleanField(default=False)

    def __unicode__(self):
        return self.username

    class Meta:
        db_table = 'users' # Postgres reserves user as a keyword
```

And the database in postgres:

```
=# select * from users;
 username | admin | id |         join_date          | active |                    password                    | email 
----------+-------+----+----------------------------+--------+------------------------------------------------+-------
 admin    |     1 |  1 | 2011-12-20 10:55:07.186137 |      1 | 1b7e1$9112656704dad1f93fdb40727e5d5ea33bf46616 | 
(1 row)
```


I'm going to open a new issue on flask_peewee since it doesn't honor the settings in the model - the meta users database table change on model breaks mysql also.