Menu
CoddyTech

PostgreSQL Data Types and JSONB Interview Questions

Numbers, text, dates and time zones, casts, arrays, JSON and JSONB, UUIDs, booleans and enums, with the trap in each.

11 questions
  1. What is the difference between numeric and double precision in PostgreSQL?

    PostgreSQL
    SELECT 0.1::float8 + 0.2::float8, 0.1::numeric + 0.2::numeric;

    It returns 0.30000000000000004 | 0.3. real and double precision (float8) are binary floating point: fast, but 0.1 has no exact binary form, so sums drift. numeric (same as decimal) stores exact decimal digits: slower, and the right type for money. numeric(10,2) allows 10 digits, 2 after the point.

    A related trap: 7 / 2 is integer division and returns 3; write 7 / 2.0 or cast one side to numeric.

  2. What is the difference between text, varchar(n) and char(n) in PostgreSQL?

    They run at the same speed; only the rules differ. text has no limit, varchar(n) rejects a longer value, and char(n) pads with spaces, then ignores them in comparisons and when it becomes text.

    PostgreSQL
    CREATE TABLE codes (fixed char(5), flexible varchar(5));
    INSERT INTO codes VALUES ('ab', 'ab');
    SELECT fixed || '|', flexible || '|', octet_length(fixed) FROM codes;

    It returns ab| | ab| | 5: the padding is stored (5 bytes) but dropped when fixed is concatenated. Use text, or varchar(n) when the limit is a real rule. Trap: an explicit cast cuts instead of failing, so 'PostgreSQL'::varchar(5) is Postg.

  3. What is the difference between timestamp and timestamptz in PostgreSQL?

    timestamptz (timestamp with time zone) stores an absolute instant, kept in UTC and shown in the session's TimeZone. timestamp (without time zone) stores a wall clock reading with no zone at all. Neither one stores the zone name, and both take 8 bytes.

    PostgreSQL
    SET TIME ZONE 'UTC';
    CREATE TABLE events (local_at timestamp, instant_at timestamptz);
    INSERT INTO events VALUES ('2026-11-17 10:00', '2026-11-17 10:00');
    SET TIME ZONE 'Asia/Kolkata';
    SELECT local_at::text, instant_at::text FROM events;

    It returns 2026-11-17 10:00:00 | 2026-11-17 15:30:00+05:30: the instant is shown in India time, while the wall clock reading does not move. Use timestamptz for the moment something happened. instant_at AT TIME ZONE 'America/New_York' turns it into a timestamp in that zone. See date and time types.

  4. What do you get when you subtract one date from another in PostgreSQL?

    PostgreSQL
    SELECT DATE '2024-03-01' - DATE '2024-02-01';

    It returns 29: date - date is an integer number of days, and 2024 is a leap year.

    Other date arithmetic: date + 7 returns a date; date + interval '1 month' returns a timestamp, and from January 31 lands on the last day of February; timestamp - timestamp returns an interval. Cut to a period with date_trunc('month', ts). now() is the start of the current transaction, so it does not change inside one; clock_timestamp() does.

  5. What is the difference between :: and CAST in PostgreSQL, and does a cast to integer round or truncate?

    PostgreSQL
    SELECT 3.7::int, CAST(2.5 AS integer), (-2.5)::int;

    It returns 4 | 3 | -3. x::type is PostgreSQL shorthand and CAST(x AS type) is the SQL standard; they do the same thing. A cast from numeric to an integer type rounds, with halves going away from zero, instead of truncating as C or Java do; use trunc() or floor() to cut. A double precision value rounds halves to even instead, so 2.5::float8::int is 2.

    A string that is not a number, such as 'abc'::int, raises an error rather than returning NULL. Since PostgreSQL 16, pg_input_is_valid('abc', 'integer') tests it first.

  6. Are PostgreSQL arrays 0-based or 1-based?

    PostgreSQL
    SELECT (ARRAY[10, 20, 30])[1], (ARRAY[10, 20, 30])[0];

    It returns 10 | NULL. Arrays start at 1, and an index outside the array returns NULL, not an error. Find rows containing a value with = ANY(col), or test containment with @>, which a GIN index serves.

    PostgreSQL
    CREATE TABLE posts (id int, tags text[]);
    INSERT INTO posts VALUES (1, '{sql,postgres}'), (2, ARRAY['python']), (3, '{postgres,python}');
    SELECT id FROM posts WHERE 'postgres' = ANY(tags) ORDER BY id;

    It returns posts 1 and 3. Drivers pass a list the same way: WHERE id = ANY($1). Data you join on often belongs in its own table, not an array.

  7. What is the difference between JSON and JSONB in PostgreSQL?

    json stores the input text exactly and reparses it on every read. jsonb stores a parsed binary form: faster to query, indexable with GIN, with containment (@>), but it drops whitespace, reorders keys and keeps only the last duplicate key.

    PostgreSQL
    SELECT '{"b": 1, "a": 2, "a": 3}'::json::text AS as_json,
           '{"b": 1, "a": 2, "a": 3}'::jsonb::text AS as_jsonb;

    It returns the input unchanged for json and {"a": 3, "b": 1} for jsonb. Use jsonb by default, and json only when you must keep the exact text, such as a signed webhook body. See JSON types.

  8. What do the JSONB operators ->, ->>, @> and ? do in PostgreSQL?

    -> returns a value as jsonb, ->> returns it as text, @> tests whether the left value contains the right one, and ? tests whether a top-level key exists.

    PostgreSQL
    CREATE TABLE users (id int, profile jsonb);
    INSERT INTO users VALUES
      (1, '{"plan": "pro", "city": "Pune", "age": 30}'),
      (2, '{"plan": "free", "age": 9}');
    SELECT id,
           (profile -> 'plan')::text AS arrow,
           profile ->> 'plan' AS double_arrow,
           profile @> '{"plan": "pro"}' AS is_pro,
           profile ? 'city' AS has_city,
           profile ->> 'age' > '10' AS text_compare
    FROM users ORDER BY id;

    -> keeps the JSON quotes ("pro"), ->> gives plain pro. The last column is the trap: it is true for both users, because ->> returns text and '9' > '10' compares characters. Cast first: (profile ->> 'age')::int > 10. A GIN index, USING gin (profile jsonb_path_ops), serves @> lookups. More in JSON functions.

  9. How do you generate a UUID in PostgreSQL?

    Call gen_random_uuid(), built in since PostgreSQL 13, which returns a random (version 4) uuid. Before 13 it came from the pgcrypto extension; uuid_generate_v4() comes from the uuid-ossp extension.

    PostgreSQL
    CREATE TABLE orders (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      total numeric
    );
    INSERT INTO orders (total) VALUES (10), (20);
    SELECT count(DISTINCT id) AS ids, max(pg_column_size(id)) AS bytes FROM orders;

    It returns 2 | 16: store a UUID as uuid, 16 bytes, not as 36 characters of text. The follow-up: random UUIDs scatter inserts across the primary key index, which hurts caching on large tables. Time-ordered UUIDs avoid that, and PostgreSQL 18 adds uuidv7() for them.

  10. How does the boolean type work in PostgreSQL?

    boolean holds true, false or NULL in one byte. As input it accepts strings such as 'true', 'yes', 'on', '1' and 't' (and their opposites); psql displays the values as t and f. The trap is NULL:

    PostgreSQL
    CREATE TABLE tasks (title text, done boolean);
    INSERT INTO tasks VALUES ('Write', true), ('Test', false), ('Ship', NULL);
    SELECT count(*) FILTER (WHERE NOT done) AS not_done,
           count(*) FILTER (WHERE done IS NOT TRUE) AS not_true
    FROM tasks;

    It returns 1 | 2: NOT NULL is still NULL, so WHERE NOT done skips Ship, while IS NOT TRUE counts it. Declare NOT NULL DEFAULT false when a flag has two states. Unlike MySQL, where BOOLEAN is tinyint(1), WHERE done = 1 is an error in PostgreSQL: there is no boolean = integer operator.

  11. What is an enum type in PostgreSQL, and when should you avoid it?

    An enum is a type with a fixed, ordered list of labels, made with CREATE TYPE ... AS ENUM. Values are checked on input, take 4 bytes, and sort in the order you declared, not alphabetically.

    PostgreSQL
    CREATE TYPE priority AS ENUM ('low', 'medium', 'high');
    CREATE TABLE tickets (title text, level priority);
    INSERT INTO tickets VALUES ('Typo', 'low'), ('Outage', 'high'), ('Slow page', 'medium');
    SELECT title, level FROM tickets ORDER BY level DESC;

    It returns Outage, Slow page, Typo. ALTER TYPE priority ADD VALUE 'urgent' adds a label, but none can be dropped: you build a new type and migrate the column. When the list changes often, a lookup table with a foreign key, or text with a CHECK, is easier.

Coddy programming languages illustration

Learn to code with Coddy

Get started