I have a PostGIS database with an empty table ch01.highways. With the following code, I want to populate the table:

INSERT INTO ch01.highways (gid, feature, name, state, geom) 
SELECT gid, feature, name, state, ST_TRANSFORM(geom, 2163) 
FROM ch01.highways_staging 
WHERE feature LIKE 'Principal Highway%'

The ch01.highways_staging table was imported with the shp2pgsql-gui. For both tables, the owner is postgres.

When I run the command the ch01.highways remains empty, no data are imported.

  • do you get any error message? did you check, if there is data in the ch01.highways_staging table? – LaughU Jul 6 '17 at 9:32
  • no, no error message. yes, I have the data I need in the ch01.highways_staging table. There is also a spatial index for that. – geofee Jul 6 '17 at 9:54
  • 1
    It is a huge mistake to ever load or manipulate data as the primary administrator account, be it root, SYS, or postgres. You really ought to create a login or three and start over from scratch before you accidently harm the database instance. You haven't provided the result of the SELECT statement without the INSERT. While creating a spatial index is good procedure, it has nothing to do with a query with no spatial constraint. Please edit the question in response to requests for clarification; it's not fair to those who would answer to need to mine the comments for details. – Vince Jul 6 '17 at 10:18
  • could you add sample data from that ch01.highways_staging table (screenshot maybe)? do you need to update a potentially populated table or do you just want to create that table like that (for you could do that in one step)? – ThingumaBob Jul 6 '17 at 15:42
  • what do you mean by adding sample data? I can visualize the ch01.highways_staging table with QGIS – geofee Jul 10 '17 at 11:00

Your Answer

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

Browse other questions tagged or ask your own question.