yoklainterview sim
ProgressHistory

Database Pg Types Constraints Interview Questions

75 verified Database Pg Types Constraints interview questions — solve with answers, learn from explanations, test yourself in a real simulation.

Try the real simulation →

Sample questions

Pg Types ConstraintsDifficulty 2
CREATE TABLE t (c1 char(10), c2 varchar(10));
INSERT INTO t VALUES ('abc', 'abc');
SELECT length(c1), length(c2) FROM t;

What does this return?
  • a3 and 3 — length() ignores char(n)'s trailing pad spaces✓
  • b10 and 3 — char(n) always reports its declared width
  • c10 and 10 — both columns report their declared width
  • d3 and 10 — varchar(n) reports its declared width, char(n) does not
Explanation:char(n) physically pads the stored value with trailing spaces up to n, but length() (like most string functions) treats trailing pad spaces as insignificant and strips them before counting. So length() returns 3 for both columns even though c1 is internally padded to 10 bytes.
Pg Types ConstraintsDifficulty 3
CREATE TABLE t (c1 char(10), c3 text);
INSERT INTO t VALUES ('abc', 'abc');
SELECT octet_length(c1), octet_length(c3) FROM t;

What does this return?
  • a3 and 3 — octet_length also strips char(n) padding
  • b10 and 3 — octet_length exposes char(n)'s real padded storage✓
  • c10 and 10 — both are padded to the same physical size
  • d3 and 10 — text is padded, char(n) is not
Explanation:Unlike length(), octet_length() reports the actual stored byte size without stripping trailing pad spaces. So c1 (char(10) holding 'abc') shows 10 bytes of physical storage, while c3 (text holding 'abc') shows 3 — confirming char(n)'s padding is real even though length() hides it.
Pg Types ConstraintsDifficulty 1
CREATE TABLE t (id int, email text);
INSERT INTO t VALUES (1, NULL), (2, NULL), (3, '[email protected]');
SELECT DISTINCT email FROM t;

How many rows does this return?
  • a3 rows — DISTINCT never removes NULL rows, since NULL is never equal to NULL
  • b1 row — DISTINCT collapses everything, including the non-NULL value, into a single row
  • c2 rows — DISTINCT treats all NULLs as one group, plus one row for '[email protected]'✓
  • dAn error — DISTINCT cannot be applied to a nullable column
Explanation:Even though NULL = NULL evaluates to NULL under normal comparison semantics, DISTINCT (like GROUP BY and UNIQUE) uses a special "not distinct from" grouping rule where all NULLs are treated as one group. So the two NULL emails collapse into a single output row, alongside the one row for '[email protected]' — 2 rows total.
Pg Types ConstraintsDifficulty 1
CREATE TABLE t (id int, score int);
INSERT INTO t VALUES (1,30),(2,NULL),(3,10),(4,NULL);
SELECT id, score FROM t ORDER BY score ASC;

Where do the NULL rows appear in the output, with no NULLS FIRST/LAST specified?
  • aBefore any non-NULL value, since PostgreSQL always sorts NULL as the smallest possible value
  • bAfter all non-NULL values, since PostgreSQL's default for ascending order is NULLS LAST✓
  • cIn their original insertion order, interleaved with the non-NULL rows
  • dThe query fails, since ORDER BY cannot sort a column containing NULL
Explanation:PostgreSQL's default NULL ordering depends on sort direction: ascending order defaults to NULLS LAST, descending order defaults to NULLS FIRST. So ORDER BY score ASC returns 10, 30, then the two NULLs — the opposite of what many engineers assume (that NULL always sorts as the lowest value).
Pg Types ConstraintsDifficulty 1
CREATE TABLE t (id int, score int);
INSERT INTO t VALUES (1,30),(2,NULL),(3,10),(4,NULL);
SELECT count(*) AS cnt_star, count(score) AS cnt_col FROM t;

What does this return?
  • acnt_star = 2, cnt_col = 2 — both count only rows where score is not NULL
  • bcnt_star = 4, cnt_col = 4 — both count every row regardless of NULLs
  • ccnt_star = 4, cnt_col = 2 — count(*) counts rows, count(column) skips NULLs✓
  • dcnt_star = 2, cnt_col = 4 — count(*) only counts rows with no NULL columns at all
Explanation:count(*) counts rows themselves, regardless of any column's content, so it returns 4 for this table. count(score), like every other aggregate applied to a specific column, ignores NULL values in that column — only the 2 rows with a non-NULL score are counted.
Pg Types ConstraintsDifficulty 2
CREATE TABLE t (id int, email text UNIQUE);
INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (2, NULL);

What happens on the second INSERT?
  • aIt fails with a unique constraint violation, since email already has a NULL row
  • bIt fails, because UNIQUE implies NOT NULL in PostgreSQL
  • cIt succeeds, but PostgreSQL silently converts the second NULL to an empty string
  • dIt succeeds, because a UNIQUE constraint treats each NULL as distinct from other NULLs✓
Explanation:PostgreSQL's UNIQUE constraint does not treat NULL as a comparable value for uniqueness purposes — each NULL is considered distinct from every other NULL, so a UNIQUE column can hold any number of NULL rows without conflict.

By specialization

MongoDB 450MySQL 450PostgreSQL 450Redis 450

By seniority

Database JuniorDatabase MidDatabase Senior

Other fields

BackendFrontendFullstackDevOps / CloudAI EngineerMobileSecurityQA / Test AutomationData EngineerGame DevelopmentML Engineer

Test yourself against the 2475-question Database bank.

Start interview