Обсуждение: Question / Joel /BE

Поиск
Список
Период
Сортировка

Question / Joel /BE

От
Joël Francken
Дата:
Hellow

I'd like to know if it is possible to select data from a db on a server
and then to insert it on an other server ?
Is there somth like

SELECT * INTO backup FROM server.database.table;

???

Is there an alternate solution ?

Good day,

/joel francken
Belgium


RE: Question / Joel /BE

От
John Pagakis
Дата:
There is a SELECT INTO, however it is used for a different purpose than the
one I believe you require.

For your purposes,

INSERT INTO
    table_name( field1,
            field2,
            fieldn )
SELECT
    field1,
    field2,
    "default"
FROM
    other_table_name;

should do the trick.  The "default" is just to illustrate the point that you
are not limited to selecting fields from the source table, you can plug
literals.

SELECT INTO is a different beast.

SELECT
    field1,
    field2,
    fieldn
INTO
    target_table
FROM
    source_table;

will actually create a table named target_table with field1, field2 and
fieldn, and then populate it from source_table.  Beware!!

SELECT
    *
INTO
    target_table
FROM
    source_table;

will create an *ALMOST* exact duplicate of target table.  I say *ALMOST*
because the columns will be there in the target table, all the data will be
there.  None of the constraints will be picked up.  Keys will not have
indices - they will be columns just like any other in the table.
Referential integrity will be lost (i.e.., foreign keys will exist only as
fields - no relationship to the other tables).

Basically, you use SELECT * INTO to create a temporary holding place for
data while you do something destructive to the original table.

Hope this helps ....
_______________________________________
John Pagakis
DevelopOnline.com



-----Original Message-----
From: Joël Francken [mailto:joel.francken@agency.be]
Sent: Wednesday, May 16, 2001 5:03 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Question / Joel /BE


Hellow

I'd like to know if it is possible to select data from a db on a server
and then to insert it on an other server ?
Is there somth like

SELECT * INTO backup FROM server.database.table;

???

Is there an alternate solution ?

Good day,

/joel francken
Belgium


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Вложения

Re: Question / Joel /BE

От
Alexander Dederer
Дата:
Jo?l Francken wrote:
> I'd like to know if it is possible to select data from a db on a server
> and then to insert it on an other server ?
> Is there somth like
>
> SELECT * INTO backup FROM server.database.table;
>
> Is there an alternate solution ?

As I know it's in 7.1.1 not working.
Try Perl with 2 connections to 2 databases. And use CURSOR !!!