DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:sender:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition:x-google-sender-auth;
	b=dtxqKXmEscT3jLFWhsas4QrlEU2E9vXx0HBfn+fNnqiLlSffk42YB/EL9B5GcIFX2aX/Nzd4NaKiK/I9jkT6ew57Leg8qvt9aXxZkV0d2n8A1r4pl8WxAePz+9DDbFL/EpANGzUQv0BTH6qqG6qPSQDTYINkHS97EHB/tHqRED8=
Message-ID: <ddcb1c340708212136x1c3a5168ya884b252fa434ed7@mail.gmail.com>
Date: Wed, 22 Aug 2007 14:36:15 +1000
From: novice <user.postgresql@gmail.com>
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: raw data into table process
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.