1

When inserting into my table I get this error:

ERROR: column "brandid" of relation "Item" does not exist

The brandId column has a foreign key constraint on it that links it to the id of another table.

the table I am inserting into is defined as such: 

Column  |  Type   |                      Modifiers                      | Storage  | Stats target | Description 
---------+---------+-----------------------------------------------------+----------+--------------+-------------
 id      | integer | not null default nextval('"Item_id_seq"'::regclass) | plain    |              | 
 name    | text    | not null                                            | extended |              | 
 price   | money   | not null                                            | plain    |              | 
 sizes   | json    | not null                                            | extended |              | 
 brandId | integer | not null                                            | plain    |              | 
 deptId  | integer | not null                                            | plain    |              | 
Indexes:
    "item_pk" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "Item_fk0" FOREIGN KEY ("brandId") REFERENCES "Brand"(id)
    "Item_fk1" FOREIGN KEY ("deptId") REFERENCES "Department"(id)

I am trying to do the following insert statement: 

INSERT INTO "Item" (name, price, sizes, brandId, deptId) VALUES
        ('Air Force 1', '120.00', '{"12" : 1 , "10" : 12}',
            (SELECT id FROM "Brand" WHERE name= 'Nike'),
            (SELECT id FROM "Department" WHERE name= 'Mens Shoes'));

All the id columns across my database are of type serial.

The Brand and Department tables have been populated already and those select statements have been tested and work correctly.

StanleyDharan
  • 13
  • 4
  • Is there more than one Nike brand shoe in your Brand table? Same question with Department shoes? I would load them into a variable and see if your actually getting scalar results. I've honestly never tried doing an INSERT like that. Not sure it would work, and I'd be suspicious of it's performance. – Jamie Marshall Sep 11 '18 at 03:25

1 Answers1

0

Error tells you that pgsql cannot find field brandid (instead of brandId as you expected). Difference is i vs I. Try put field name in insert query in double quotes 

INSERT INTO "Item" (name, price, sizes, "brandId", "deptId") VALUES
    ('Air Force 1', '120.00', '{"12" : 1 , "10" : 12}',
        (SELECT id FROM "Brand" WHERE name= 'Nike'),
        (SELECT id FROM "Department" WHERE name= 'Mens Shoes'));
webcitron
  • 124
  • 11
  • Wow that worked thanks! why did I have to wrap those two columns with "" but not the other ones? – StanleyDharan Sep 11 '18 at 03:24
  • Because these two columns have capitalize letters in their names. PostgreSQL make lowercase all ddl names not wrapped by double quotes. That’s why good convention is use camel_case when you designing your database – webcitron Sep 11 '18 at 03:40