PostgreSQL Interview Questions

• ACID Compliance: Ensures atomicity, consistency, isolation, and durability in transactions.
• Support for relational features: Foreign keys, joins, views, triggers, and stored procedures.
• Full-text search: Allows efficient searching within text data.
• Advanced data types: Supports arrays, hstore, and JSONB for flexible data storage.
• Extensibility: Users can create their own functions, operators, and data types.
• MVCC (Multi-Version Concurrency Control): Enables concurrent transaction handling without locking issues.

  CREATE DATABASE mydatabase;
  

How to Create a New Table In PostgreSQL?

  CREATE TABLE employees (
      employee_id SERIAL PRIMARY KEY,
      name VARCHAR(100),
      position VARCHAR(100),
      salary NUMERIC,
      hire_date DATE
  );
  

• Readers do not block writers.
• Writers do not block readers.
• Transactions see a consistent snapshot of the data as of the start of the transaction.

Triggers in PostgreSQL are special procedures that automatically execute when certain events occur on a table (such as INSERT, UPDATE, or DELETE).

Example – Creating a Trigger:

  CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
  BEGIN
      NEW.updated_at = NOW();
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER update_timestamp
  BEFORE UPDATE ON employees
  FOR EACH ROW
  EXECUTE FUNCTION update_timestamp();
  

Foreign keys establish a relationship between two tables and ensure referential integrity. The foreign key value in one table must match a value in another table.

Example – Creating Foreign Keys:

  CREATE TABLE departments (
      department_id SERIAL PRIMARY KEY,
      department_name VARCHAR(100)
  );

  CREATE TABLE employees (
      employee_id SERIAL PRIMARY KEY,
      name VARCHAR(100),
      department_id INT,
      FOREIGN KEY (department_id) REFERENCES departments(department_id)
  );
  

A view in PostgreSQL is a virtual table based on the result of a SELECT query. It encapsulates complex queries and allows them to be reused as if they were tables.

Example – Creating a View:

  CREATE VIEW high_salary_employees AS
  SELECT name, salary
  FROM employees
  WHERE salary > 80000;
  

Exceptions in PL/pgSQL can be handled using the EXCEPTION block. This allows you to catch and manage errors in your stored procedures or functions.

Example – Handling a Duplicate Key Error:

  DO $$
  BEGIN
      INSERT INTO employees (employee_id, name) VALUES (1, 'John Doe');
  EXCEPTION
      WHEN unique_violation THEN
          RAISE NOTICE 'Duplicate key error!';
  END;
  $$;
  

Common Table Expressions (CTEs) are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They make queries easier to read and maintain.

Example – Using a CTE:

  WITH employee_salaries AS (
      SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id
  )
  SELECT * FROM employee_salaries;
  

Window functions perform calculations across a set of table rows related to the current row, often used for ranking, running totals, and moving averages.

Example – Ranking Employees by Salary:

  SELECT name, salary, 
      RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees;
  

PostgreSQL supports JSON and JSONB data types for storing and querying JSON data. JSONB is a binary format that is more efficient for indexing and searching.

Example – Creating a Table with JSONB:

  CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      details JSONB
  );

  INSERT INTO products (details) 
  VALUES ('{"name": "Laptop", "price": 1200}');
  

PostgreSQL supports a variety of operators for performing operations on data. These include:

• Arithmetic Operators: For mathematical calculations such as addition (+), subtraction (-), multiplication (*), division (/).
• Comparison Operators: For comparing values, including =, !=, <, >, <=, >=.
• Logical Operators: For combining conditions, such as AND, OR, and NOT.
• Bitwise Operators: For performing bit-level operations such as &, |, #, ~, <<, >>.

You can delete a PostgreSQL database in two ways:

• Using the SQL command DROP DATABASE database_name;
• Using the command-line utility dropdb database_name

Indexes in PostgreSQL are used to speed up data retrieval operations. They act like a lookup table for the database, allowing the query engine to find rows faster without scanning the entire table.

A Cluster index sorts the table's data rows based on the index key values, physically ordering them to improve performance for certain queries.

Database callback functions in PostgreSQL are known as triggers. They are automatically executed when a specified database event occurs (such as INSERT, UPDATE, or DELETE), enabling automated actions like logging, validation, or data synchronization.

Specifying data types for table columns provides several benefits:

• Consistency: Ensures all data in a column follows the same format.
• Compactness: Optimizes storage by using appropriate data types.
• Validation: Prevents invalid data from being inserted.
• Performance: Allows the database engine to optimize queries based on data type.

To update statistics in PostgreSQL, use the VACUUM ANALYZE command or the ANALYZE command. These update optimizer statistics, helping PostgreSQL make better query planning decisions.

The disadvantage of the DROP TABLE command is that it permanently removes both the table data and the table structure from the database. To store data again, the table must be re-created.

The CTID field identifies the specific physical location of a row in a table by indicating the block and offset positions.

The main commands for controlling transactions in PostgreSQL are:

• BEGIN TRANSACTION – Starts a transaction block.
• COMMIT – Saves all changes made during the transaction.
• ROLLBACK – Reverts all changes made during the transaction.

Some key differences between SQL and PostgreSQL include:

• In PostgreSQL, views are not updatable by default, unlike in SQL Server.
• SQL Server supports computed columns, whereas PostgreSQL does not.
• PostgreSQL allows dynamic actions without needing DLL creation, unlike SQL Server.
• PostgreSQL supports more advanced dynamic operations.

PostgreSQL ensures security by supporting SSL connections to encrypt client-server communication, protecting data from eavesdropping and tampering.

Atomicity ensures that all operations in a transaction are completed successfully. If any operation fails, the entire transaction is rolled back, maintaining database integrity.

Advantages of PostgreSQL include:

• Open-source and free to use.
• Strong community support.
• ACID compliance.
• Advanced indexing techniques.
• Full-text search support.
• Various replication methods.
• Extensibility through custom functions and data types.

Write-Ahead Logging (WAL) enhances reliability by recording changes to a log before applying them to the database. This ensures that changes can be recovered in case of a crash.

Some key administration tools for PostgreSQL are:

• psql – Command-line tool for SQL queries and database management.
• pgAdmin – Graphical interface for managing PostgreSQL databases.
• phpPgAdmin – Web-based PostgreSQL administration tool.