Click here to Skip to main content
15,400,390 members
Articles / Database Development / PostgreSQL
Posted 9 May 2020

Tagged as


2 bookmarked

Postgres Foreign Table Example

Rate me:
Please Sign up or sign in to vote.
4.67/5 (3 votes)
9 May 2020CPOL2 min read
Snippet to get started with foreign tables
This is a step by step guide to configure and use foreign tables for the first time.


I have been working with Postgres for over 10 years and I must say that I am very satisfied with its performance, reliability and features that make developers' lives easier.

One of those features I love is the support for Foreign tables, a very useful feature that had been there for a few years before I found it a few months ago when I was surfing the web.


Postgres official documentation describes how to create a foreign table but it doesn't show you how to make it work step by step, so I decided to write this post here.

Environment Configuration

For this example, we need two Postgres servers. I am going to use Docker to create them in my PC so I can get rid of them easily once I finish this post.

This step is completely optional since I am going to assume that you already have the servers, but if you are curious and have never worked with it, feel free to go here and here.

You should be able to create the servers with a terminal and these two commands, one for each server.

docker run --name postgres_1 -e POSTGRES_PASSWORD=postgres_1_pw -d postgres
docker run --name postgres_2 -e POSTGRES_PASSWORD=postgres_2_pw -d postgres

You know everything went well if you run docker ps and you see something like:

65b706348544  postgres  "docker-entrypoint.s…" 2 minutes ago Up 2 minutes  5432/tcp  postgres_2
16b1dcfd09fb  postgres  "docker-entrypoint.s…" 3 minutes ago Up 3 minutes  5432/tcp  postgres_1

Database Setup

Before we get our hands on the foreign tables, we will need a very basic structure in both servers illustrated below:

Image 1

Once this is completed, we will need a sample table called users with some random data on database_2 located in postgres_2.

Image 2

To read and write the users table from server 1 (postgres_1), we need to use the postgres_fdw extension that will allow us to access data from remote tables, create an origin source of data and, of course, we will need some credentials to access the data in the remote server.

Image 3

Once you have imported the users table from postgres_2 into postgres_1, the users table should be accessible for read or write operations.

Image 4

Putting It All Together

Script for Remote Server (postgres_2)

    id serial primary key,
    name character varying NOT NULL,
    email character varying NOT NULL UNIQUE,
    bio text

INSERT INTO users (name, email, bio) VALUES
('Angelika Bartlett', '', 'Lorem ipsum dolor sit amet, 
  consectetur adipisicing elit'),
('Roger Scott', '', 
 'sed do eiusmod tempor incididunt ut labore et dolore magna aliqua'),
('Malia Murray', '', 'Ut enim ad minim veniam, 
  quis nostrud exercitation ullamco laboris');

Script for Local Server (postgres_1)

CREATE EXTENSION postgres_fdw;

CREATE SERVER postgres_2
 OPTIONS (dbname 'database_2', host 'postgres_2', port '5432');

  SERVER postgres_2
  OPTIONS (user 'postgres', password 'postgres_2_pw');

IMPORT FOREIGN SCHEMA "public" limit to (users) FROM SERVER postgres_2 INTO public;

FROM users;

UPDATE users
SET name = 'Ing. Malia Murray'
WHERE id = 3;

FROM users
WHERE id = 3;


I hope this snippet can save a few minutes if you ever need to work with Postgres and foreign tables.

Thanks for reading!


  • 9th May, 2020: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Carlos Luis Rojas Aragonés
Chief Technology Officer
Costa Rica Costa Rica
CTO and Co-founder of Golabs, a software development company located in Costa Rica. I am very passionate about my job and I really enjoy building things with software and make things happening. I am also a professor at Universidad Técnica Nacional because I believe that knowledge should be shared, I really enjoy seeing people grow from students at the university to great professionals and entrepreneurs.

Comments and Discussions

QuestionWell explained, my vote of 5 Pin
Luis Perez Garcia12-May-20 1:25
MemberLuis Perez Garcia12-May-20 1:25 
AnswerRe: Well explained, my vote of 5 Pin
Carlos Luis Rojas Aragonés12-May-20 6:49
professionalCarlos Luis Rojas Aragonés12-May-20 6:49 
SuggestionPostgreSQL portable Pin
RickZeeland9-May-20 9:45
mveRickZeeland9-May-20 9:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.