I have a table with 9,000 records (MYdb) and 4 visualisations linked to the table.
I am at a point where I have another 800 records (update001) to add to the original table. I expect I will have to do this three times a year.

Data is in the same format in both tables.

I have tried the following SQL insert into MYdb select * from Update001

This creates an error column "quantity" is of type integer but expression is of type text.

Any ideas as the best way to do this?

All the columns are Strings except two, quantity and year that are set as number.
I have checked both the tables for the column name and format and both I have the exact same details in both tables.

The year column was in a different sequence in the table.

  • make sure that quantity is defined as type integer in both tables? – toms Mar 13 '15 at 21:53
  • Both tables have quantity set as Number. – Lorcan Mar 14 '15 at 13:52
  • 1
    Do both table have the same numbers of columns and of the same types. It would help if you included your table structures in the question. The error suggests that you have a column type mismatch somewhere. – John Powell Mar 14 '15 at 14:01
  • Please use these instructions to merge your accounts. – PolyGeo Mar 18 '15 at 10:16

It looks like your second upload may have chosen different column types than your initial one. If (hopefully) your new data is in fact all the same types as the original, an explicit cast should be sufficient to achieve your ends.

Make sure you reference the columns from the update table in the same order they appear in the original table.

insert into MYdb select myfirstcolumn, quantity::integer, year::integer from Update001

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.