PostgreSQL Beginner Quiz
30 questions on PostgreSQL.
Question 1: What is PostgreSQL?
- A. An operating system
- B. A NoSQL document database
- C. An advanced, open-source object-relational database management system (ORDBMS) β (correct answer)
- D. A frontend web framework
Explanation: PostgreSQL is a powerful, open-source relational database highly respected for its strict compliance to SQL standards, data integrity, and advanced feature set.
Question 2: Which command is used to create a new table in PostgreSQL?
- A. BUILD TABLE
- B. NEW TABLE
- C. MAKE TABLE
- D. CREATE TABLE β (correct answer)
Explanation: The standard SQL CREATE TABLE command is used to define a new table and its columns in PostgreSQL.
Question 3: Which PostgreSQL command line utility is used to interactively type SQL queries?
- A. pg_query
- B. psql β (correct answer)
- C. postgres_cli
- D. pg_admin
Explanation: psql is the official terminal-based front-end to PostgreSQL. It allows you to run queries interactively or execute scripts.
Question 4: What does the SERIAL data type do in PostgreSQL?
- A. Automatically creates an auto-incrementing integer column β (correct answer)
- B. Stores a sequence of strings
- C. Serializes data into JSON
- D. Creates a foreign key relationship
Explanation: SERIAL is a convenience type in PostgreSQL that creates an integer column and automatically assigns a sequence generator to it (similar to MySQL's AUTO_INCREMENT).
Question 5: Which PostgreSQL data type is best used for storing text of potentially unlimited length?
- A. VARCHAR(255)
- B. CHAR(100)
- C. TEXT β (correct answer)
- D. STRING
Explanation: Unlike some other databases where TEXT incurs a performance penalty, in PostgreSQL, TEXT is highly efficient and recommended for strings of arbitrary length.
Question 6: Which command deletes a table and all of its data in PostgreSQL?
- A. DELETE TABLE
- B. REMOVE TABLE
- C. DROP TABLE β (correct answer)
- D. TRUNCATE TABLE
Explanation: DROP TABLE removes the table structure entirely. TRUNCATE only removes the data but leaves the table structure intact.
Question 7: What does ACID stand for in the context of database transactions (a core feature of PostgreSQL)?
- A. Auto, Copy, Index, Delete
- B. Atomicity, Consistency, Isolation, Durability β (correct answer)
- C. Array, Character, Integer, Double
- D. Add, Change, Insert, Drop
Explanation: ACID properties guarantee that database transactions are processed reliably, ensuring data integrity even in the event of errors or power failures.
Question 8: Which command starts a transaction block in PostgreSQL?
- A. BEGIN
- B. START TRANSACTION
- C. INITIATE
- D. Both A and B β (correct answer)
Explanation: In PostgreSQL, BEGIN and START TRANSACTION are synonymous and open a transaction block.
Question 9: Which command saves the changes made during a transaction?
- A. SAVE
- B. DONE
- C. COMMIT β (correct answer)
- D. END
Explanation: COMMIT makes all the data changes performed within the transaction permanent.
Question 10: Which command undoes all changes made during the current transaction?
- A. REVERT
- B. ROLLBACK β (correct answer)
- C. UNDO
- D. ABORT
Explanation: If an error occurs, ROLLBACK undoes the entire transaction, leaving the database exactly as it was before the transaction started.
Question 11: Which PostgreSQL data type natively stores and allows querying of JSON objects?
- A. STRING
- B. XML
- C. JSONB β (correct answer)
- D. TEXT
Explanation: JSONB stores JSON data in a decomposed binary format. It is much faster to process than standard JSON and supports indexing for rapid NoSQL-style querying.
Question 12: How do you select only the first 5 records in a PostgreSQL query?
- A. SELECT TOP 5 * FROM table;
- B. SELECT * FROM table LIMIT 5; β (correct answer)
- C. SELECT * FROM table FETCH 5;
- D. SELECT * FROM table MAXIMUM 5;
Explanation: PostgreSQL uses the LIMIT clause (standardized in newer SQL versions) to restrict the number of rows returned.
Question 13: In PostgreSQL, how do you concatenate two strings (e.g., first_name and last_name)?
- A. first_name + last_name
- B. first_name & last_name
- C. first_name || last_name β (correct answer)
- D. CONCATENATE(first_name, last_name)
Explanation: The double pipe || is the standard SQL operator used in PostgreSQL to concatenate strings. (The CONCAT() function is also available).
Question 14: What is a "View" in PostgreSQL?
- A. A graphical user interface
- B. A backup of the database
- C. A virtual table based on the result-set of an SQL statement β (correct answer)
- D. A specific type of index
Explanation: Views do not store data themselves; they store a query. They simplify complex queries and can restrict user access to specific columns.
Question 15: What is the purpose of RETURNING in an INSERT or UPDATE statement in PostgreSQL?
- A. To automatically rollback if an error occurs
- B. To return the rows that were just inserted, updated, or deleted β (correct answer)
- C. To return to the previous database state
- D. To bounce the query back to the client
Explanation: Adding RETURNING * (or specific columns like RETURNING id) at the end of an INSERT saves you from having to run a separate SELECT query to find the newly generated ID.
Question 16: What does the UUID data type store?
- A. A universally unique identifier (a 128-bit value) β (correct answer)
- B. A User Interface ID
- C. A URL string
- D. An encrypted password
Explanation: UUIDs (like a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11) are excellent for distributed systems because they can be generated independently without collision.
Question 17: In PostgreSQL, what does the COALESCE function do?
- A. Combines two tables
- B. Returns the first non-null argument from a list β (correct answer)
- C. Concatenates strings
- D. Calculates the average of a column
Explanation: COALESCE(val1, val2, 'default') checks val1. If it's NULL, it checks val2. If that's NULL, it returns 'default'.
Question 18: Which type of JOIN returns all records when there is a match in either left or right table?
- A. INNER JOIN
- B. LEFT JOIN
- C. FULL OUTER JOIN β (correct answer)
- D. CROSS JOIN
Explanation: A FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. If there is no match for a row, the missing side will contain NULLs.
Question 19: What is a "Materialized View" in PostgreSQL?
- A. A view that is only visible to the database admin
- B. A physical table that actually stores the result of a query periodically, providing faster read performance β (correct answer)
- C. A view mapped to a JSON file
- D. A view that cannot be deleted
Explanation: Unlike standard Views which run the query every time they are accessed, Materialized Views cache the data physically. They must be refreshed manually using REFRESH MATERIALIZED VIEW.
Question 20: How can you safely delete a table only if it actually exists (to prevent an error)?
- A. DROP TABLE IF EXISTS table_name; β (correct answer)
- B. DELETE TABLE WHEN PRESENT table_name;
- C. SAFE DROP table_name;
- D. TRY DROP TABLE table_name;
Explanation: Adding IF EXISTS ensures the script continues to run without throwing a fatal error if the table is already deleted or never existed.
Question 21: What is pgAdmin?
- A. The core database engine
- B. A popular open-source graphical management tool for PostgreSQL β (correct answer)
- C. A backup utility script
- D. The superuser account name
Explanation: pgAdmin provides a powerful GUI to manage databases, write queries, and monitor server performance without using the command line.
Question 22: Which operator is used to extract a specific property from a JSONB column as text?
- A.
->> β (correct answer)
- B.
=>
- C.
.
- D.
::
Explanation: In PostgreSQL, data->>'name' extracts the JSON property "name" and casts the result as standard SQL text. -> extracts it as a JSONB object.
Question 23: What does the EXPLAIN ANALYZE command do?
- A. Analyzes the database for viruses
- B. Shows the query plan and actually executes the query to show real run times β (correct answer)
- C. Deletes slow queries
- D. Fixes bad table structures
Explanation: EXPLAIN only guesses the plan. EXPLAIN ANALYZE physically runs the query and reports exactly how many milliseconds each step took.
Question 24: What is the default superuser role in a standard PostgreSQL installation?
- A. root
- B. admin
- C. postgres β (correct answer)
- D. sysadmin
Explanation: By default, PostgreSQL creates a highly privileged superuser role named postgres.
Question 25: Which of the following creates an index on the "email" column of the "users" table?
- A. ADD INDEX ON users (email);
- B. CREATE INDEX idx_users_email ON users (email); β (correct answer)
- C. MAKE INDEX FOR email IN users;
- D. INDEX (users, email);
Explanation: CREATE INDEX is the standard command, followed by the index name, the ON keyword, the table name, and the column(s) in parentheses.
Question 26: What is a "CTE" (Common Table Expression)?
- A. A temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement, usually defined using the
WITH keyword β (correct answer)
- B. A permanent table for common data
- C. A function that returns a table
- D. An error message
Explanation: CTEs act like inline, temporary views that make extremely complex queries much easier to read and structure.
Question 27: In PostgreSQL, what is the syntax to cast a string '123' to an integer?
- A.
CAST_INT('123')
- B.
TO_INTEGER('123')
- C.
'123'::integer β (correct answer)
- D.
INT('123')
Explanation: PostgreSQL supports the standard CAST('123' AS integer), but the shorthand double-colon :: syntax is widely preferred and idiomatic.
Question 28: What is the difference between TIMESTAMP and TIMESTAMPTZ?
- A. TIMESTAMP stores time; TIMESTAMPTZ stores dates
- B. TIMESTAMPTZ stores the timestamp along with a timezone awareness, converting it to UTC internally β (correct answer)
- C. They are exactly the same
- D. TIMESTAMPTZ is only used for server logs
Explanation: It is a best practice to always use TIMESTAMPTZ (Timestamp with Time Zone) to avoid massive headaches when dealing with users in different global locations.
Question 29: What does the ON CONFLICT clause do (often called "Upsert")?
- A. It causes the database to crash if data matches
- B. It catches an insertion error (like a unique constraint violation) and allows you to update the existing row instead of failing β (correct answer)
- C. It deletes the conflicting row
- D. It skips the table
Explanation: INSERT INTO ... ON CONFLICT (id) DO UPDATE SET ... allows you to seamlessly insert a new row or update it if the ID already exists.
Question 30: What is the purpose of PostgreSQL "Triggers"?
- A. To automatically shoot an email to the admin
- B. To automatically execute a specified database function whenever a certain event (INSERT, UPDATE, DELETE) occurs on a table β (correct answer)
- C. To trigger database backups
- D. To drop tables if they get too large
Explanation: Triggers are highly useful for maintaining audit logs or automatically updating a updated_at timestamp column whenever a row is modified.