Обсуждение: 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
On Tue, 15 Nov 2011, David Johnston wrote: > 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. David, This was pointed out to me. What I did was display the schema for the table, then use it to create a similar table in the new database. Thanks, Rich
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rich Shepard Sent: Tuesday, November 15, 2011 11:42 AM To: pgsql-general@postgresql.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.
On 11/15/11 8:42 AM, Rich Shepard wrote: > 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. use Schemas instead of databases if this is what you need. Combined with search_path, this will get you the exact results you're looking for. in fact, the default search_path is $USER,public, so it will look in a schema named after the current user before it looks in the default schema... -- john r pierce N 37, W 122 santa cruz ca mid-left coast