Thread: raw data into table process

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.

Hi All ,

I have successfully installed pgcluster 1.7 rc7 and it is working fine .
But i have got a doubt on " Single Point Failure " .

I need a suggest from u all on this .

For an Application which demands High Availability and  no Single Point
Failure and built Multi-Master Replication system  , is PGCLUSTER - II
a suitable option.

Please suggest/support this issue .

Thanks in advance .

Best Regards,

Trinath Somanchi.


********************************************************************************
This email message (including any attachments) is for the sole use of the intended recipient(s)
and may contain confidential, proprietary and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended recipient,
please immediately notify the sender by reply email and destroy all copies of the original message.
Thank you.

Intoto Inc.

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
>