From: Russell Smith <mr-russ@pws.com.au>
To: novice <user.postgresql@gmail.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: raw data into table process
Date: Wed, 22 Aug 2007 21:23:09 +1000
Message-ID: <46CC1C9D.4080304@pws.com.au> (raw)
In-Reply-To: <ddcb1c340708212136x1c3a5168ya884b252fa434ed7@mail.gmail.com>
References: <ddcb1c340708212136x1c3a5168ya884b252fa434ed7@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mr-russ@pws.com.au, user.postgresql@gmail.com, pgsql-general@postgresql.org
Subject: Re: raw data into table process
In-Reply-To: <46CC1C9D.4080304@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox