How I can create a foreign table using a foreign data wrapper that references to a remote one with the same name?

Posted on

Question :

I have 2 databases one named logs that has the table:

id: serial
method: Varchar(10)
url: varchar(150)

I also have an another database named archiving that also has a table named http_log:

id: unsinged integer
method: Varchar(10)
url: varchar(150)

How I can create the foreign table archived_http_log so I can transfer data from the http_log to archived_http_log. I cannot have a table with the same name in my postgresql therefore I cannot have 2 tables named http_log.

What I want top achieve is via a single SQL script to transfer data from logs.http_log to archiving.http_log. So in a server to run:

INSERT INTO archived_http_log VALUES (SELECT * from http_log);

Answer :

A foreign table need not have the same name as the remote table:

CREATE FOREIGN TABLE remote_http_log (
   id integer NOT NULL,
   method text,
   url text
) SERVER whatever OPTIONS (table_name 'http_log');

Alternatively, you can have two tables with the same name in different schemas.

Leave a Reply

Your email address will not be published. Required fields are marked *