Questions tagged [foreign-data]

Use this tag for questions related to the definition and usage of the SQL feature "External Data" (foreign data wrapper, foreign servers, foreign tables), as defined in the SQL standard, part 9, SQL/MED, or as implemented by the PostgreSQL database management system. If the question is about the PostgreSQL implementation, please also use the [postgresql] tag. Question that deal with the standard should also use the [sql] tag.

Filter by
Sorted by
Tagged with
1 vote
1 answer
414 views

Set a column value using the (remote) default when inserting a row into a foreign table

I have a database with a table whose primary key is a serial column, or a column with a locally-computed default value that prevents conflicts, for instance: CREATE TABLE foo ( foo_id serial ...
user2233709's user avatar
0 votes
1 answer
69 views

Refreshing development environment from backup that contains FDW in Postgres

Background: We recently added a foreign data wrapper from one of our Postgres (v14) databases to another Postgres (v14) database in a different system. It works fine in our production environment, but ...
David S's user avatar
  • 282
1 vote
0 answers
47 views

PostgreSQL: data retrieval from a private (authentication-based) RESTful web API using a foreign data wrapper

In some cases, one want to feed or populate some PostgreSQL database tables using data fetched from the web, e.g. through a RESTful API (serving JSON features). I've recently discovered foreign data ...
s.k's user avatar
  • 362
0 votes
1 answer
75 views

postgres_fdw: from where, is the resource utilized? remote or local?

I have an OLTP database and ETL jobs are running in the same database in the background. I was thinking of separating the OLTP and ETL instances so that resource utilization would be distributed. ...
Sajith P Shetty's user avatar
0 votes
1 answer
2k views

PostgreSQL Foreign Data Wrappers - Simultaneous queries won't finish

We're using foreign data wrappers in a database which points to another server (which is a read-only replica). We run scheduled jobs using python ( more on this here: https://github.com/sqlalchemy/...
K. Anye's user avatar
  • 13
1 vote
1 answer
2k views

How to re-create or update schema on Postgresql Foreign Data Wrappers?

I had two postgresql database server, each running on separate physical server. One is running version 13.3 (say server A) and the other 9.6 (say server B). I have installed postgresql's foreign data ...
Sapta's user avatar
  • 11
0 votes
1 answer
580 views

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: ...
Dimitrios Desyllas's user avatar
0 votes
1 answer
568 views

PostgreSQL : Refresh a materialized view base on a foreign table

I've got a foreign table from a distant csv host on a external webserver. I need to enhance this table so I create a materialized view to add some extra fields and join with internal data. Making a ...
Bad Wolf's user avatar
0 votes
2 answers
2k views

user mapping hide password

I would like to perform queries over different postgresql11-databases. Therefore I created a data wrapper and a user mapping as following: CREATE server 'test' foreign data wrapper postgres_fdw ...
oppia04's user avatar
  • 101
0 votes
1 answer
1k views

Listing active PostgreSQL foreign data wrappers

I have a few foreign data wrappers set up from my main PostgreSQL database to other databases. Is there a table I can query to list all of the FDWs? Unfortunately select * from information_schema....
rcv's user avatar
  • 103
-1 votes
1 answer
386 views

Postgres 12 Scalability using table Partitioning and Foreign Data Wrappers

I have looked through the archives and I cant find any discussions around the following topic. I have a fairly in depth question that I would appreciate some guidance with. Current environment ...
Francois's user avatar
0 votes
2 answers
175 views

Using IMPORT FOREIGN SCHEMA with Greenplum database

I have setup a PostgreSQL 12 database and am trying to connect to a Greenplum database in order to create proxy tables. I am able to connect to the Greenplum db, but I get an error when I try to use ...
Jason's user avatar
  • 1
1 vote
0 answers
117 views

Querying foreign table with SP-GiST index results in "ERROR: cache lookup failed for type 0" [duplicate]

I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on ...
João Ferreira's user avatar
0 votes
1 answer
764 views

Postgres special character rendering

I have an old Sql Server db with a ‘free entry’ field that is being accessed via a Postgres 10.6 foreign table. This field contains a lot of garbage characters (higher than Ascii 128) which Postgres ...
BigClyde's user avatar
0 votes
0 answers
408 views

Permission issue with a view built on top of foreign tables

I am consolidating data from several postgresql databases, all with the same schema, into a new one. To do that I mapped the tables I am interested in the new DB as foreign tables with postgres_fdw. ...
Cans's user avatar
  • 101
3 votes
2 answers
1k views

FDW Permissions error using MD5 encrypted password

When creating Foreign Data Wrappers in Postgres 9.6, if I use the following: CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'database', port '5432'); ...
John's user avatar
  • 33
