Postgresql with pk_col='ID' reported "doesn't exist" #108

Closed
widoyo opened this Issue Sep 17, 2012 · 5 comments

Projects

None yet

2 participants

@widoyo
widoyo commented Sep 17, 2012

I have a primary column named 'ID' (UPPERCASE). Executing SQL into Postgresql "SELECT COUNT(ID) FROM inbox" postgresql reported "column doesn't exist". But it is return as expected when my SQL "SELECT COUNT('id') FROM inbox".

I found the problem when using flask-peewee that the error ofound at peewee.py line 1410.

The problem doesn't occur when I change column name from 'ID' into 'id'.

@coleifer
Owner

Can you share your model code with me? Just the class definition should be fine. If you can get me the SQL create table query as well that you ran that'd be great (ModelClass.create_table_query() i think). Were these models autogenerated by peewee or were they pre-existing?

@coleifer
Owner

I'm not sure if you have tried this, but you might write:

class MyModel(db.Model):
    ID = PrimaryKeyField()
    some_field = CharField()

That should work correctly, and in my testing locally appears to do the right thing.

@coleifer coleifer closed this Sep 17, 2012
@widoyo
widoyo commented Sep 17, 2012

I use pre existing database, I use Gammu with postgresql database.

...sql
-- Table: sentitems

-- DROP TABLE sentitems;

CREATE TABLE sentitems
(
"ID" serial NOT NULL,
"UDH" text NOT NULL,
"SMSCNumber" character varying(20) NOT NULL DEFAULT ''::character varying,
"Class" integer NOT NULL DEFAULT (-1),
"TextDecoded" text NOT NULL DEFAULT ''::text,
"SenderID" character varying(255) NOT NULL,
"SequencePosition" integer NOT NULL DEFAULT 1,
"Status" character varying(255) NOT NULL DEFAULT 'SendingOK'::character
varying,
"StatusError" integer NOT NULL DEFAULT (-1),
"TPMR" integer NOT NULL DEFAULT (-1),
"RelativeValidity" integer NOT NULL DEFAULT (-1),
"CreatorID" text NOT NULL,
id_folder integer NOT NULL DEFAULT 3,
)
...

And my model is

...python
class Sent(db.Model, PgModel):
ID = pw.PrimaryKeyField()
body = pw.TextField(db_column='TextDecoded')
destination = pw.CharField(max_length=20, db_column='DestinationNumber')

class Meta:
    db_table = 'sentitems'

...

and the error is:

psycopg2.ProgrammingError

ProgrammingError: column "id" does not exist LINE 1: SELECT COUNT(ID) FROM
"sentitems" ^

When I rename column 'ID' into 'id' there is no "programming error".
The programming error is because of "SELECT COUNT(ID) FROM ...", it is not
error when "SELECT COUNT('ID') FROM ...", I have tried from SQL command on
Postgresql.

On Mon, Sep 17, 2012 at 11:35 PM, Charles Leifer
notifications@github.comwrote:

Can you share your model code with me? Just the class definition should be
fine. If you can get me the SQL create table query as well that you ran
that'd be great (ModelClass.create_table_query() i think). Were these
models autogenerated by peewee or were they pre-existing?

Widoyo

@coleifer
Owner

Hmm -- something strange is going on. Is it only SELECT COUNT() queries that don't work? Is the SQL you pasted generated by dumping your existing database, or is it something else? I'm curious to see exactly what your current database schema looks like

@widoyo
widoyo commented Sep 18, 2012

The problem could be on the postgresql with column named ID. Sql generated
from pgadminIII

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment