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



  • 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);
    


  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2