fbpx

Top 100 PostgreSQL Interview Questions and Answers

Top 100 PostgreSQL Interview Questions and Answers

Contents show

1. What is PostgreSQL?

Answer: PostgreSQL is an open-source relational database management system (RDBMS) known for extensibility and SQL compliance. Unlike many other RDBMSs, PostgreSQL allows users to create custom data types and query methods, making it quite powerful.


2. How do you create a new database in PostgreSQL?

Answer:
Use the createdb command-line utility or the SQL command CREATE DATABASE.

CREATE DATABASE dbname;

Reference: PostgreSQL Official Documentation


3. What are the different types of indexes available in PostgreSQL?

Answer:
PostgreSQL offers several index types: B-tree, Hash, GiST (Generalized Search Tree), SP-GiST (Space-partitioned Generalized Search Tree), GIN (Generalized Inverted Index), and BRIN (Block Range INdexes).


4. How do you retrieve the current date and time in PostgreSQL?

Answer:
Use the CURRENT_TIMESTAMP function.

SELECT CURRENT_TIMESTAMP;

Reference: PostgreSQL Official Documentation


5. How to list all tables in the current database?

Answer:
You can query the pg_tables system catalog or use the \dt command in psql.

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

6. Explain the difference between INNER JOIN and LEFT JOIN.

Answer:
INNER JOIN returns rows from both tables where the condition is met. LEFT JOIN returns all rows from the left table, and matching rows from the right table. Non-matching rows will have NULLs.


7. What is a VIEW in PostgreSQL?

Answer:
A VIEW is a virtual table representing the result of a SELECT query. It acts like a table but doesn’t store data physically.

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

8. How do you backup a PostgreSQL database?

Answer:
Use the pg_dump tool. It creates a backup in the form of SQL scripts or custom formats.

pg_dump dbname > backupfile.sql

Reference: PostgreSQL Official Documentation


9. How can you increase the performance of a PostgreSQL database?

Answer:
Some common techniques include:

  • Using appropriate indexes
  • Regularly vacuuming databases
  • Optimizing queries using EXPLAIN
  • Increasing buffer cache (shared_buffers)
  • Partitioning large tables

10. Explain the EXPLAIN command.

Answer:
EXPLAIN analyzes and shows the execution plan of a SQL statement, helping developers understand and optimize queries.

EXPLAIN SELECT * FROM table_name WHERE column = value;

Reference: PostgreSQL Official Documentation


11. How do you concatenate strings in PostgreSQL?

Answer:
Use the || operator or the concat() function.

SELECT 'Post' || 'greSQL';
SELECT concat('Post', 'greSQL');

12. How can you handle NULL values while sorting data in PostgreSQL?

Answer:
Use the ORDER BY clause with the NULLS FIRST or NULLS LAST option.

SELECT column_name FROM table_name ORDER BY column_name NULLS LAST;

13. What is the function to get the length of a string?

Answer:
Use the LENGTH() function.

SELECT LENGTH('PostgreSQL');

14. How do you update data in a table?

Answer:
Use the UPDATE statement with a SET clause.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

15. What is a transaction in PostgreSQL?

Answer:
A transaction is a sequence of SQL operations executed as a single unit. It ensures data integrity using the ACID properties (Atomicity, Consistency, Isolation, Durability).

BEGIN;
-- SQL statements
COMMIT;

Reference: PostgreSQL Official Documentation


16. How do you delete all rows from a table without deleting the table itself?

Answer:
Use the TRUNCATE statement.

TRUNCATE table_name;

17. How do you find duplicates in a table?

Answer:
Group by the columns and filter groups having count greater than 1.

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

18. What is the default port for PostgreSQL?

Answer:
The default port for PostgreSQL is 5432.


19. How do you find the version of PostgreSQL you are using?

Answer:
Use the version() function.

SELECT version();

20. What is a sequence in PostgreSQL?

Answer:
A sequence is a database object that generates a sequence of integers. Commonly used for auto-incrementing primary keys.

CREATE SEQUENCE seq_name;

Reference: PostgreSQL Official Documentation


21. How can you retrieve the first 5 rows from a table in PostgreSQL?

Answer:
Use the LIMIT clause.

SELECT * FROM table_name LIMIT 5;

22. How do you add a new column to an existing table?

Answer:
Use the ALTER TABLE command with ADD COLUMN.

