MCQs on Data Import and Export | PostgreSQL Database

Master PostgreSQL data import and export processes with this focused set of 30 MCQs. Explore topics like using COPY for bulk data loading, exporting files, importing various formats, and handling data encoding.

---

Using COPY for Bulk Data Loading

1. Which PostgreSQL command is used for bulk loading data into a table?
   • A) LOAD DATA
   • B) INSERT
   • C) COPY
   • D) BULK INSERT
2. What is required when using the COPY command in PostgreSQL?
   • A) The table must be empty
   • B) The file path must be accessible by the database server
   • C) Only CSV files are supported
   • D) Only text files are supported
3. Which option is used with the COPY command to specify the delimiter in a file?
   • A) DELIMITER
   • B) FORMAT
   • C) SEPARATOR
   • D) SPLIT
4. How can you specify that the first row in a file contains column headers when using COPY?
   • A) HEADER
   • B) IGNORE FIRST ROW
   • C) SKIP HEADER
   • D) OMIT HEADER
5. When importing data using COPY, what happens if the file contains invalid data?
   • A) The invalid rows are skipped automatically
   • B) The operation fails completely
   • C) Only valid rows are imported
   • D) Invalid data is replaced with NULL
6. What does the WITH clause in a COPY command allow you to do?
   • A) Specify column names
   • B) Define file format and options
   • C) Set the table structure
   • D) Validate data constraints

---

Exporting Data to Files

7. Which PostgreSQL command exports table data to a file?
   • A) EXPORT
   • B) SAVE AS
   • C) COPY
   • D) BACKUP
8. What is the default delimiter when exporting data using the COPY command?
   • A) Tab
   • B) Comma
   • C) Pipe
   • D) Space
9. How can you export data to a CSV file using the COPY command?
   • A) Use COPY ... TO 'file.csv' with FORMAT CSV
   • B) Use EXPORT ... TO 'file.csv'
   • C) Use SAVE ... TO 'file.csv'
   • D) Use DUMP ... TO 'file.csv'
10. Which permission is required to export data to a file using the COPY command?
    • A) SELECT on the table
    • B) INSERT on the table
    • C) Superuser privilege
    • D) DELETE on the table
11. How can you include column headers in an exported CSV file?
    • A) Use the WITH HEADER option
    • B) Use the HEADER option in the COPY command
    • C) Use the INCLUDE COLUMNS clause
    • D) Column headers are included by default
12. What file format is NOT directly supported by PostgreSQL for exporting data using COPY?
    • A) CSV
    • B) JSON
    • C) Text
    • D) XML

---

Importing Data from CSV/JSON/Other Formats

13. How do you import data from a CSV file into a PostgreSQL table?
    • A) Use the COPY ... FROM command
    • B) Use the IMPORT ... INTO command
    • C) Use the INSERT ... FROM FILE command
    • D) Use the LOAD DATA command
14. Which PostgreSQL function can import data from a JSON file?
    • A) jsonb_import()
    • B) pg_import_json()
    • C) json_populate_recordset()
    • D) load_json_file()
15. What must you ensure before importing data from a file?
    • A) The table has no constraints
    • B) The file encoding matches the database encoding
    • C) The table has an AUTO_INCREMENT column
    • D) The file is stored in the same directory as the database
16. What does the FORCE_NULL option in the COPY command do?
    • A) Converts empty strings to NULL
    • B) Replaces invalid data with NULL
    • C) Forces specific columns to accept NULL values
    • D) Replaces NULL values with default values
17. When importing data from a CSV file, how can you handle embedded delimiters in a column?
    • A) Use escape characters
    • B) Enclose the value in double quotes
    • C) Both A and B
    • D) Embedded delimiters cannot be handled
18. Which PostgreSQL function is used to parse JSON data into table records?
    • A) json_to_table()
    • B) jsonb_recordset()
    • C) json_populate_recordset()
    • D) json_import_table()

---

Handling Data Encoding

19. Which PostgreSQL parameter sets the default encoding for a database?
    • A) encoding_default
    • B) default_charset
    • C) client_encoding
    • D) server_encoding
20. What is the default encoding used by PostgreSQL when creating a new database?
    • A) UTF-8
    • B) ASCII
    • C) ISO-8859-1
    • D) UTF-16
21. How do you specify a file’s encoding in the COPY command?
    • A) ENCODING ‘utf-8’
    • B) CHARACTER SET ‘utf-8’
    • C) FILE FORMAT ‘utf-8’
    • D) CHARSET ‘utf-8’
22. What happens if the file encoding does not match the database encoding during data import?
    • A) Data is imported without issues
    • B) Errors occur for mismatched characters
    • C) PostgreSQL converts encoding automatically
    • D) The COPY command ignores encoding
23. Which PostgreSQL function checks the encoding of a string?
    • A) pg_check_encoding()
    • B) pg_convert()
    • C) pg_char_encoding()
    • D) encode_check()
24. How can you change the encoding of an existing PostgreSQL database?
    • A) Using ALTER DATABASE command
    • B) Using CONVERT DATABASE command
    • C) Using pg_dump and pg_restore with the desired encoding
    • D) Using ENCODE DATABASE command

---

Advanced Import/Export Scenarios

25. Which PostgreSQL tool is commonly used for importing and exporting large databases?
    • A) pg_dump
    • B) pg_import
    • C) COPY
    • D) psql
26. What does the pg_dump tool generate by default?
    • A) A SQL script for recreating the database
    • B) A binary backup file
    • C) A JSON export file
    • D) A plain text report
27. How can you export data from a PostgreSQL table directly into an Excel file?
    • A) Use COPY with FORMAT XLSX
    • B) Export data as CSV and open in Excel
    • C) Use EXPORT TO EXCEL command
    • D) PostgreSQL cannot export to Excel
28. Which option in the COPY command allows skipping rows during import?
    • A) HEADER
    • B) OFFSET
    • C) SKIP
    • D) ROWS
29. How can you compress exported data in PostgreSQL?
    • A) Use COPY with the COMPRESS option
    • B) Export data and compress manually using tools like gzip
    • C) Use EXPORT COMPRESSED command
    • D) PostgreSQL does not support compression
30. Which tool can be used to resolve encoding conflicts during data import/export?
    • A) psql
    • B) pg_encoding_resolver
    • C) pg_convert
    • D) pg_fix_encoding

Here are the answers to the MCQs:

Qno	Answer
1	C) COPY
2	B) The file path must be accessible by the database server
3	A) DELIMITER
4	A) HEADER
5	B) The operation fails completely
6	B) Define file format and options
7	C) COPY
8	A) Tab
9	A) Use COPY ... TO 'file.csv' with FORMAT CSV
10	C) Superuser privilege
11	B) Use the HEADER option in the COPY command
12	D) XML
13	A) Use the COPY ... FROM command
14	C) json_populate_recordset()
15	B) The file encoding matches the database encoding
16	A) Converts empty strings to NULL
17	C) Both A and B
18	C) json_populate_recordset()
19	D) server_encoding
20	A) UTF-8
21	A) ENCODING ‘utf-8’
22	B) Errors occur for mismatched characters
23	B) pg_convert()
24	C) Using pg_dump and pg_restore with the desired encoding
25	A) pg_dump
26	A) A SQL script for recreating the database
27	B) Export data as CSV and open in Excel
28	B) OFFSET
29	B) Export data and compress manually using tools like gzip
30	C) pg_convert