Re: raw data into table process

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.