Foreign data wrappers concept in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Foreign Data Wrappers in PostgreSQL

What is the main purpose of using Foreign Data Wrappers (FDW) in PostgreSQL?

A To speed up local query execution by caching data internally

B To create backup copies of local tables automatically

C To allow PostgreSQL to query and manipulate data stored in external data sources as if they were local tables

D To encrypt data stored in PostgreSQL tables

💡 Hint

Think about how PostgreSQL can access data outside its own database.

Foreign Data Wrappers enable PostgreSQL to access and query data from external sources like other databases or files, treating them like local tables.

❓ query_result

intermediate

Querying Foreign Table Output

Given a foreign table named 'foreign_employees' linked to an external database, what will this query return?

SELECT name, department FROM foreign_employees WHERE department = 'Sales';

A All employees from the local PostgreSQL database regardless of department

B All employees from the external source who work in the Sales department

C An error because foreign tables cannot be filtered

D Only the names of employees without their departments

💡 Hint

Foreign tables behave like regular tables in queries.

Foreign tables can be queried with filters and return matching rows from the external source.

📝 Syntax

advanced

Correct Syntax to Create a Foreign Table

Which of the following SQL statements correctly creates a foreign table in PostgreSQL using a foreign data wrapper named 'mysql_fdw'?

A CREATE FOREIGN TABLE employees (id INT, name TEXT) SERVER mysql_server OPTIONS (dbname 'company', table_name 'employees');

B CREATE FOREIGN TABLE employees (id INT, name TEXT) USING mysql_fdw OPTIONS (dbname 'company', table_name 'employees');

C CREATE FOREIGN TABLE employees (id INT, name TEXT) SERVER mysql_fdw OPTIONS (database 'company', table 'employees');

D CREATE FOREIGN TABLE employees (id INT, name TEXT) SERVER mysql_server OPTIONS (database 'company', table_name 'employees');

💡 Hint

The SERVER clause must reference the foreign server name, and OPTIONS keys are case sensitive.

Option A uses the correct SERVER name and proper OPTIONS keys 'dbname' and 'table_name'.

❓ optimization

advanced

Improving Performance of Foreign Table Queries

Which approach can improve query performance when accessing large foreign tables in PostgreSQL?

A Create indexes on the foreign table in the external data source if supported

B Use only SELECT * queries without WHERE clauses

C Disable the foreign data wrapper to speed up queries

D Always fetch all rows without filtering to reduce overhead

💡 Hint

Think about how databases speed up queries on large tables.

Indexes on the external source help the FDW fetch only needed rows, improving performance.

🔧 Debug

expert

Diagnosing Foreign Table Query Error

You run this query on a foreign table:

SELECT * FROM foreign_customers WHERE customer_id = 123;

But you get the error:

ERROR:  foreign table "foreign_customers" does not exist

What is the most likely cause?

A The query syntax is invalid because WHERE clause cannot be used on foreign tables

B The foreign data wrapper is not installed on the PostgreSQL server

C The external data source is offline

D The foreign table 'foreign_customers' was not created or is misspelled in the current schema

💡 Hint

Check if the table exists in the database schema.

The error indicates PostgreSQL cannot find the foreign table, likely because it was never created or the name is wrong.