Questions tagged [foreign-data-wrapper]

Foreign data wrappers are postgresql extensions that allow you to load data from a foreign resource into a database table

Foreign data wrappers are postgresql extensions that allow you to load data from a foreign resource (other database, other rdbm, nosql db, csv, ...) into a foreign table and address this foreign table in your queries as if were it a normal postgresql table

Reference:

147 questions
20
votes
5 answers

Keep PostgreSQL (FDW) Foreign Schema In-Sync

Using Postgres 9.6 with postgres_fdw extension, there any means to keep the tables present within the local schema in-sync with the tables on the remote database? I often need to add new tables to the wrapped schema on the remote database and would…
atdfairfax
  • 345
  • 2
  • 9
10
votes
2 answers

Do databases besides Postgres have features comparable to foreign data wrappers?

I'm very excited by several of the more recently-added Postgres features, such as foreign data wrappers. I'm not aware of any other RDBMS having this feature, but before I try to make the case to my main client that they should begin preferring…
iconoclast
  • 21,213
  • 15
  • 102
  • 138
9
votes
1 answer

How to use index in foreign table SELECT MAX(id) query in PostgreSQL?

I've got a foreign table (using postgresql_fdw foreign data wrapper) and I need to find maximum ID to copy all records. When I run SELECT MAX(id) FROM foreign_table it doesn't seem to be using index: Aggregate (cost=205.06..205.07 rows=1 width=4)…
8
votes
2 answers

How to connect to localhost with postgres_fdw?

The idea is that I have local database named northwind, and with postgres_fdw I want to connect with another database named test on localhost (remote connection simulation, for situations like when table in my database is updated, do something in…
6
votes
1 answer

updating table with Postgresql Foreign data wrapper

I created a foreign data wrapper table named t_user into mySchema. IMPORT FOREIGN SCHEMA public LIMIT TO (t_user) FROM SERVER myServer INTO mySchema; The myServer side t_user added some column, but the foreign table didn't update accordingly. I…
Zuojia Jiang
  • 65
  • 1
  • 4
6
votes
1 answer

Postgres: foreign key to foreign table

I have a foreign table, for example: CREATE FOREIGN TABLE film ( id varchar(40) NOT NULL, title varchar(40) NOT NULL, did integer NOT NULL, date_prod date, kind varchar(10), len …
smang
  • 1,187
  • 10
  • 23
5
votes
1 answer

How to force evaluation of subquery before joining / pushing down to foreign server

Suppose I want to query a big table with a few WHERE filters. I am using Postgres 11 and a foreign table; foreign data wrapper (FDW) is clickhouse_fdw. But I am also interested in a general solution. I can do so as follows: SELECT id,c1,c2,c3 from…
5
votes
1 answer

Azure PostgreSQL foreign data wrapper postgres_fdw

I am trying to use the PostgreSQL foreign data wrapper (postgres_fdw) extension from an Azure DB for PostgreSQL instance. The foreign data wrapper should be supported as mentioned in Azure DB for PostgreSQL:…
Namux
  • 305
  • 5
  • 17
5
votes
2 answers

Force postgres_fdw to use password?

I have two databases set up as part of the same Postgresql 9.4 database cluster, and I'm trying to access a table in one of them from the other using a postgres_fdw. I have it all set up and working as a superuser, however when I try to access the…
ibrewster
  • 3,482
  • 5
  • 42
  • 54
5
votes
3 answers

SQL: error when creating a foreign table that has an enum column

I'm creating a foreign table (foo_table) in database_a. foo_table lives in database_b. foo_table has an enum (bar_type) as one of its columns. Because this enum is in database_b, the creation of the foreign table fails in database_a. database_a…
shf8888
  • 255
  • 1
  • 9
4
votes
0 answers

Importing data from MS Access db to PostgreSQL db

I have a table in an MS Access db that I want to export to a PostgreSQL database. Every 2 or so months, I want to move all records from the Access table to a table in Postgres. Right now, I am using the Export to ODBC option in Access to do this,…
user5873424
  • 61
  • 1
  • 5
4
votes
2 answers

Can a foreign table have same name as a local table?

My problem is that I am using PostgreSQL database and want to import a table named vfm from other database, say B. But there's a table with the same name table in my current database (A). I'm getting an error in my query saying the relation exists…
4
votes
0 answers

Postgres_fdw LEFT JOIN with foreign table

I have this base query and execution time around 400ms: SELECT s.parcelno FROM export.scans s INNER JOIN plnum_customerno cp ON cp.pl_number = s.parcelno WHERE s.type_of_scan = '05' AND s.sdate::date BETWEEN '2017-10-01' AND…
4
votes
2 answers

Can the foreign data wrapper fdw_postgres handle the GEOMETRY data type of PostGIS?

I am accessing data from a different DB via fdw_postgres. It works well: CREATE FOREIGN TABLE fdw_table ( name TEXT, area double precision, use TEXT, geom GEOMETRY ) SERVER foreign_db OPTIONS (schema_name 'schema_A', table_name…
andschar
  • 3,504
  • 2
  • 27
  • 35
4
votes
1 answer

Does Citus support creating shards using mysql_fdw?

The Citus documentation for the master_get_table_metadata function states: part_storage_type: Type of storage used for the table. May be ‘t’ (standard table), ‘f’ (foreign table) or ‘c’ (columnar table). But I searched the entire documentation and…
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
1
2 3
9 10