Skip site navigation (1) Skip section navigation (2)

Loading data into Postgres

From: Przemyslaw Bak <przemol(at)st3(dot)makro(dot)com(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: Loading data into Postgres
Date: 1998-05-31 15:22:37
Message-ID: Pine.SV4.3.96.980531164247.23497A-100000@STORE3 (view raw or flat)
Thread:
Lists: pgsql-general
Hi,

every night my script (using perl) will load about 50.000 rows of
data into Postgres. I know two ways:

In first version of my script I used method by inserting, eg:
---------- cut ----------
#!/bin/perl

use Pg;

open (MyData, "...");
while (<MyData>) {
  chop;
  ...
  &insert_cust_sales ($NDate, $CustNo, $ArtGrp, $Qty, $Amount);
}
close MyData;

sub insert_cust_sales {
  my $SQL_QUERY = "insert into cust_sales values ('$_[0]',$_[1],$_[2],$_[3],$_[4],$_[5]);";
  my $conn = Pg::connectdb ("dbname = store3");
  my $result = $conn->exec ( $SQL_QUERY );
}
---------- cut ----------
but it is very slow and my disk works very havy.
So I created another solution, look below:
---------- cut ----------
#!/bin/perl

# Open connection to Postgres
open (PSQL, "| psql store3") || die "Can not open connection to Postgres\n";
print PSQL " copy cust_sales from stdin using delimiters '|';";

# Read data from file
open (MyData, "...");
while (<MyData>) {
  s/	//g; # remove \t
  s/\s+//g;    # ... and spaces
  print PSQL,$_,"\n";
}
print PSQL "\\.\n";
close PSQL;
close MyData;
--------- cut ----------
but while running this script I receive message "resetting connection".
Why ? What's wrong with this script ? I have seen in sources
of postgres that this message is when copy failed, but why ?

przemol


pgsql-general by date

Next:From: Marin DDate: 1998-05-31 15:36:33
Subject: libpq.so missing
Previous:From: Marin DDate: 1998-05-31 15:10:43
Subject: no shared libs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group