[GIS] Using INSERT in PostgreSQL

postgispostgresqlsql

I am trying to execute the following:

    INSERT INTO lithic (cp_east, cp_north, type, material, art_count, comment, field_no, gps_unit, initials, rec_date, cortex, geom)

SELECT (cp_east, cp_north, type, material, art_count, comment, field_no, gps_unit, initials, rec_date, cortex, geom) 

FROM lithic_9375_1230

WHERE lithic_9375_1230.rec_date > '12/18/2013'

My goal is to insert the rows in lithic_9375_1230 with rec_date later than 12/18/2013 into the table lithic. When I execute the above code I get the following:

ERROR: INSERT has more target columns than expressions
SQL state: 42601
Hint: The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?
Character: 30

I'm running PostgreSQL 9.1 which was installed as a component of the OpenGeo suite. The two tables are different versions of the same shapefile which have been imported into Postgres via the pgShapeLoader tool. They use the same SRID.

Best Answer

I think it's a simple SQL syntax problem. Remove the parens from your SELECT, i.e.:

INSERT INTO lithic
  (cp_east, cp_north, type, material, art_count, comment, field_no,
  gps_unit, initials, rec_date, cortex, geom)
SELECT
  cp_east, cp_north, type, material, art_count, comment, field_no,
  gps_unit, initials, rec_date, cortex, geom
FROM lithic_9375_1230
WHERE lithic_9375_1230.rec_date > '12/18/2013'
Related Question