Menu
CoddyTech

PostgreSQL Interview Questions for Freshers

What PostgreSQL is, psql, schemas, identity columns, NULL, pattern matching, pagination and the commands that remove data.

11 questions
  1. What is PostgreSQL?

    PostgreSQL is a free, open source object-relational database. It has SQL, ACID transactions and MVCC, plus custom types, arrays, jsonb and functions in several languages. It ships under the permissive PostgreSQL License and is developed by a community, not one vendor.

    "Object-relational" means you can extend it: CREATE TYPE adds your own types, and CREATE EXTENSION adds packages such as PostGIS or pgvector.

  2. What is the difference between PostgreSQL and MySQL?

    Both are open source relational databases. PostgreSQL implements more of the SQL standard and has more built-in types. The differences that show up in code:

    PostgreSQLMySQL
    DDL in a transactionyes, can be rolled backno, DDL commits implicitly
    UpsertINSERT ... ON CONFLICTINSERT ... ON DUPLICATE KEY UPDATE
    RETURNINGyesno
    FULL OUTER JOINyesno
    JSONjson and indexable jsonbJSON
    LicensePostgreSQL LicenseGPL
  3. What is psql, and which psql commands should you know?

    psql is PostgreSQL's command-line client. You type SQL ending with ;, plus backslash meta-commands that psql runs itself.

    psql -h localhost -U postgres -d shop
    
    CommandDoes
    \llist databases
    \c shopconnect to shop
    \dtlist tables
    \d orderscolumns, indexes, constraints
    \dnlist schemas
    \dulist roles
    \qquit

    Meta-commands never reach the server, so a database driver in your application cannot run them. See the psql reference.

  4. What is the difference between a database and a schema in PostgreSQL, and what is search_path?

    A server holds databases, a database holds schemas, and a schema holds tables, views and functions. A connection uses one database and cannot query another directly (that takes postgres_fdw), but one query can join tables across schemas.

    search_path lists the schemas searched for an unqualified name; the default is "$user", public.

    CREATE SCHEMA sales;
    CREATE TABLE sales.orders (id int, amount numeric);
    SET search_path TO sales, public;
    SELECT * FROM orders;  -- finds sales.orders
    

    Since PostgreSQL 15, ordinary users cannot create objects in public unless granted CREATE on it. More in schemas.

  5. What is the difference between SERIAL and an identity column in PostgreSQL?

    Both fill an integer column from a sequence. SERIAL is PostgreSQL shorthand that creates a sequence and sets DEFAULT nextval(...); an identity column (GENERATED ... AS IDENTITY, since PostgreSQL 10) is the SQL standard form and the better choice for new tables.

    PostgreSQL
    -- fails on purpose: GENERATED ALWAYS rejects a hand-written id
    CREATE TABLE users (
      id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      name text
    );
    INSERT INTO users (name) VALUES ('Asha');
    INSERT INTO users (id, name) VALUES (5, 'Ben');

    The second insert fails with "cannot insert a non-DEFAULT value into column". OVERRIDING SYSTEM VALUE forces it; GENERATED BY DEFAULT accepts a given id the way SERIAL does. ALWAYS stops code from writing ids the sequence will later collide with.

  6. Does NULL = NULL return true in PostgreSQL, and what does COALESCE do?

    PostgreSQL
    SELECT COALESCE(NULL, NULL, 'none'), NULL = NULL;

    It returns none | NULL. COALESCE returns its first argument that is not NULL. NULL = NULL is NULL, not true: NULL means "unknown", and comparing with an unknown value gives unknown.

    Test with IS NULL, and compare two values that may be NULL with IS NOT DISTINCT FROM, which treats two NULLs as equal. WHERE col = NULL returns no rows and raises no error, because WHERE keeps only rows where the condition is true.

  7. Is LIKE case-sensitive in PostgreSQL, and what is ILIKE?

    PostgreSQL
    SELECT 'PostgreSQL' LIKE 'post%', 'PostgreSQL' ILIKE 'post%';

    It returns false | true. LIKE is case-sensitive in PostgreSQL, so 'post%' does not match PostgreSQL; ILIKE is the case-insensitive version, a PostgreSQL extension. In both, % matches any run of characters and _ exactly one. For regular expressions use ~, or ~* to ignore case.

    The follow-up is speed. A B-tree index helps only a pattern anchored at the start ('post%'). ILIKE and a leading % scan the table unless you add a trigram index from the pg_trgm extension.

  8. How do LIMIT, OFFSET and FETCH FIRST work in PostgreSQL?

    LIMIT n returns at most n rows and OFFSET m skips m rows first. FETCH FIRST n ROWS ONLY is the standard spelling, and since PostgreSQL 13 WITH TIES also keeps rows tied with the last one.

    PostgreSQL
    CREATE TABLE scores (name text, points int);
    INSERT INTO scores VALUES ('Asha', 90), ('Ben', 85), ('Chen', 85), ('Dev', 70);
    SELECT name, points FROM scores
    ORDER BY points DESC
    FETCH FIRST 2 ROWS WITH TIES;

    It returns three rows: Asha, then Ben and Chen, tied on 85. Without ORDER BY the rows you get are not defined. A large OFFSET is slow because skipped rows are still read.

  9. What is the difference between DELETE, TRUNCATE and DROP in PostgreSQL?

    DELETE removes chosen rows, TRUNCATE removes all rows at once, and DROP TABLE removes the table.

    DELETETRUNCATEDROP TABLE
    Row triggersfiredo not firedo not fire
    Big tablesslow, leaves dead rowsfastfast
    Can roll backyesyesyes

    The trap: all three are transactional in PostgreSQL, while MySQL and Oracle commit on TRUNCATE.

    PostgreSQL
    CREATE TABLE logs (id int);
    INSERT INTO logs VALUES (1), (2), (3);
    BEGIN;
    TRUNCATE logs;
    ROLLBACK;
    SELECT count(*) FROM logs;

    It returns 3. TRUNCATE takes an ACCESS EXCLUSIVE lock, blocking all access to the table until commit.

  10. What is the difference between a primary key and a unique key in PostgreSQL?

    A primary key is UNIQUE plus NOT NULL, and a table has at most one. A table can have many unique constraints, and their columns allow NULL. PostgreSQL builds a unique B-tree index for each, named users_pkey and users_email_key here.

    PostgreSQL
    CREATE TABLE users (id int PRIMARY KEY, email text UNIQUE);
    INSERT INTO users VALUES (1, NULL), (2, NULL), (3, 'asha@example.com');
    SELECT count(*) AS total_rows, count(email) AS emails FROM users;

    It returns 3 | 1: the two NULL emails are not duplicates, because NULL is not equal to NULL. A foreign key usually references the primary key, but any unique constraint works. More on keys in the DBMS questions.

  11. What happens when you concatenate a string with NULL in PostgreSQL?

    PostgreSQL
    SELECT 'Hi ' || NULL, concat('Hi ', NULL, 'there');

    It returns NULL | Hi there. || returns NULL when either side is NULL, so one missing middle name blanks the whole full name. concat() skips NULL arguments, and concat_ws(', ', a, b, c) skips them and puts the separator between the rest.

    Oracle developers trip on this, because Oracle's || treats NULL as an empty string. To keep ||, wrap the column that may be missing: first_name || ' ' || COALESCE(last_name, '').

Coddy programming languages illustration

Learn to code with Coddy

Get started