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'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.

Improve this question

1 Answer 1

Reset to default
10

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'
Improve this answer
3
  • 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, 2014 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, 2014 at 20:53
  • 5
    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, 2014 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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