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'.