Question :
I have 2 databases one named logs
that has the table:
http_log:
id: serial
method: Varchar(10)
url: varchar(150)
I also have an another database named archiving
that also has a table named http_log
:
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.