Django syncdb on SQL initial data using PostgreSQL yields "column ... does not exist"

Question

Platform: Python 2.5, Django development root, PostgreSQL 8.4, Windows Vista Ultimate SP2. Procedure: Django Documentation, Release 1.0, link text, Section 34.2, Providing initial SQL data.

CODE:

models.py:  

class aisc_customary(models.Model):
    MTYPE                 = models.CharField(max_length=4, editable=False, 
                                help_text="Shape type, e.g. W, C, L, etc.")
    EDI_STD_NOMENCLATURE  = models.CharField(max_length=26, editable=False, 
                                help_text="EDI shape designation")
    AISC_MANUAL_LABEL     = models.CharField(max_length=26, editable=False, primary_key=True, 
                                help_text="AISC Manual label")
    T_F                   = models.CharField(max_length=1, editable=False, 
                                help_text="Special note flag, T or F")
    W                     = models.FloatField(editable=False, 
                                help_text="Nominal weight, lbs/ft")
... (45 more FloatFields)


application1/sql/aisc_customary.sql:  

    INSERT INTO application1_aisc_customary (MTYPE, EDI_STD_NOMENCLATURE, AISC_MANUAL_LABEL, T_F, W, A, D, HT, OD, BF, B, ID, TW, TF, T, TNOM, TDES, KDES, KDET, K1, X, Y , E0, XP, YP, BF_2TF, B_T, H_TW, H_T, D_T, IX, ZX, SX, RX, IY, ZY, SY, RY, RZ, J, CW, C, WNO, SW, QF, QW, RO, H, TAN_ALPHA, QS) VALUES ('W', 'W44X335', 'W44X335', 'F', 335, 98.5, 44.0, 0, 0, 15.9, 0, 0, 1.03, 1.77, 0, 0, 0.00, 2.56, 2.63, 1.31, 0.00, 0.00, 0.00, 0.00, 0.00, 4.50, 0.00, 38.0, 0.00, 0.00, 31100, 1620, 1410, 17.8, 1200, 236, 150, 3.49, 0.00, 74.7, 535000, 0.00, 168, 1180, 278, 805, 0.00, 0.00, 0.00, 0.00);
    INSERT INTO application1_aisc_customary (MTYPE, EDI_STD_NOMENCLATURE, AISC_MANUAL_LABEL, T_F, W, A, D, HT, OD, BF, B, ID, TW, TF, T, TNOM, TDES, KDES, KDET, K1, X, Y , E0, XP, YP, BF_2TF, B_T, H_TW, H_T, D_T, IX, ZX, SX, RX, IY, ZY, SY, RY, RZ, J, CW, C, WNO, SW, QF, QW, RO, H, TAN_ALPHA, QS) VALUES ('W', 'W44X290', 'W44X290', 'F', 290, 85.4, 43.6, 0, 0, 15.8, 0, 0, 0.865, 1.58, 0, 0, 0.00, 2.36, 2.44, 1.25, 0.00, 0.00, 0.00, 0.00, 0.00, 5.02, 0.00, 45.0, 0.00, 0.00, 27000, 1410, 1240, 17.8, 1040, 205, 132, 3.49, 0.00, 50.9, 461000, 0.00, 166, 1040, 248, 701, 0.00, 0.00, 0.00, 0.00);
    INSERT INTO application1_aisc_customary (MTYPE, EDI_STD_NOMENCLATURE, AISC_MANUAL_LABEL, T_F, W, A, D, HT, OD, BF, B, ID, TW, TF, T, TNOM, TDES, KDES, KDET, K1, X, Y , E0, XP, YP, BF_2TF, B_T, H_TW, H_T, D_T, IX, ZX, SX, RX, IY, ZY, SY, RY, RZ, J, CW, C, WNO, SW, QF, QW, RO, H, TAN_ALPHA, QS) VALUES ('W', 'W44X262', 'W44X262', 'F', 262, 76.9, 43.3, 0, 0, 15.8, 0, 0, 0.785, 1.42, 0, 0, 0.00, 2.20, 2.25, 1.19, 0.00, 0.00, 0.00, 0.00, 0.00, 5.57, 0.00, 49.6, 0.00, 0.00, 24100, 1270, 1110, 17.7, 923, 182, 117, 3.47, 0.00, 37.3, 405000, 0.00, 165, 928, 223, 630, 0.00, 0.00, 0.00, 0.00);

