MCQs on Extensions and Customizations | PostgreSQL Database

PostgreSQL offers a highly extensible framework, allowing users to enhance database functionality with extensions, foreign data wrappers (FDWs), and custom-written modules. Learn about managing and leveraging extensions effectively.

---

MCQs on Extensions and Customizations in PostgreSQL

1. Installing and Managing Extensions (CREATE EXTENSION)

1. What command is used to add an extension in PostgreSQL?
   • A) ADD EXTENSION
   • B) CREATE EXTENSION
   • C) INSTALL EXTENSION
   • D) SET EXTENSION
2. Extensions in PostgreSQL are typically stored in:
   • A) The system’s RAM
   • B) The database schema
   • C) An external storage device
   • D) pg_temp tablespace
3. Which statement is true about managing extensions in PostgreSQL?
   • A) Extensions are installed per user.
   • B) Extensions are installed globally across all databases.
   • C) Extensions are installed per database.
   • D) Extensions do not need database-specific installation.
4. What command is used to remove an extension from PostgreSQL?
   • A) DELETE EXTENSION
   • B) DROP EXTENSION
   • C) REMOVE EXTENSION
   • D) UNINSTALL EXTENSION
5. The command CREATE EXTENSION IF NOT EXISTS is used for:
   • A) Updating an existing extension
   • B) Creating an extension only if it doesn’t already exist
   • C) Deleting an unused extension
   • D) Checking extension compatibility
6. To view all installed extensions in a PostgreSQL database, you can use:
   • A) \dx in psql
   • B) SELECT * FROM extensions;
   • C) SHOW EXTENSIONS;
   • D) LIST ALL EXTENSIONS;
7. What permission is typically required to install an extension?
   • A) CONNECT privilege
   • B) DATABASE OWNER role
   • C) SUPERUSER role
   • D) TABLE OWNER role

---

2. Popular Extensions (PostGIS, pg_stat_statements, etc.)

8. What is the purpose of the PostGIS extension in PostgreSQL?
   • A) Enhances indexing functionality
   • B) Adds support for geospatial data
   • C) Improves query optimization
   • D) Provides real-time replication
9. Which of the following is NOT a feature of the PostGIS extension?
   • A) Spatial indexing
   • B) Raster support
   • C) Graph database functionality
   • D) Topological modeling
10. The pg_stat_statements extension is used for:
    • A) Managing database connections
    • B) Analyzing query performance and statistics
    • C) Optimizing table storage
    • D) Replicating data across clusters
11. Which extension adds support for full-text search in PostgreSQL?
    • A) pg_trgm
    • B) tsearch2
    • C) PostGIS
    • D) pg_stat_statements
12. The tablefunc extension provides functionality for:
    • A) Creating materialized views
    • B) Generating pivot tables
    • C) Managing table partitions
    • D) Monitoring table sizes
13. Which PostgreSQL extension is specifically designed for time-series data?
    • A) PostGIS
    • B) TimescaleDB
    • C) pg_stat_statements
    • D) dblink
14. What feature does the pgcrypto extension add?
    • A) Advanced indexing
    • B) Cryptographic functions for encryption and decryption
    • C) Multi-factor authentication
    • D) Schema versioning tools
15. The dblink extension in PostgreSQL allows:
    • A) Cross-database queries
    • B) Automatic database backups
    • C) Data visualization
    • D) Real-time analytics
16. Which extension provides additional JSON-related functions?
    • A) json_fdw
    • B) jsonb_tools
    • C) pg_partman
    • D) plperl
17. The pg_partman extension is primarily used for:
    • A) Managing and creating partitions in tables
    • B) Enhancing query performance
    • C) Enabling geospatial data analysis
    • D) Encrypting database records

---

3. Using Foreign Data Wrappers (FDWs)

18. What does an FDW in PostgreSQL enable?
    • A) Managing table partitions
    • B) Accessing external data sources as if they were local tables
    • C) Enabling full-text search
    • D) Backing up databases
19. Which command is used to create a foreign data wrapper?
    • A) CREATE FDW
    • B) CREATE FOREIGN DATA WRAPPER
    • C) INSTALL FOREIGN DATA WRAPPER
    • D) ADD FDW
20. The postgres_fdw extension allows PostgreSQL to:
    • A) Query another PostgreSQL database
    • B) Backup PostgreSQL databases automatically
    • C) Replicate data to cloud services
    • D) Enable full-text search across clusters
21. What is the role of the IMPORT FOREIGN SCHEMA command?
    • A) Copies tables to the current schema
    • B) Maps external tables to the local database schema
    • C) Deletes foreign schemas
    • D) Encrypts data in foreign schemas
22. Which FDW is commonly used to connect PostgreSQL with MySQL?
    • A) mysql_fdw
    • B) db2_fdw
    • C) mysql_wrapper
    • D) foreign_mysql
23. Which of the following is required to use a foreign data wrapper?
    • A) SUPERUSER privilege
    • B) Installation of the corresponding extension
    • C) A replicated cluster
    • D) A unique constraint on foreign keys
24. The file_fdw extension allows PostgreSQL to access:
    • A) JSON data directly
    • B) CSV and text files as foreign tables
    • C) Cloud-hosted tables
    • D) Real-time log data

---

4. Writing Custom Extensions

25. PostgreSQL custom extensions are usually written in:
    • A) Python
    • B) C
    • C) Java
    • D) SQL
26. What is the first step in creating a custom extension?
    • A) Install a default extension
    • B) Define the SQL functions and scripts
    • C) Write the extension in pg_config
    • D) Publish the extension to the pg_catalog schema
27. Custom PostgreSQL extensions are typically loaded using:
    • A) CREATE EXTENSION command
    • B) pg_restore utility
    • C) SQL scripts executed manually
    • D) pg_upgrade
28. To package an extension for PostgreSQL, it is common to use:
    • A) Makefile and SQL scripts
    • B) Bash scripts
    • C) Precompiled binaries
    • D) A cloud service
29. The .control file in a PostgreSQL extension specifies:
    • A) Data storage rules
    • B) Extension metadata and version information
    • C) User privileges for extension usage
    • D) Backup configurations
30. PostgreSQL extensions are stored in:
    • A) The system tablespace
    • B) The database catalog
    • C) The pg_extension system table
    • D) The local schema by default

---

Answer Key

Qno	Answer (Option with Text)
1	B) CREATE EXTENSION
2	B) The database schema
3	C) Extensions are installed per database
4	B) DROP EXTENSION
5	B) Creating an extension only if it doesn’t already exist
6	A) \dx in psql
7	C) SUPERUSER role
8	B) Adds support for geospatial data
9	C) Graph database functionality
10	B) Analyzing query performance and statistics
11	A) pg_trgm
12	B) Generating pivot tables
13	B) TimescaleDB
14	B) Cryptographic functions for encryption and decryption
15	A) Cross-database queries
16	B) jsonb_tools
17	A) Managing and creating partitions in tables
18	B) Accessing external data sources as if they were local tables
19	B) CREATE FOREIGN DATA WRAPPER
20	A) Query another PostgreSQL database
21	B) Maps external tables to the local database schema
22	A) mysql_fdw
23	B) Installation of the corresponding extension
24	B) CSV and text files as foreign tables
25	B) C
26	B) Define the SQL functions and scripts
27	A) CREATE EXTENSION command

Here are the answers for the last 3 questions:

Qno	Answer (Option with Text)
28	A) Makefile and SQL scripts
29	B) Extension metadata and version information
30	C) The pg_extension system table