3 votes
1 answer
400 views

Dynamic paths for postgres file_fdw (csv)

I've got a postgres database which relies on the use of foreign tables to support the loading of large data sets based on user-provided (system sanitized) tables. I'd use COPY, but the tables loaded ...
Adam Bethke's user avatar
3 votes
2 answers
2k views

ORDER BY too slow in Foreign Table using postgres_fdw

PostgreSQL v9.6, postgres_fdw Foreign table CREATE FOREIGN TABLE user_info ( id bigint , info jsonb ) SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'user_info_raw' ); -- ...
Luan Huynh's user avatar
  • 1,962
2 votes
1 answer
752 views

naming a column differently then its original one in foreign data wrapper of PostgreSQL

I have two databases. In each database I have an employee table which differ from each other in the number of columns and column names. Here is what the table looks in the first database: Emp_ID ...
Sayad Xiarkakh's user avatar
4 votes
2 answers
2k views

postgres_fdw slower then copy + scp + copy (~12 times)

foreign server 9.2 local server 9.5 table is 10GB data transfer performed on same network interface as foreign server works no indexes set on data old way: copy to - 2:36 scp - 08:17 copy from - ...
Vao Tsun's user avatar
  • 1,213
1 vote
0 answers
2k views

Can I use Foreign Data Wrapper for SQL Server in Windows Server 2012?

I use PostgreSQL and SQL Server. I want to get SQL Server data in PostgreSQL. I found PostgreSQL Foreign Data Wrapper. There are installation packs for CentOS and Ubuntu. However, I want to use FDW ...
hongsii's user avatar
  • 11
8 votes
1 answer
5k views

Create Materialized View from foreign table

I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server. What do I have to add to make the query access the foreign ...
andschar's user avatar
  • 237
1 vote
1 answer
7k views

SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once

Table A (users) id | name | ip 52433 Yashas 1.1.1.1 512 Admin 5.6.7.8 Table B (admin_record) id | admin | time | date | reason | action 52433 512 2312352 ...
Yashas's user avatar
  • 365
3 votes
1 answer
4k views

Postgres 9.5 foreign table inheritance not using indexes

In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
Dzmitry Nikitsin's user avatar
1 vote
1 answer
2k views

Foreign Data Wrappers & Windows Authentication

I been trying to get data from a SQL Server to use it on a PostgreSQL database. After a previous question I got into use FOREIGN DATA WRAPPERS, first I try with tds_fdw but after a successfull install ...
Nelson's user avatar
  • 1,689
0 votes
2 answers
85 views

Check attribute table referenced foreign key

I have two tables a and b like this: a: id, type b: a_id, ... There is a limitation for which the references of a in b can only be those which have type equal to a limited set of values. Is it ...
Mario's user avatar
  • 101
5 votes
1 answer
3k views

Rely on .pgpass in CREATE USER MAPPING

I am trying to create a script which creates a postgres-fdw connection between two postgres 9.4 databases. The script (which is checked in under version control), has been relying on pgpass to do ...
shaunc's user avatar
  • 151
6 votes
2 answers
2k views

manual execution of specific remote query with postgresql_fdw

In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g. > explain verbose select max(col1) from remote_tables.table1; ...
zwol's user avatar
  • 251
1 vote
1 answer
1k views

FDW, replication and CREATE SERVER - will it work?

We have two servers (master, slave) and we want to use the FDW. The two databases to be connected with the FDW are both on the master (and the slave of course). When we are to CREATE SERVER we would ...
adamo's user avatar
  • 145