Thread: copying tables
Hi. I am trying to copy tables. I want to copy a table in one database to a
table in another database. The tables are identical. Here is to code and the
error when using DBI. Can I just use postgresQL somehow?
Thank you.
Table = fs648
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| day | text | var |
| type | text | var |
| flight | text | var |
| scene | text | var |
| pattern | int4 | 4 |
| leg | text | var |
| id | int4 not null | 4 |
+----------------------------------+----------------------------------+-------+
#!/usr/bin/perl -wT
use strict;
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=misr_l1;
host=sundog.larc.nasa.gov;port=5432", "", "")
or die "Can't connect\n"; #connect to database
my $sth = $dbh->prepare("SELECT * from fs648");
$sth->execute();
my $array_ref = $sth->fetchall_arrayref();
$sth->finish;
$dbh->disconnect or warn "Disconnection failed\n";
$dbh = DBI->connect("dbi:Pg:dbname=misr_l0;
host=sundog.larc.nasa.gov;port=5432", "", "")
or die "Can't connect\n"; #connect to database
foreach my $row ($array_ref)
{ my ($day, $type, $flight, $scene, $pattern, $leg, $id) = @$row;
my $dayq = $dbh->quote($day);
my $typeq = $dbh->quote($type);
my $flightq = $dbh->quote($flight);
my $sceneq = $dbh->quote($scene);
my $legq = $dbh->quote($leg);
my $rows = $dbh->do("INSERT INTO fs648 VALUES ( $dayq, $typeq,
$flightq, $sceneq, $pattern, $legq, $id)");
}
$dbh->disconnect or warn "Disconnection failed\n";
DBD::Pg::db do failed: ERROR: parser: parse error at or near "xe1154"
$pattern and $id are integers and so don't need quoting. It doesn't work if I
quote them anyway.
Thanks again!
Bob Kernell
Research Scientist
Analytical Services & Materials, Inc.
NASA Atmospheric Sciences Competency
Radiation and Aerolsols Branch
email: r.w.kernell@larc.nasa.gov
tel: 757-827-4631
Check out pg_dump: basically, this works -- pg_dump -t tablename olddbname > table.out followed by psql -d newdbname < table.out or psql -d newdbname -f table.out On Tue, 20 Feb 2001, Robert Kernell wrote: > Hi. I am trying to copy tables. I want to copy a table in one database to a > table in another database. The tables are identical. Here is to code and the > error when using DBI. Can I just use postgresQL somehow? > > Thank you. > > Bob Kernell > Research Scientist > Analytical Services & Materials, Inc. > NASA Atmospheric Sciences Competency > Radiation and Aerolsols Branch > email: r.w.kernell@larc.nasa.gov > tel: 757-827-4631 > -- James Eric Lawson Research Publications Editor III National Simulation Resource eric@bioeng.washington.edu - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Everyday language is a part of the human organism and is no less complicated than it. - Ludwig Wittgenstein (1889-1951) [Tractatus Logico-Philosophicus, 1921]
use pg_dump with the -t option.
--rob
----- Original Message -----
From: "Robert Kernell" <kernell@sundog.larc.nasa.gov>
To: <pgsql-general@postgresql.org>; <pgsql-novice@postgresql.org>;
<dbi-users-subscribe@perl.org>
Sent: Tuesday, February 20, 2001 10:51 AM
Subject: copying tables
> Hi. I am trying to copy tables. I want to copy a table in one database to
a
> table in another database. The tables are identical. Here is to code and
the
> error when using DBI. Can I just use postgresQL somehow?
>
> Thank you.
>
> Table = fs648
>
+----------------------------------+----------------------------------+-----
--+
> | Field | Type |
Length|
>
+----------------------------------+----------------------------------+-----
--+
> | day | text |
var |
> | type | text |
var |
> | flight | text |
var |
> | scene | text |
var |
> | pattern | int4 |
4 |
> | leg | text |
var |
> | id | int4 not null |
4 |
>
+----------------------------------+----------------------------------+-----
--+
>
> #!/usr/bin/perl -wT
>
> use strict;
> use DBI;
>
> my $dbh = DBI->connect("dbi:Pg:dbname=misr_l1;
> host=sundog.larc.nasa.gov;port=5432", "", "")
> or die "Can't connect\n"; #connect to database
> my $sth = $dbh->prepare("SELECT * from fs648");
> $sth->execute();
> my $array_ref = $sth->fetchall_arrayref();
> $sth->finish;
> $dbh->disconnect or warn "Disconnection failed\n";
> $dbh = DBI->connect("dbi:Pg:dbname=misr_l0;
> host=sundog.larc.nasa.gov;port=5432", "", "")
> or die "Can't connect\n"; #connect to database
> foreach my $row ($array_ref)
> { my ($day, $type, $flight, $scene, $pattern, $leg, $id) = @$row;
> my $dayq = $dbh->quote($day);
> my $typeq = $dbh->quote($type);
> my $flightq = $dbh->quote($flight);
> my $sceneq = $dbh->quote($scene);
> my $legq = $dbh->quote($leg);
> my $rows = $dbh->do("INSERT INTO fs648 VALUES ( $dayq, $typeq,
> $flightq, $sceneq, $pattern, $legq, $id)");
> }
> $dbh->disconnect or warn "Disconnection failed\n";
>
> DBD::Pg::db do failed: ERROR: parser: parse error at or near "xe1154"
>
> $pattern and $id are integers and so don't need quoting. It doesn't work
if I
> quote them anyway.
>
> Thanks again!
>
> Bob Kernell
> Research Scientist
> Analytical Services & Materials, Inc.
> NASA Atmospheric Sciences Competency
> Radiation and Aerolsols Branch
> email: r.w.kernell@larc.nasa.gov
> tel: 757-827-4631
>
>
Does pg_dump work with BLOBs yet ?
terry
> ----------
> From: Rob Arnold[SMTP:rob@cabrion.com]
> Sent: Tuesday, February 20, 2001 9:31 PM
> To: Robert Kernell; pgsql-general@postgresql.org;
> pgsql-novice@postgresql.org; dbi-users-subscribe@perl.org
> Subject: Re: copying tables
>
> use pg_dump with the -t option.
>
> --rob
>
> ----- Original Message -----
> From: "Robert Kernell" <kernell@sundog.larc.nasa.gov>
> To: <pgsql-general@postgresql.org>; <pgsql-novice@postgresql.org>;
> <dbi-users-subscribe@perl.org>
> Sent: Tuesday, February 20, 2001 10:51 AM
> Subject: copying tables
>
>
> > Hi. I am trying to copy tables. I want to copy a table in one database
> to
> a
> > table in another database. The tables are identical. Here is to code and
> the
> > error when using DBI. Can I just use postgresQL somehow?
> >
> > Thank you.
> >
> > Table = fs648
> >
> +----------------------------------+----------------------------------+---
> --
> --+
> > | Field | Type |
> Length|
> >
> +----------------------------------+----------------------------------+---
> --
> --+
> > | day | text |
> var |
> > | type | text |
> var |
> > | flight | text |
> var |
> > | scene | text |
> var |
> > | pattern | int4 |
> 4 |
> > | leg | text |
> var |
> > | id | int4 not null |
> 4 |
> >
> +----------------------------------+----------------------------------+---
> --
> --+
> >
> > #!/usr/bin/perl -wT
> >
> > use strict;
> > use DBI;
> >
> > my $dbh = DBI->connect("dbi:Pg:dbname=misr_l1;
> > host=sundog.larc.nasa.gov;port=5432", "", "")
> > or die "Can't connect\n"; #connect to database
> > my $sth = $dbh->prepare("SELECT * from fs648");
> > $sth->execute();
> > my $array_ref = $sth->fetchall_arrayref();
> > $sth->finish;
> > $dbh->disconnect or warn "Disconnection failed\n";
> > $dbh = DBI->connect("dbi:Pg:dbname=misr_l0;
> > host=sundog.larc.nasa.gov;port=5432", "", "")
> > or die "Can't connect\n"; #connect to database
> > foreach my $row ($array_ref)
> > { my ($day, $type, $flight, $scene, $pattern, $leg, $id) = @$row;
> > my $dayq = $dbh->quote($day);
> > my $typeq = $dbh->quote($type);
> > my $flightq = $dbh->quote($flight);
> > my $sceneq = $dbh->quote($scene);
> > my $legq = $dbh->quote($leg);
> > my $rows = $dbh->do("INSERT INTO fs648 VALUES ( $dayq, $typeq,
> > $flightq, $sceneq, $pattern, $legq, $id)");
> > }
> > $dbh->disconnect or warn "Disconnection failed\n";
> >
> > DBD::Pg::db do failed: ERROR: parser: parse error at or near "xe1154"
> >
> > $pattern and $id are integers and so don't need quoting. It doesn't work
> if I
> > quote them anyway.
> >
> > Thanks again!
> >
> > Bob Kernell
> > Research Scientist
> > Analytical Services & Materials, Inc.
> > NASA Atmospheric Sciences Competency
> > Radiation and Aerolsols Branch
> > email: r.w.kernell@larc.nasa.gov
> > tel: 757-827-4631
> >
> >
>
>