2

I have two instances of postgreSQL , one as the data source (port 5433) and the other as data access layer(host of foreign data wrappers on port 5432).

I want to import the schema of postgreSQL table (Dzlog) so that I dont have to map each column in "CREATE FOREIGN TABLE". Therefore, I have the following command:

engine = create_engine('pgfdw://postgres:test123@localhost:5432/mydb')
connection = engine.connect()
connection.execute("""CREATE SERVER pgfdw_server_1 FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'sorucedb', port '5433', host 'localhost');""")

connection.execute("""CREATE USER MAPPING FOR postgres SERVER `pgfdw_server_1 
OPTIONS (user 'postgres',password 'password')""")`

connection.execute ("""CREATE SCHEMA imported;""")
connection.execute("""IMPORT FOREIGN SCHEMA public LIMIT TO(Dzlog) FROM SERVER pgfdw_server_1 INTO imported;""")

Now, how can I create the foreign table with this imported schema?

Update:

So if by importing foreign schema (Dzlog) the foreign table is already created, I use the following select statement:

connection.execute("""SELECT sheetId FROM importing.Dzlog WHERE dz(30)=-5;""")

ProgrammingError: (psycopg2.ProgrammingError) relation "Dzlog " does not exist

In psql also the relation does not exist

SillyPerson
  • 589
  • 2
  • 7
  • 30
  • 1
    The `IMPORT FOREIGN SCHEMA` already creates foreign tables. If you want to create more, just add their names to `LIMIT TO`. – pozs Mar 29 '17 at 13:29
  • No it did not. it tells me that the relation does not exist, so the name of the foreign table that I can query is Dzlog? I think the LIMIT TO defines that from which table in the source data source we want to import the schema. – SillyPerson Mar 29 '17 at 13:38
  • 1
    When did it say that? If your `IMPORT FOREIGN SCHEMA` statement executed successfully, then a `Dzlog` named (foreign) table should have been created in your `imported` schema. – pozs Mar 29 '17 at 13:40
  • then maybe my select query is not correct. I update the question with my select query. – SillyPerson Mar 29 '17 at 13:49
  • Maybe the problem is that my foreign server (pgfdw_server_1) was created on the public schema ? any idea? – SillyPerson Mar 29 '17 at 13:55
  • No I have also user mapping. I have other foreign tables in my postgres and I can query them successfully. I added it to the question – SillyPerson Mar 29 '17 at 13:59
  • 2
    That space in `relation "Dzlog "` is very suspicious. If that's not the cause make sure that the `IMPORT FOREIGN SCHEMA` runs with success (without any error). You can also double check the name of the table (f.ex. upper-case `D` matters when you use quoting, but will be lower-cased when you don't use quoting.) – pozs Mar 29 '17 at 14:02
  • what happens when you run the select in `psql`?.. – Vao Tsun Mar 29 '17 at 14:14
  • in psql also: It tells me that the relation "importing.dzlog" does not exist. i changed Dzlog to lowercase though. – SillyPerson Mar 29 '17 at 14:23
  • I solved the problem by importing the foreign schema into my public schema instead of "imported", do you know what was the original problem of this? – SillyPerson Mar 30 '17 at 07:10
  • 1
    You have imported foreign schema into `imported`, but you query `importing`. Those are 2 different schema names. – Łukasz Kamiński Mar 30 '17 at 11:40

0 Answers0