Message-ID: <46CC1C9D.4080304@pws.com.au>
Date: Wed, 22 Aug 2007 21:23:09 +1000
From: Russell Smith <mr-russ@pws.com.au>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: novice <user.postgresql@gmail.com>
CC: pgsql-general@postgresql.org,  pgsql-sql@postgresql.org
Subject: Re: raw data into table process
References: <ddcb1c340708212136x1c3a5168ya884b252fa434ed7@mail.gmail.com>
In-Reply-To: <ddcb1c340708212136x1c3a5168ya884b252fa434ed7@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

novice wrote:
> 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,
> )
> 

If your on unix, why not use those tools first?
awk '{print $1 "\t" $2 "\t" $3 "\t" $4 " " $5}' sample.dat > sample.tab

-- Begin SQL script
CREATE TEMP TABLE maintenance_tmp (
   meter_id integer,
   status text,
   inspector text,
   inspection_date timestamp with time zone
);

SET datestyle='ymd';
\copy maintenance_tmp FROM sample.tab
INSERT INTO maintenance (meter_id, status, inspector, inspection_date)
   SELECT DISTINCT meter_id, status, inspector, inspection_date FROM 	
          maintenance_tmp ORDER BY inpsection_date;

ANALYZE maintenance;

-- End SQL Script
[snip]

> Any thoughts and suggestions welcome.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>