3

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 have been beating my head on this for a few hours now. 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.

Any help is appreciated.

8

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'
  • Yup. That did it. Ugh. I'm still learning SQL (obviously) - How do I know when to use the parentheses and when not to? – Kevin Jan 10 '14 at 20:50
  • examples in the documentation are a pretty good guide. This one is even pretty similar to your query: postgresql.org/docs/9.1/static/sql-selectinto.html – WileyB Jan 10 '14 at 20:53
  • 4
    The SQL grammar has a lot of quirks, and this highlights one of them. INSERT statements (canonically) start with INSERT INTO table (col1, col2, ...). But for reasons probably lost to the mists of time, when SELECT was invented they chose not to have an outer set of parens around the expression list. It's just SELECT expr1, expr2, ... FROM .... Adding the parens (as you did) actually selects one "column" whose value is a list. – csd Jan 10 '14 at 20:58

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.