All quizzesEasy
Keys, Types & Constraints — Series 3
Preview — 3 of 10 questions
What's the practical difference between created_at TIMESTAMP and created_at TIMESTAMPTZ in PostgreSQL?
ATIMESTAMPTZ stores the time zone name (like 'America/New_York') alongside the value; TIMESTAMP doesn't store any zone information at all
BTIMESTAMPTZ requires an explicit UTC offset to be provided on every insert, or it errors
CThey're exact synonyms; TIMESTAMPTZ is just shorter to type
DTIMESTAMP (without time zone) stores exactly the date/time value given, with no zone conversion; TIMESTAMPTZ stores the instant internally as UTC and converts to/from the session's TimeZone setting on input and output — so the same stored TIMESTAMPTZ value can display differently to sessions in different time zones
Both RESTRICT and NO ACTION prevent deleting a referenced row that still has dependents. What's the actual difference between them?
ARESTRICT only applies to UPDATE; NO ACTION only applies to DELETE
BThey're identical in every way; NO ACTION is just older SQL-standard terminology
CRESTRICT allows the delete to proceed with a warning; NO ACTION blocks it outright
DNO ACTION (the default if neither is specified) checks the constraint at the end of the statement (or, if deferred, at the end of the transaction) — this allows a same-statement/transaction sequence that temporarily "breaks" the constraint before fixing it before the check runs; RESTRICT checks immediately, with no possibility of deferring it, even if the constraint is otherwise marked DEFERRABLE
What's a key trade-off between a UUID primary key (generated with gen_random_uuid()) and a BIGSERIAL primary key?
AThere is no meaningful trade-off; they're interchangeable in every situation
BBIGSERIAL values are globally unique across every database in the world, while UUIDs are only unique within one table
CUUID primary keys cannot have indexes built on them
DUUIDs can be generated independently on multiple systems (clients, distributed services) without coordinating with the database or each other, avoiding a central sequence bottleneck and letting IDs be assigned before a row is even inserted — at the cost of being larger (16 bytes vs 8), unordered by generation time (hurting index locality and range-scan-friendliness), and less human-readable than a small sequential integer
Sign up free to play
Answer all 10 questions (7 more), see explanations for every answer, and track your score.