Re: Copy rows returned from a view into a table in a different db

On 5 November 2011 17:55, Russell Christopher <russch@hotmail.com> wrote:
> Complete PostgreSQL newb here.
>
>
>
> I have 7-8 views worth of data in db1 that I need to copy into tables with
>  matching schemae (schemas?) in a different database. The destination
> database may be the same instance of postgresql, or one on a different box
> altogether.
>
>
>
> I know 2-3 different ways to accomplish this with the databases I'm familiar
> with, but I'm helpless on this one. Can someone suggest some basic
> strategies for me?
>
>
>
> In a perfect world, I'd prefer not to have to do anything that feels too
> ETL-ish - I'd rather do some sort of SELECT FROM instance1.db1.viewname INTO
> instance2.db5.tablename then dump data out of the view as text file and
> reload into the destination table. I see postgresql has a COPY command, and
> that’ the sort of thing I want to avoid if I can.
>
>
>
> Since I don't know postgreSQL, so I don't really know what is within the
> realm of possibility, though.

PostgreSQL's databases are isolated from another another in a way
which means they have no visibility of other databases, so you cannot
reference a table in another database without using something like
dblink.

So your options are:

1) Use the COPY command to dump the data out of your view into a text
file. (http://www.postgresql.org/docs/current/static/sql-copy.html)

2) Create a real table with the same structure as the view, copy the
data from the view into the table, then use pg_dump to target just
that one table, then restore it in your destination database. (see
http://www.postgresql.org/docs/current/static/app-pgdump.html)

3) Use the dblink contrib module on the destination database/server to
pull in data from the target view. (see
http://www.postgresql.org/docs/current/static/dblink.html)

Hopefully when 9.2 comes out, we'll have the pgsql_fdw contrib module
which allows you to "mount" tables from other PostgreSQL databases
(local or remote) into your database, and you would be able to use
that, but unfortunately that's not available yet.

Regards

Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company