Re: COPY from .csv File and Remove Duplicates

On Fri, 12 Aug 2011, David Johnston wrote:

> Select *
> From table
> Natural Inner join (
> SELECT loc_name, sample_date, param, Count(*) as duplicate_count
> FROM table
> Group by loc_name, sample_date, param
> ) grouped
> Where duplicate_count > 1;

   Tried to use the above in an INSERT INTO statement to a copy of the main
data table. Here's what I've tried and how postgres responds:

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