PostgreSQL help

dreamduster

Hello

I have 2 postgreSQL databases called "DATA1" and "DATA2" with several tables inside them (table A, B, C).

I modify the details in table "A" in database "DATA1"

How can I copy table "A" from database "DATA1" and paste it in database "DATA2" using the same table name ??

Thank you.

Sxooter

Do you want to do this from php, or from the command line.

From the command line, it's quite easy:

pg_dump db1name -t tablename | psql db2name

From php, you'd have to create the new table to match it and move the data by hand.

While is some functionality provided by dblink() in the contrib files to help with connecting between databases, in postgresql, databases are entities apart from each other. You might want to look at using schemas instead of databases, since you CAN operate on different schemas within the same database about the same as you'd operate on different databases in MySQL...

dreamduster

Sxooter wrote:
Do you want to do this from php, or from the command line.

From the command line, it's quite easy:

pg_dump db1name -t tablename | psql db2name

From php, you'd have to create the new table to match it and move the data by hand.

While is some functionality provided by dblink() in the contrib files to help with connecting between databases, in postgresql, databases are entities apart from each other. You might want to look at using schemas instead of databases, since you CAN operate on different schemas within the same database about the same as you'd operate on different databases in MySQL...

Correct me if i'm wrong..

So i create a database "MYDATABASE" and then create 2 schemas "MYSCHEMA1" and "MYSCHEMA2" and i create the tables "A", "B", "C" in both of them.

1 . Then it is possible to copy only surtain tables from "MYSCHEMA1" to "MYSCHEMA2" ??
2 . And then when i make DSN i call only the database "MYDATABASE" ??

thank you..

Sxooter

Within the same database, copying things like this is much easier. You can use the fairly straight forward syntax:

<set search path to dest schema here>
create table tablename as select x,y,z from otherschema.othertable where...;

Or if the table already exists:
insert into desttable (field1, field2, field3) select field1, field2, field3 from otherschema.othertable where...;