Re: Syntax To Create Table As One In Another Database

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rich Shepard
Sent: Tuesday, November 15, 2011 11:42 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Syntax To Create Table As One In Another Database

I need a pointer to the appropriate docs that show me how to specify a
table in a different database.

What I want is to CREATE TABLE <tablename> AS TABLE
<otherdatabase><same_tablename>; but using a period (dot) to separate the
source database and table name doesn't work. My searches of the 9.0.x docs
have missed finding this information.

TIA,

Rich

---------------------------------------------------------------------------

Aside from roles/users each database exists in isolation and so what you
describe cannot be done. The syntax you describe "<something>.<tablename>"
is reserved for "SCHEMA" usage within PostgreSQL.

If you really need to copy/clone a table to another database you will need
to pg_dump the table and then pg_restore it into the second database. An
alternative to is, somehow, simply dump "INSERT" statements for all the
records and then manually recreate the table in the second database and then
execute the INSERTS. I use third-party software that can dump the INSERTs
for me so I am unsure whether psql or pgAdmin can do the same. Replication
solutions work as well - depending on the complexity and frequency of your
need.

David J.