CREATE TABLE hecto_fitted_idlist (hecto_date varchar, hecto_dataset varchar, hecto_spec varchar);
\copy hecto_fitted_idlist FROM /data1/rcsed_v2/postgres/hecto_fitted_idlist.csv WITH CSV HEADER;
UPDATE hecto_fitted_idlist SET hecto_date = hecto_date || '0' WHERE char_length(hecto_date)=8;
CREATE TABLE hecto_fitted_2 (hecto_date varchar, hecto_dataset varchar, hecto_spec varchar);
\copy hecto_fitted_2 FROM /data1/rcsed_v2/postgres/hecto_fitted_2.csv WITH CSV HEADER;
UPDATE hecto_fitted_2 SET hecto_date = hecto_date || '0' WHERE char_length(hecto_date)=8;
SELECT hectospec_date, hectospec_dataset, hectospec_spec INTO TABLE hecto FROM full_spec_spoint WHERE survey='hectospec';
UPDATE hecto SET hectospec_dataset=REPLACE(hectospec_dataset, '%3A', '3A');
UPDATE hecto SET hectospec_dataset=REPLACE(hectospec_dataset, '%2B', '2B');
UPDATE hecto SET hectospec_spec=REPLACE(hectospec_spec, '%3A', ':');
UPDATE hecto SET hectospec_spec=REPLACE(hectospec_spec, '%2B', '+');
SELECT COUNT(*) from hecto;

SELECT * INTO tables_hecto FROM (SELECT * FROM hecto_fitted_idlist UNION ALL SELECT * FROM hecto_fitted_2) AS tmp;
SELECT COUNT(*) from tables_hecto;

SELECT * INTO hecto_nofit_list FROM hecto as b LEFT JOIN tables_hecto as a ON a.hecto_date=b.hectospec_date AND a.hecto_dataset=b.hectospec_dataset AND a.hecto_spec=b.hectospec_spec;
DELETE FROM hecto_nofit_list WHERE hecto_date is not null;
SELECT COUNT(*) from hecto_nofit_list;

\COPY (SELECT * FROM hecto_nofit_list) TO /data1/rcsed_v2/postgres/hecto_nofit_list2.csv WITH CSV HEADER;

DROP TABLE hecto_fitted_idlist;
DROP TABLE hecto;
DROP TABLE hecto_nofit_list;
DROP TABLE hecto_nofit_list2;
DROP TABLE hecto_fitted_2;
DROP TABLE matched;
