pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedraw data into table process
3+ messages / 3 participants
[nested] [flat]
* raw data into table process
@ 2007-08-22 04:36 novice <user.postgresql@gmail.com>
2007-08-22 11:23 ` Re: raw data into table process Russell Smith <mr-russ@pws.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: novice @ 2007-08-22 04:36 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: raw data into table process
2007-08-22 04:36 raw data into table process novice <user.postgresql@gmail.com>
@ 2007-08-22 11:23 ` Russell Smith <mr-russ@pws.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Russell Smith @ 2007-08-22 11:23 UTC (permalink / raw)
To: novice <user.postgresql@gmail.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: raw data into table process
@ 2007-08-22 11:36 Steve Midgley <public@misuse.org>
0 siblings, 0 replies; 3+ messages in thread
From: Steve Midgley @ 2007-08-22 11:36 UTC (permalink / raw)
To: pgsql-sql-owner@postgresql.org; pgsql-sql
Hi,
I'm not sure if you have access to a scripting language (like perl or
ruby) but my experience is that if you transform the source text file
into a secondary text file that postgres "copy" can read
natively/directly into the data formats you want, the copy command will
move everything into your primary table and way faster than using sql
to transform.
I've had great experience with using Ruby/regex to do text file
transforms such as this - it's amazingly fast (considering many say
it's a performance dog).
The best way to get started is copy some sample data OUT of PG to a
file and then use your scripting language to build a transformation
that formats exactly like your sample. (I've imported polygon, circle
and point types using this method, as well as dates, like what you're
trying to do).
I hope that helps - drop me a line off-list if you'd like some sample
Ruby code to read/write/transform your source.
Steve
At 06:33 AM 8/22/2007, pgsql-sql-owner@postgresql.org wrote:
>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
>Message-ID:
><ddcb1c340708212136x1c3a5168ya884b252fa434ed7@mail.gmail.com>
>
>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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-08-22 11:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-22 04:36 raw data into table process novice <user.postgresql@gmail.com>
2007-08-22 11:23 ` Russell Smith <mr-russ@pws.com.au>
2007-08-22 11:36 Re: raw data into table process Steve Midgley <public@misuse.org>
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