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

Show quoted text

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]

Does pg_dump work with BLOBs yet ?

terry

Show quoted text

----------
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