... (1965 more lines like this)

Django development server works fine and PostgreSQL server is working and answers queries about data of other models when the troublesome initial data file is removed from its standard path.

With previous versions of bad tables dropped using pgAdmin III, console command "python manage.py syncdb" yields this error:

Creating table application1_aisc_customary Installing custom SQL for application1.aisc_customary model Failed to install custom SQL for application1.aisc_customary model: column "mtype" of relation "application1_aisc_customary" does not exist LINE 1: INSERT INTO application1_aisc_customary (MTYPE, EDI_STD_NOME...

A carat points to the M of MTYPE. The error notwithstanding, column (upper case) MTYPE does exist, as may be seen using pgAdmin III. Note that Django admin reports the table, but it has no records.

I've tried unicode and ANSI encoding for the SQL, taking editable=False off the model attributes, and lower case names for everything but the model attributes. Maybe I'm missing some preparatory SQL statement. I'm striking out. I would be extremely grateful for an enlightening response. Thanks in advance for your help.

09/21/09: For the record, zalew's answer is correct. Lower-case field names are needed. I also had to change one field name, id (inside diameter) to i_d in order to correct the obvious conflict with the primary key. I changed od to o_d to match. Problem solved.

it doesn't solve your question, but why you load sql instead of a fixture for the initial data? — zalew
– zalew, Commented Sep 20, 2009 at 3:40
Why not? That is, if it can be made to work! Recasting the original .csv database as SQL was easy, with all the names on the left and all the values on the right. I did it with macros in Notepad++. JSON or YAML, on the other hand, will require a short Python script. That's what I'll do, though, if I can't figure out what the problem is with the SQL. I am not aware of any great advantage to either method, given that I propose no use of custom SQL through this facility beyond the INSERT INTO commands. Possibly the SQL will load quicker, but it's a one-time thing, and not a consideration. — Thomas B. Higgins
– Thomas B. Higgins, Commented Sep 20, 2009 at 6:06

zalew · Accepted Answer · 2009-09-21 11:41:14Z

1

I ran a test, with the same result as you. U must use lowercase field names so it works. However, u don't have to rewrite the sql, can leave uppercase in the sql, having lowercase in the model definition and it'll work just fine! which is strange because PgSql column names are case-sensitive. On the other side, Django won't let you have two fields - one lowercase and one uppercase with the same name (probably blocked due to various db systems django works with), so... still strange :)

Cannot find any background details on this issue though. Just follow the lowercase convention. Edit the model fields to lower and run your sql.

edited Sep 21, 2009 at 11:41

answered Sep 21, 2009 at 11:35

zalew

10.3k3 gold badges31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas B. Higgins Over a year ago

Your insight is the answer, zalew. Thank you for your help! Lower case field names turned the trick. This is ideal, as most of the nomenclature is closer to standard in lower case anyway, and looks better to me as an engineer. I did a little more searching and saw that MySql does not permit case-sensitive field names. Perhaps Django wants lower case for field names but converts them to upper case for queries to any DBMS. This is peculiar and unexpected. I also see that column name case sensitivity is a pretty big issue in general for users of MySQL who are converting to/from other DBMSs.

Fragsworth · Accepted Answer · 2009-09-20 01:25:55Z

0

Hmm. Shot in the dark, maybe your custom SQL is being executed before the table is created via the syncdb command?

answered Sep 20, 2009 at 1:25

Fragsworth

36k27 gold badges86 silver badges99 bronze badges

1 Comment

Thomas B. Higgins Over a year ago

The Django document says the SQL code is executed just after the CREATE TABLE statements. I tend to think they would have given the timing issue some thought. Thanks for your thoughts, though.

Collectives™ on Stack Overflow

Django syncdb on SQL initial data using PostgreSQL yields "column ... does not exist"

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related