ALTER TABLE table_name ADD COLUMN new_column_name data_type;

23. What is the difference between NOT NULL and UNIQUE constraints?

Answer:
NOT NULL ensures the column cannot have NULL values, while UNIQUE ensures all values in the column are distinct.


24. How can you remove a column from a table?

Answer:
Use the ALTER TABLE command with DROP COLUMN.

ALTER TABLE table_name DROP COLUMN column_name;

25. How do you create a primary key in PostgreSQL?

Answer:
Use the PRIMARY KEY constraint when creating a table or with ALTER TABLE.

CREATE TABLE table_name (column_name data_type PRIMARY KEY);

or

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

26. How can you find the total number of rows in a table?

Answer:
Use the COUNT function.

SELECT COUNT(*) FROM table_name;

27. How can you prevent SQL injections in PostgreSQL?

Answer:
Use parameterized queries or prepared statements. This ensures user input is always treated as data and not executable code.


28. What is the difference between CLUSTER and VACUUM in PostgreSQL?

Answer:
CLUSTER physically reorders a table based on an index to improve query performance. VACUUM recovers space from deleted/updated rows and can also analyze and optimize tables.


29. How do you create a user in PostgreSQL?

Answer:
Use the CREATE USER or CREATE ROLE command.

CREATE USER username WITH PASSWORD 'password';

30. How do you revoke privileges in PostgreSQL?

Answer:
Use the REVOKE command.

REVOKE privilege_type ON object_name FROM role_name;

31. What is the difference between a FUNCTION and a PROCEDURE in PostgreSQL?

Answer:
FUNCTION returns a value and can be used in expressions. PROCEDURE performs actions but doesn’t return values and is invoked using the CALL command.


32. How do you return multiple rows from a function in PostgreSQL?

Answer:
Use the SETOF datatype.

CREATE FUNCTION func_name() RETURNS SETOF table_name AS $$ ... $$ LANGUAGE language_name;

33. How can you clone a database in PostgreSQL?

Answer:
Use the createdb command with -T option or use the CREATE DATABASE with TEMPLATE clause.

createdb -T existing_db new_db;

or

CREATE DATABASE new_db WITH TEMPLATE existing_db;

34. How do you handle JSON data in PostgreSQL?

Answer:
PostgreSQL offers JSON and JSONB datatypes, and a variety of functions and operators for querying and manipulating JSON data.


35. How do you terminate an ongoing query in PostgreSQL?

Answer:
In psql, you can use Ctrl + C. Alternatively, you can use pg_terminate_backend function with the process ID.


36. How do you get a list of all active connections in PostgreSQL?

Answer:
Query the pg_stat_activity view.

SELECT * FROM pg_stat_activity;

37. What is a CTE in PostgreSQL?

Answer:
A Common Table Expression (CTE) provides a way to create temporary result sets using the WITH clause, which can be referred to within a main query.

WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name;

38. How do you extract the month from a date in PostgreSQL?

Answer:
Use the EXTRACT function.

SELECT EXTRACT(MONTH FROM date_column) FROM table_name;

39. How do you merge two tables in PostgreSQL?

Answer:
You can use UNION or UNION ALL to merge tables with similar structure.

SELECT * FROM table1 UNION SELECT * FROM table2;

40. What’s the difference between VARCHAR and TEXT data types?

Answer:
Both can store strings. VARCHAR has a limit, whereas TEXT doesn’t. Functionally, they’re often interchangeable in PostgreSQL.



41. How can you increase the value of a column by a specific amount?

Answer:
Use the UPDATE statement with arithmetic operations.

UPDATE table_name SET column_name = column_name + value WHERE condition;

42. How do you retrieve unique values from a column?

Answer:
Use the DISTINCT keyword.

SELECT DISTINCT column_name FROM table_name;

43. What is a FOREIGN KEY in PostgreSQL?

Answer:
A FOREIGN KEY is a constraint ensuring that values in a column match values in another table’s primary key column.


44. How do you enforce referential integrity in PostgreSQL?

Answer:
Use FOREIGN KEY constraints along with actions like CASCADE, SET NULL, or SET DEFAULT.


45. How can you change the data type of a column?

Answer:
Use the ALTER TABLE command with ALTER COLUMN.

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

46. How can you find the second highest value in a column?

Answer:
Use the ORDER BY clause with LIMIT and OFFSET.

SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1 OFFSET 1;

