How do you use foreign data wrappers (FDWs) in PostgreSQL to query data from external sources like other databases or CSV files? — PostgreSQL Interview Question #50

PostgreSQL provides the Foreign Data Wrapper (FDW) feature for querying data from external sources like other databases or CSV files. This feature allows you to connect to external data sources and access them as if they are local PostgreSQL tables.

FDWs in PostgreSQL are implemented as extensions, so first, you need to make sure that the required extension is installed. The following command can be used to install the extension for different types of data sources:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE EXTENSION IF NOT EXISTS mysql_fdw;

Once the extension is installed, you can create a foreign server that points to the external data source to be queried. For example, to create a foreign server that connects to a MySQL database, you can use the following SQL statement:

CREATE SERVER my_mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'localhost', port '3306', dbname 'mydatabase');

Next, you need to create a user mapping that specifies the credentials for accessing the external data source. For example, to create a user mapping for a MySQL database, you can use the following SQL statement:

CREATE USER MAPPING FOR postgres
SERVER my_mysql_server
OPTIONS (username 'myuser', password 'mypassword');

Once you have created the foreign server and user mapping, you can create a foreign table that represents the external data source. For example, to create a foreign table that represents a table in a MySQL database, you can use the following SQL statement:

CREATE FOREIGN TABLE mytable (
    id integer,
    name character varying(50),
    age integer
)
SERVER my_mysql_server
OPTIONS (schema_name 'mydatabase', table_name 'mytable');

You can then use standard SQL queries to query the foreign table just like you would any other PostgreSQL table. For example, to select all the rows from the foreign table, you can use the following SQL statement:

SELECT * FROM mytable;

FDWs can also be used to query data from CSV files. To do this, you need to create a foreign table that represents the CSV file. For example, to create a foreign table that represents a CSV file, you can use the following SQL statement:

CREATE FOREIGN TABLE mycsv (
    id integer,
    name character varying(50),
    age integer
)
SERVER file_server
OPTIONS (filename '/path/to/myfile.csv', format 'csv');

SELECT * FROM mycsv;

FDWs provide a powerful mechanism for querying data from external sources in PostgreSQL.