Foreign data wrappers concept in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a foreign server named 'myserver' using the 'postgres_fdw' wrapper.

PostgreSQL

CREATE SERVER [1] FOREIGN DATA WRAPPER postgres_fdw;

Drag options to blanks, or click blank then click option'

Amyserver

Bforeign_server

Cserver1

Dfdw_server

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong server name that doesn't match later references.

Confusing the foreign data wrapper name with the server name.

The server name is 'myserver' as specified in the task.

2fill in blank

medium

Complete the code to create a user mapping for user 'localuser' to connect to 'myserver' with password 'mypassword'.

PostgreSQL

CREATE USER MAPPING FOR localuser SERVER myserver OPTIONS (user '[1]', password 'mypassword');

Drag options to blanks, or click blank then click option'

Aadmin

Bpostgres

Clocaluser

Dremoteuser

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different username than the local user without reason.

Confusing the password field with the user field.

The user mapping must specify the remote user name that matches the local user, here 'localuser'.

3fill in blank

hard

Fix the error in the code to create a foreign table named 'foreign_table' that references 'remote_table' on 'myserver'.

PostgreSQL

CREATE FOREIGN TABLE foreign_table (id integer, name text) SERVER [1] OPTIONS (table_name 'remote_table');

Drag options to blanks, or click blank then click option'

Aforeign_server

Bpostgres_fdw

Cremote_server

Dmyserver

Attempts:

3 left

💡 Hint

Common Mistakes

Using the foreign data wrapper name instead of the server name.

Using a server name that was not created.

The SERVER clause must specify the foreign server name, which is 'myserver'.

4fill in blank

hard

Fill both blanks to grant SELECT permission on 'foreign_table' to user 'readonly'.

PostgreSQL

GRANT [1] ON [2] TO readonly;

Drag options to blanks, or click blank then click option'

ASELECT

BINSERT

Cforeign_table

Dremote_table

Attempts:

3 left

💡 Hint

Common Mistakes

Granting INSERT instead of SELECT.

Using the remote table name instead of the foreign table name.

You grant SELECT permission on the foreign table named 'foreign_table'.

5fill in blank

hard

Fill all three blanks to import foreign schema 'public' from server 'myserver' into local schema 'foreign_schema'.

PostgreSQL

IMPORT FOREIGN SCHEMA [1] FROM SERVER [2] INTO [3];

Drag options to blanks, or click blank then click option'

Apublic

Bmyserver

Cforeign_schema

Dlocal_schema

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up local and remote schema names.

Using wrong server name.

Omitting the INTO clause.

The command imports the 'public' schema from 'myserver' into the local schema 'foreign_schema'.

Practice

1. What is the main purpose of a Foreign Data Wrapper (FDW) in PostgreSQL?

easy

A. To speed up local query execution by caching results

B. To create backup copies of local tables automatically

C. To encrypt data stored in PostgreSQL tables

D. To access external data sources as if they were local tables

Solution

1. Step 1: Understand FDW functionality

   FDWs allow PostgreSQL to connect to external data sources and treat them like local tables.

2. Step 2: Compare options

   Options A, B, and D describe unrelated features: caching, backups, and encryption, not FDWs.

3. Final Answer:

   To access external data sources as if they were local tables -> Option D

4. Quick Check:

   FDW = Access external data like local tables [OK]

Hint: FDWs connect external data as local tables [OK]

Common Mistakes:

• Confusing FDW with backup or caching features
• Thinking FDW encrypts data
• Assuming FDW only works with local data

2. Which of the following is the correct SQL command to create a foreign server named myserver using the postgres_fdw wrapper?

easy

A. CREATE FOREIGN SERVER myserver FOREIGN DATA WRAPPER postgres_fdw;

B. CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw;

C. CREATE SERVER myserver USING FOREIGN DATA WRAPPER postgres_fdw;

D. CREATE FOREIGN SERVER myserver USING postgres_fdw;

Solution

1. Step 1: Recall correct syntax for creating foreign server

   The correct syntax is: CREATE FOREIGN SERVER server_name FOREIGN DATA WRAPPER wrapper_name;

2. Step 2: Match options to syntax

   CREATE FOREIGN SERVER myserver FOREIGN DATA WRAPPER postgres_fdw; matches exactly. Options A and C miss the FOREIGN keyword before SERVER. CREATE FOREIGN SERVER myserver USING postgres_fdw; uses USING incorrectly.

