INSERT INTO chem_too
(lab_nbr, loc_name, sample_date, param, quant, units, qa_qc,
easting, northing, remark)
SELECT *
FROM chemistry
Natural Inner join (
SELECT loc_name, sample_date, param, Count(*) as duplicate_count
FROM chemistry
GROUP BY loc_name, sample_date, param) grouped
WHERE duplicate_count > 1;
psql -f cp_dups.sql nevada
psql:cp_dups.sql:10: ERROR: INSERT has more expressions than target columns
LINE 4: SELECT *
^
The INSERT INTO clause lists all columns in both tables so I fail to
understand to what the error message refers.
Rich
----------------------------------------------------------------------
The INSERT and SELECT portions of the query are independent; the column
listing in the INSERT does not affect the select. The only thing that
matters is that the DATA TYPE of the matching pairs are the same. For
instance:
INSERT INTO table1(field1, field2)
SELECT 'One' AS fieldA, 'Two'
Would work assuming that both field1 and field2 are text; fieldA gets
inserted into field1 and the unnamed second column gets inserted into
field2.
SELECT * --> means uses every column from the FROM/JOIN tables. Try issuing
the SELECT by itself and see what columns (and in what order) it returns.
In this case it will, at minimum, return a "duplicate_count" column which is
not going to be in the "chem_too" table. Thus, you need to replace the "*"
in the SELECT with the specific columns that correspond to the columns
listed in to INSERT portion of the query. Likely this means
INSERT INTO chem_too (lab_nbr, loc_name, sample_date, param, quant, units,
qa_qc, easting, northing, remark)
SELECT lab_nbr, loc_name, sample_date, param, quant, units, qa_qc, easting,
northing, remark -- in the original "*" expansion the duplicate_count field
is present AND quite possibly the order of the fields is messed up
FROM chemistry
NATURAL JOIN ( ... ) WHERE duplicate_count > 1;
IF the chemistry table is a true copy of the chem_too table you can shorten
the above in two ways; though using NATURAL JOIN may cause them to fail due
to column order. The above is the safest way to write the query -
explicitly specify all fields in both the INSERT and the SELECT portions of
the query.
INSERT INTO chem_too (lab_nbr, loc_name, sample_date, param, quant, units,
qa_qc, easting, northing, remark)
SELECT chemistry.*
FROM chemistry NATURAL JOIN ( ... ) WHERE duplicate_count > 1
OR, even further,
INSERT INTO chem_too
SELECT chemistry.*
FROM chemistry NATURAL JOIN ( ... ) WHERE duplicate_count > 1;
David J.