47. How do you rename a table in PostgreSQL?

Answer:
Use the ALTER TABLE command with RENAME.

ALTER TABLE old_name RENAME TO new_name;

48. What is a FULL OUTER JOIN?

Answer:
It returns all rows when there’s a match in either the left or right table. Non-matching rows will have NULLs.


49. How can you check if a column contains a specific substring?

Answer:
Use the LIKE or ILIKE (case-insensitive) operator.

SELECT * FROM table_name WHERE column_name LIKE '%substring%';

50. How do you calculate the aggregate sum of a column?

Answer:
Use the SUM() function.

SELECT SUM(column_name) FROM table_name WHERE condition;

51. How do you round a number in PostgreSQL?

Answer:
Use the ROUND() function.

SELECT ROUND(column_name) FROM table_name;

52. How can you convert a string to uppercase in PostgreSQL?

Answer:
Use the UPPER() function.

SELECT UPPER(column_name) FROM table_name;

53. What is tsvector in PostgreSQL?

Answer:
tsvector is a datatype used for text search. It represents a document in a format optimized for text search functions.


54. What is the difference between SERIAL and BIGSERIAL data types?

Answer:
Both are auto-incrementing integer columns. SERIAL is 4 bytes and goes up to 2^31-1, while BIGSERIAL is 8 bytes and goes up to 2^63-1.


55. How do you get the current user in PostgreSQL?

Answer:
Use the CURRENT_USER or SESSION_USER function.

SELECT CURRENT_USER;

56. How do you add a comment to a table or column?

Answer:
Use the COMMENT statement.

COMMENT ON TABLE table_name IS 'comment';
COMMENT ON COLUMN table_name.column_name IS 'comment';

57. How do you list all stored procedures in PostgreSQL?

Answer:
Query the pg_proc system catalog.

SELECT proname FROM pg_proc WHERE pronamespace = 'public';

58. What is a trigger in PostgreSQL?

Answer:
A trigger is a function invoked automatically before or after a change to data, based on specified events.


59. How do you get the size of a database?

Answer:
Use the pg_database_size() function.

SELECT pg_size_pretty(pg_database_size('dbname'));

60. What is the role of the pg_ctl utility?

Answer:
pg_ctl is a command-line utility to start, stop, restart, and manage the status of a PostgreSQL database server.



61. How can you retrieve the last n rows of a table?

Answer:
Use the ORDER BY clause with a LIMIT.

SELECT * FROM table_name ORDER BY column_name DESC LIMIT n;

62. What is pg_dumpall?

Answer:
pg_dumpall is a utility to backup an entire PostgreSQL cluster including all databases, roles, and tablespaces.


63. How can you change the owner of a table?

Answer:
Use the ALTER TABLE command with OWNER TO.

ALTER TABLE table_name OWNER TO new_owner;

64. What is the difference between NOW() and CURRENT_DATE?

Answer:
NOW() returns the current date and time, while CURRENT_DATE returns just the current date.


65. How can you add a unique constraint to a column?

Answer:
Use the ALTER TABLE command with ADD CONSTRAINT.

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

66. What is a savepoint in PostgreSQL?

Answer:
A savepoint is a point within a transaction to which you can later roll back. Useful for dividing transactions into smaller parts.


67. How can you see the list of all databases in PostgreSQL?

Answer:
In psql, use the \l or \list command. Alternatively, query pg_database.

SELECT datname FROM pg_database;

68. How can you perform case-insensitive search in a column?

Answer:
Use the ILIKE operator.

SELECT * FROM table_name WHERE column_name ILIKE '%value%';

69. How do you lock a table in PostgreSQL?

Answer:
Use the LOCK statement.

LOCK table_name [IN lock_mode MODE];

70. How can you check if a table exists in PostgreSQL?

Answer:
Query the pg_tables system catalog.

SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'table_name');

71. What is a materialized view?

Answer:
A materialized view is a stored or cached view which contains results of a query. Unlike views, its content is static until refreshed.


72. How do you refresh a materialized view?

Answer:
Use the REFRESH MATERIALIZED VIEW statement.

REFRESH MATERIALIZED VIEW view_name;

73. What’s the difference between ROLLBACK and COMMIT?

Answer:
ROLLBACK undoes all changes made in the current transaction, while COMMIT saves all transaction changes permanently.


74. How can you find tables without indexes?