3. Final Answer:

   CREATE FOREIGN SERVER myserver FOREIGN DATA WRAPPER postgres_fdw; -> Option A

4. Quick Check:

   CREATE FOREIGN SERVER ... FOREIGN DATA WRAPPER ... [OK]

Hint: Use CREATE FOREIGN SERVER ... FOREIGN DATA WRAPPER ... [OK]

Common Mistakes:

• Omitting FOREIGN keyword before SERVER
• Using USING instead of FOREIGN DATA WRAPPER
• Mixing order of keywords

3. Given the following commands in PostgreSQL:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER foreign_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.10', dbname 'remotedb');
CREATE USER MAPPING FOR current_user SERVER foreign_srv OPTIONS (user 'remoteuser', password 'remotepass');
CREATE FOREIGN TABLE foreign_table (id INT, name TEXT) SERVER foreign_srv OPTIONS (schema_name 'public', table_name 'users');
SELECT * FROM foreign_table WHERE id = 1;

What will the SELECT query do?

medium

A. Retrieve rows from the remote table users in remotedb where id = 1

B. Retrieve rows from the local table named foreign_table

C. Cause a syntax error because foreign_table is not a local table

D. Return an empty result because no data is copied locally

Solution

1. Step 1: Understand foreign table mapping

   The foreign table foreign_table maps to the remote table users in the remotedb database on host 192.168.1.10.

2. Step 2: Analyze the SELECT query

   The SELECT queries the foreign_table, which fetches data from the remote users table where id = 1.

3. Final Answer:

   Retrieve rows from the remote table users in remotedb where id = 1 -> Option A

4. Quick Check:

   Foreign table SELECT fetches remote data [OK]

Hint: Foreign tables query remote data transparently [OK]

Common Mistakes:

• Thinking foreign_table is local data
• Expecting syntax error on foreign table
• Assuming data is copied locally automatically

4. You run this command:

CREATE FOREIGN TABLE foreign_table (id INT, name TEXT) SERVER foreign_srv OPTIONS (schema_name 'public', table_name 'users');

But get an error: ERROR: foreign server "foreign_srv" does not exist
What is the most likely cause?

medium

A. The user mapping for foreign_srv is missing

B. The foreign table syntax is incorrect and missing parentheses

C. The foreign server foreign_srv was not created before creating the foreign table

D. The remote table users does not exist in the remote database

Solution

1. Step 1: Interpret the error message

   The error says foreign server "foreign_srv" does not exist, meaning PostgreSQL cannot find that server definition.

2. Step 2: Check prerequisites for foreign table creation

   Before creating a foreign table, the foreign server must be created. If missing, this error occurs.

3. Final Answer:

   The foreign server foreign_srv was not created before creating the foreign table -> Option C

4. Quick Check:

   Foreign server must exist before foreign table [OK]

Hint: Create foreign server before foreign table [OK]

Common Mistakes:

• Ignoring the need to create foreign server first
• Assuming user mapping causes this error
• Thinking syntax error causes this message

5. You want to combine data from a local table orders and a remote table customers accessed via FDW named cust_fdw. Which approach correctly joins these tables in PostgreSQL?

hard

A. Create a view combining orders and customers on the remote server

B. Create foreign table for customers via cust_fdw, then run: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

C. Use dblink to fetch customers data inside a subquery, then join with orders

D. Copy remote customers data into a local table, then join locally

Solution

1. Step 1: Understand FDW usage for joining local and remote data

   FDW allows creating a foreign table for remote customers, so you can join it directly with local orders.

2. Step 2: Evaluate options for combining data

   Create foreign table for customers via cust_fdw, then run: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id; uses FDW foreign table and a direct join, which is correct and efficient. Copy remote customers data into a local table, then join locally copies data manually, less dynamic. Use dblink to fetch customers data inside a subquery, then join with orders uses dblink, more complex. Create a view combining orders and customers on the remote server creates a view on remote server, not accessible locally.

3. Final Answer:

   Create foreign table for customers via cust_fdw, then run: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id; -> Option B

4. Quick Check:

   FDW foreign table join local table directly [OK]

Hint: Create foreign table, then join like local tables [OK]

Common Mistakes:

• Trying to join without creating foreign table
• Copying remote data manually instead of FDW
• Using dblink unnecessarily for simple joins