I am trying to record the following entries into a table. I'm curious
to know if there's an efficient/effective way of doing this? This
entries come from an ancient datalogger (note: separated by space and
uses YY/MM/DD format to record date)
Plain file sample.dat
3665 OK BS 07/08/16 07:28
3665 CC BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
4532 OK BS 07/08/16 07:34
4004 OK BS 07/08/16 07:51
3991 OK BS 07/08/16 07:54
This is the table that I'm adding the entries to
CREATE TABLE maintenance
(
maintenance_id SERIAL PRIMARY KEY,
meter_id integer,
status text,
inspector text,
inspection_date timestamp with time zone,
)
-- Begin SQL Script
-- First table to dump the records in
CREATE TABLE dataload1
(data text)
-- Dump records using \copy
\copy dataload1 FROM sample.dat
-- Second table to import unique records ONLY
CREATE TABLE dataload2 AS
SELECT DISTINCT
data FROM dataload1;
-- Now I update unique records into the maintenance table
-- maintenance_id is SERIAL so it will be populated automatically
INSERT INTO maintenance(meter_id, status, inspector, inspection_date)
SELECT substr("data", 1, 4)::int
, substr("data", 8, 3)
, substr("data", 21, 2)
, (20||substr("data", 24, 2) ||'-'|| substr("data", 27, 2) ||'-'||
substr("data", 30, 2)||' '||substr("data", 33, 5))::timestamp as
inspection_date
FROM dataload2
-- So the new records will also be in timestamp order
ORDER BY inspection_date ;
-- Some housekeeping
VACUUM FULL VERBOSE ANALYZE maintenance;
-- Finally, drop the temporary tables
DROP TABLE dataload1
DROP TABLE dataload2
-- End SQL script
Any thoughts and suggestions welcome.