Answer:
Query the pg_stat_user_tables and pg_indexes.

SELECT tablename FROM pg_stat_user_tables WHERE schemaname = 'public' AND tablename NOT IN (SELECT tablename FROM pg_indexes WHERE schemaname = 'public');

75. How do you run SQL scripts in PostgreSQL?

Answer:
Use the psql command followed by the database name and -f flag.

psql dbname -f script.sql;

76. How do you subtract two dates to get the number of days?

Answer:
Subtract the dates directly.

SELECT end_date - start_date FROM table_name;

77. What are arrays in PostgreSQL?

Answer:
Arrays are one-dimensional lists that can store elements of a specific data type, and can be used as a column data type.


78. How can you find the length of an array?

Answer:
Use the array_length() function.

SELECT array_length(array_column, 1) FROM table_name;

79. How do you create an index on a JSONB column’s property?

Answer:
Use the GIN index on the jsonb column.

CREATE INDEX idx_name ON table_name USING GIN (json_column->'property');

80. How do you insert a row returning the inserted ID?

Answer:
Use the RETURNING clause with INSERT.

INSERT INTO table_name (column1) VALUES (value1) RETURNING id;


81. How can you convert a string to lowercase in PostgreSQL?

Answer:
Use the LOWER() function.

SELECT LOWER(column_name) FROM table_name;

82. How do you remove leading and trailing spaces from a string?

Answer:
Use the TRIM() function.

SELECT TRIM(column_name) FROM table_name;

83. What is partitioning in PostgreSQL?

Answer:
Partitioning divides a table into smaller, more manageable pieces called partitions based on certain criteria, improving query performance.


84. How do you check for NULL values in a column?

Answer:
Use the IS NULL or IS NOT NULL condition.

SELECT * FROM table_name WHERE column_name IS NULL;

85. How can you concatenate multiple rows into a single text string?

Answer:
Use the STRING_AGG() function.

SELECT STRING_AGG(column_name, ',') FROM table_name;

86. What is the purpose of the pg_stat_activity view?

Answer:
It provides a snapshot of current activities in the database, like active queries, connections, and more.


87. How can you generate a series of numbers in PostgreSQL?

Answer:
Use the generate_series() function.

SELECT * FROM generate_series(1,10);

88. How do you handle time zones in timestamp data?

Answer:
Use the timestamp with time zone datatype (timestamptz) which adjusts for time zone differences.


89. What is the difference between CHAR, VARCHAR, and TEXT?

Answer:
CHAR(n) is fixed-length with space padded. VARCHAR(n) is variable-length up to a limit. TEXT is variable-length without a specific limit.


90. How do you define a default value for a column?

Answer:
Use the DEFAULT keyword when creating or altering a table.

CREATE TABLE table_name (column_name data_type DEFAULT 'default_value');

91. What is a cursor in PostgreSQL?

Answer:
A cursor allows you to retrieve a few rows at a time out of the result set of a larger query, useful for handling large datasets.


92. How do you escape special characters in patterns?

Answer:
Use the E prefix for strings. For example, to search for \n, use E'\\n'.


93. How can you drop all tables in a PostgreSQL database?

Answer:
One approach is using pg_tables and dropping each table.

DO $$ DECLARE
    table_name text;
BEGIN
    FOR table_name IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public')
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || table_name || ' CASCADE';
    END LOOP;
END $$;

94. How do you calculate the difference between two timestamps?

Answer:
Subtract them directly.

SELECT timestamp1 - timestamp2 FROM table_name;

95. How do you change the owner of a database?

Answer:
Use the ALTER DATABASE command.

ALTER DATABASE dbname OWNER TO new_owner;

96. How do you check the constraints defined on a table?

Answer:
Query the information_schema.table_constraints.

SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_name = 'table_name';

97. How can you increase the value of a sequence?

Answer:
Use the ALTER SEQUENCE command.

ALTER SEQUENCE sequence_name INCREMENT BY value;

98. How can you reset the value of a sequence?

Answer:
Use the ALTER SEQUENCE command with RESTART.

ALTER SEQUENCE sequence_name RESTART WITH value;

99. What is a self-join and when would you use it?

Answer:
A self-join relates a table to itself. It’s useful when comparing rows within the same table or finding related data.


100. How do you enable logging of all SQL statements?

Answer:
Modify the postgresql.conf file and set logging_statement = 'all'.