2

I have a postcode centres table created from a shapefile and a census data table that is a CSV with postcodes. I need to add the census data columns to the postcode centres table. I would normally join them in QGIS based on the postcode and create the columns that way, but the files are too big and QGIS keeps crashing. So I've uploaded them into PostGIS and I am now trying to run a sql query on the data via the DB Manager plugin.

My data is in PostGIS accessed though QGIS 2.14. I'm very new to SQL.

INSERT INTO postcode_centres 
(total_pop, males_pop, females_pop, occupied_house_pop)
   (SELECT postcode_census.total, postcode_census.males, 
    postcode_census.females, postcode_census.occupied_house FROM postcode_census
WHERE postcode_centres.postcode = postcode_census.postcode);

closed as off-topic by user30184, whyzar, Vince, nmtoken, Richard Law Jul 11 '17 at 22:03

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – user30184, whyzar, Vince, nmtoken, Richard Law
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Are you trying to insert new rows to your table postcode_centres or do you want to add data columns to existing features? – Pahbloo Marks Jul 11 '17 at 16:49
  • I would like to add columns to existing festures – T.O.6 Jul 12 '17 at 8:00
  • 1
    There is no GIS-centric problem here -- It's still a SQL syntax issue. When you write to Database Administrators, you'll want to distinguish between a desire to INSERT rows and UPDATE columns. Providing SQL definitions of the tables (CREATE TABLE ...) and a graphic that helps explain your goal will be helpful. A little research into the difference between INSERT and UPDATE (and their very different syntax) might solve your problem directly. – Vince Jul 12 '17 at 11:50
1

Your select statement needs to run properly which will need to include a join between the two tables and also there's no table called 'females' so femails.postcode will not work - assuming you meant postcode_census.postcode, the SQL might look like this:

INSERT INTO postcode_centres 
(total_pop, males_pop, females_pop, occupied_house_pop)

   (

   SELECT postcode_census.total, postcode_census.males, 
    postcode_census.females, postcode_census.occupied_house 

FROM postcode_census as a 
join postcode_centers as b 
on  postcode_centers.postcode = postcode_census.postcode

);
  • Sorry about the females error, I typed that out wrong. When I used the join I am still get this error: LINE 5: ON postcode_centres.postcode = postcode_census.postcode – T.O.6 Jul 11 '17 at 16:30
  • does the SELECT query work on its own before you put it into the INSERT statement? – DPSSpatial Jul 11 '17 at 16:45
  • For a slightly cleaner syntax, if the join-columns are the same, you can do USING (postcode) – Evan Carroll Jul 11 '17 at 17:30
  • No the select statement doesn't work. ERROR: missing FROM-clause entry for table "postcode_censes" LINE 4: ON postcode_centres.postcode = postcode_censes.postcode ^ ********** Error ********** ERROR: missing FROM-clause entry for table "postcode_censes" SQL state: 42P01 Character: 188 – T.O.6 Jul 12 '17 at 9:03
  • get that select statement to work first then your insert should be fine... – DPSSpatial Jul 19 '17 at 3:16

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