coleifer/peewee — Issue #30: reserved word user table on postgresql?
Author: davidthewatson
Created: 2011-12-20 00:17:02+00:00
State: open

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
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!