PostgreSQL Online Test - MCQ Questions

Welcome to the PostgreSQL Online Test! We will present 25 MCQs (Multiple-Choice Questions) to test your knowledge of the PostgreSQL database.

You can select the correct answer for each question and submit the test. You will get your online test score after finishing the complete test.

1. What is the purpose of the SERIAL datatype in PostgreSQL?

CREATE TABLE items (item_id SERIAL PRIMARY KEY, item_name VARCHAR(100));

a) To generate random numbers

b) To create a unique identifier for each row automatically

c) To store serial numbers provided by users

d) To encrypt data

2. Does PostgreSQL support transactions?

a) It does not support transactions.

b) It supports transactions and follows the ACID properties.

c) It supports transactions but does not ensure data integrity.

d) It handles transactions only on request.

3. What does the following query return in PostgreSQL?

SELECT 'Data' ILIKE 'data';

a) TRUE

b) FALSE

c) ERROR

d) NULL

4. Which PostgreSQL function is used to return the current date and time with the time zone?

a) CURRENT_DATE

b) NOW()

c) GETDATE()

d) TIMESTAMP

5. What will the following PostgreSQL command do?

VACUUM FULL;

a) Back up the database

b) Restore the database from backup

c) Reclaim storage by removing unnecessary data files

d) Compress and clean the database by reclaiming storage and defragmenting

6. How do you list all columns of a table in PostgreSQL?

\d+ tablename

a) The given query

b) SHOW COLUMNS FROM tablename;

c) LIST COLUMNS FROM tablename;

d) SELECT column_name FROM tablename;

7. What is the result of the following query?

SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-02 03:04:05');

a) The number of seconds since '1970-01-02 03:04:05'

b) The date '1970-01-02 03:04:05'

c) The number of seconds since '1970-01-01 00:00:00' UTC to the specified timestamp

d) An error because the syntax is incorrect

8. How do you create an index in PostgreSQL to improve the search performance of a column?

CREATE INDEX idx_columnname ON tablename (columnname);

a) The given query

b) UPDATE INDEX SET idx_columnname ON tablename (columnname);

c) ALTER TABLE tablename ADD INDEX (columnname);

d) None of the above

9. What is the function of the CTE or WITH clause in PostgreSQL?

a) It is used to write cleaner and more readable subqueries.

b) It deletes data in a controlled manner.

c) It is used to rename columns temporarily.

d) It updates multiple rows in a single query.

10. What does the COPY command do in PostgreSQL?

a) Copies data from one table to another

b) Copies data between a file and a table

c) Clones the entire database

d) Copies permissions from one user to another

11. How can you find the version of PostgreSQL you are running?

SELECT version();

a) The given query

b) SHOW version;

c) GET version;

d) SELECT pg_version();

12. What is the purpose of the ARRAY data type in PostgreSQL?

a) To store multiple values in a single column

b) To link to another table

c) To increase query performance

d) To check the integrity of the database

13. Which operator checks whether a value exists within a range of values in PostgreSQL?

a) BETWEEN

b) CONTAINS

c) WITHIN

d) SCOPE

14. What is a DOMAIN in PostgreSQL?

a) A method to define a network range

b) A database storage area

c) A data type with constraints, such as NOT NULL

d) A virtual hosting space

15. How do you perform a full-text search in PostgreSQL?

SELECT * FROM tablename WHERE columnname @@ to_tsquery('searchterm');

a) The given query

b) SELECT FULLTEXT('searchterm');

c) SELECT * FROM tablename WHERE SEARCH(columnname, 'searchterm');

d) None of the above

16. What does the OVERLAPS operator do in PostgreSQL?

a) It checks if two periods overlap each other.

b) It determines if one table covers another in terms of data.

c) It compares the schemas of two databases.

d) It ensures data is replicated over the network.

17. What does the following command do in PostgreSQL?

ALTER TABLE tablename ADD COLUMN newcolumn VARCHAR(100) DEFAULT 'N/A';

a) Adds a new column to tablename with a default value of 'N/A'

b) Changes the datatype of newcolumn to VARCHAR(100)

c) Deletes newcolumn from tablename

d) Renames newcolumn in tablename to VARCHAR(100)

18. How do you set a column to automatically generate UUIDs in PostgreSQL?

ALTER TABLE tablename ADD COLUMN uuidcolumn UUID DEFAULT uuid_generate_v4();

a) The given query

b) UPDATE tablename SET uuidcolumn = uuid_generate();

c) INSERT INTO tablename (uuidcolumn) VALUES (uuid_generate_v4());

d) CREATE TABLE tablename (uuidcolumn UUID PRIMARY KEY);

19. What is the standard method to begin a transaction in PostgreSQL?

a) START TRANSACTION;

b) BEGIN;

c) TRANSACTION START;

d) OPEN TRANSACTION;

20. Which command removes access privileges in PostgreSQL?

a) REVOKE

b) REMOVE

c) RESTRICT

d) REDUCE

21. How do you return the fifth through tenth rows of a result set in PostgreSQL?

SELECT * FROM tablename LIMIT 5 OFFSET 5;

a) The given query

b) SELECT * FROM tablename LIMIT 5, 10;

c) SELECT * FROM tablename FETCH FIRST 5 ROWS ONLY;

d) SELECT * FROM tablename OFFSET 5 LIMIT 5;

22. What is the purpose of the pg_dump tool in PostgreSQL?

a) To physically delete the database for security reasons

b) To check the integrity of database files

c) To back up a PostgreSQL database

d) To restore data from a backup

23. What is the equivalent of the GETDATE() function from SQL Server in PostgreSQL?

a) CURRENT_TIMESTAMP

b) NOW()

c) TODAY()

d) GETDATE() is also used in PostgreSQL

24. What is the PostgreSQL data type for storing large binary objects?

a) BLOB

b) BYTEA

c) LARGE OBJECT

d) BINARY

25. What is the function of the PL/pgSQL language in PostgreSQL?

a) It is used for writing triggers and stored procedures.

b) It is used for managing user permissions.

c) It is used for handling database replication.

d) It is used for performing automated database backups.