20 PostgreSQL interview questions

Here are 20 PostgreSQL interview questions along with answers and examples:

1. What is PostgreSQL?

   PostgreSQL is an open-source, object-relational database management system (ORDBMS) known for its reliability, robustness, and support for advanced features like JSON support, full-text search, and ACID compliance.

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

   To create a new database in PostgreSQL, you can use the `CREATE DATABASE` command:

  

   CREATE DATABASE dbname;

3. What is the difference between CHAR and VARCHAR data types in PostgreSQL?

   CHAR stores fixed-length strings padded with spaces, while VARCHAR stores variable-length strings without padding.

4. How do you connect to a PostgreSQL database from the command line?

   You can use the psql command-line tool:

   

   psql -U username -d dbname -h host -p port

5. What is a schema in PostgreSQL?

   A schema in PostgreSQL is a namespace that contains named database objects such as tables, views, and functions.

6. How do you create a new table in PostgreSQL?

   You can use the CREATE TABLE command:

  

   CREATE TABLE tablename (

       column1 datatype,

       column2 datatype,

       ...

   );

7. What is an index in PostgreSQL?

   An index is a database object used to speed up data retrieval operations on a table by providing quick access to rows based on key values.

8. How do you create an index in PostgreSQL?

   You can use the CREATE INDEX command:

  

   CREATE INDEX idx_name ON tablename (column);

9. What are the different types of joins in PostgreSQL?

   PostgreSQL supports various types of joins including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

10. How do you perform a simple SELECT query in PostgreSQL?

   

    SELECT * FROM tablename;

11. What is a primary key in PostgreSQL?

    A primary key is a column or a group of columns that uniquely identifies each row in a table. It enforces entity integrity and ensures that no duplicate rows exist.

12. How do you add a new column to an existing table in PostgreSQL?

    You can use the ALTER TABLE command:

  

    ALTER TABLE tablename ADD COLUMN new_column datatype;

13. What is the difference between DELETE and TRUNCATE in PostgreSQL?

    DELETE removes rows from a table based on a condition, whereas TRUNCATE removes all rows from a table, but it's faster and doesn't generate WAL (Write-Ahead Logging) entries.

14. How do you update data in a table in PostgreSQL?

    You can use the UPDATE command:

   

    UPDATE tablename SET column = value WHERE condition;

15. What is a sequence in PostgreSQL and how do you create one?

    A sequence is a database object used to generate unique numeric values. You can create a sequence using the CREATE SEQUENCE command:

   

    CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;

16. How do you insert data into a table in PostgreSQL?

    You can use the INSERT INTO command:

   

    INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);

17. What is a subquery in PostgreSQL?

    A subquery is a query nested within another query. It's often used to retrieve data based on the result of another query.

18. How do you delete a database in PostgreSQL?

    You can use the DROP DATABASE command:

   

    DROP DATABASE dbname;

   

19. What is a view in PostgreSQL?

    A view is a virtual table based on the result of a SELECT query. It allows you to present data in a customized format without altering the underlying tables.

20. How do you drop a table in PostgreSQL?

    You can use the DROP TABLE command:

    

    DROP TABLE tablename;