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, [EMAIL PROTECTED] wrote:
Date: Wed, 22 Aug 2007 14:36:15 +1000
From: novice <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Subject: raw data into table process
Message-ID:
<[EMAIL PROTECTED]>
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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
