Quiz: Deep Postgres: Pt. 1
Does SQL make you squeal?
Part 1 of 2. Go to Part 2
PostgreSQL 🐘 Is easily my favorite Database! I’m always learning new tricks and gotchas, so I decided to put them in a new Quiz!
This quiz covers a mix of familiar and lesser-known PostgreSQL features and gotchas: from built-in aggregates to type casting, constraints, and more.
Good luck! 🍀
Warmup: Functions
Which is NOT a built-in aggregate function in PostgreSQL?
MEDIAN
is not built-in! You need:
Common built-in aggregates:
MIN
,MAX
,COUNT
AVG
,SUM
ARRAY_AGG
,STRING_AGG
- Various statistical functions
Warmup: Type Casting
Which of these type conversions is invalid ❌?
PostgreSQL supports three casting syntaxes:
- ANSI SQL:
CAST(expression AS type)
. - PostgreSQL:
expression::type
. - Type function:
type 'literal'
.
All are functionally equivalent, but:
CAST()
is most portable.::
is PostgreSQL-specific but commonly used.- Infix-style
type 'literal'
is less common but still valid.
Constraints
How many NULL values are allowed here?
UNIQUE constraints in PostgreSQL:
- Allow multiple NULL values.
NULL
≠NULL
in uniqueness checks.
To prevent NULL
values, add NOT NULL
:
Date/Time
What does this return?
Intervals are a powerful tool for simplifying date range operations!
Date arithmetic in PostgreSQL:
+ interval '24 hours'
adds 24 hours+ interval '1 day'
adds 1 day+ interval '1 month'
adds 1 month+ interval '1 year'
adds 1 year
The result is 2024-11-28 00:00:00
.
Timestamps
What’s the diff between timestamptz
and timestamp
?
timestamp
stores date/time without timezone information.
timestamptz
stores date/time with timezone information.
timestamp
is timezone agnostictimestamptz
stores the timezone offset
Use timestamptz
when you need to store timezone information. (You usually don’t.)
Generally I recommended to normalize all times to UTC and use timestamp
to avoid plenty of timezone issues.
Dizzying Types
I’m sure you’ve used so many types in PostgreSQL, right?
The next few questions are about Postgres’ v17 native types. 🤯
For each question, identify the one invalid type. 🕵️♂️
Onward!
Postgres Types
Which of these is not ❌ a valid PostgreSQL type?
PostgreSQL has a rich set of data types, but STRING(100)
is not one of them.
The correct string types include:
VARCHAR(100)
(variable-length string)CHAR(100)
(fixed-length string)TEXT
(unlimited length)CHARACTER VARYING(100)
(same asVARCHAR(100)
)
Postgres Types
Which of these is not ❌ a valid PostgreSQL type?
It may seem familiar since decimal128
is a type in many places (including Mongo and Java.) It’s not a valid PostgreSQL type, decimal
is.
The correct numeric types include:
int
(4-byte integer)bigint
(8-byte integer)real
(4-byte floating-point)double precision
(8-byte floating-point)bigserial
(auto-incrementing 8-byte integer)smallserial
(auto-incrementing 2-byte integer)
Postgres Types
Which of these is not ❌ a valid PostgreSQL type?
Did this make you frustrated, even angry? You’re not alone! To quote an unnamed “core” database contributor, “what the hell, Dan?! I crashed on the type questions! Thats violent sir! Not sharing my score, hah.” 😈 You’re welcome.
PostgreSQL’s rich set of network types do not include ipv4
. Every single time I attempt to use it without googling, I get it wrong. Perhaps macaddr8
makes me feel there must be ipv4
and ipv6
types. Nope, inet
covers both. Also, cidr
covers network masks for both.
Valid network types include:
cidr
(IPv4/IPv6 network address)inet
(IPv4/IPv6 host address)macaddr
(MAC address)macaddr8
(EUI-64 MAC address)
Postgres Types
Which of these is not ❌ a valid PostgreSQL type?
PostgreSQL has a rich set of specialized types, but currency
is not one of them!
The valid types include:
xml
(XML data)uuid
(UUID)money
(currency amount)interval
(time interval)
Postgres Types
Which of these is not ❌ a valid PostgreSQL type?
PostgreSQL has a rich set of specialized types, but triangle
is not one of them.
I believe upcoming versions of GEOS will include Triangle
OGC/WKT support, which means it should eventually be included in Postgis. (Basically, this answer may be wrong in the future.)
The correct specialized types include:
box
(rectangular box)line
(infinite line)point
(2D point)circle
(2D circle)polygon
(2D polygon)
Integer Arithmetic
What happens when calculating total possible student IDs?
PostgreSQL’s integer
type is 32-bit signed, ranging from -2,147,483,648
to 2,147,483,647
.
The calculation 256^4
= 4,294,967,296
exceeds this range.
To handle larger numbers:
Date/Time
Which is the smallest timestamp
literal to exceed the maximum time
precision in Postgres?
PostgreSQL timestamps have microsecond (6 decimal places) precision.
- Maximum:
.123456
(6 digits) - Nanoseconds (9 digits) are truncated
- Timezone offset required for
timestamptz
Not-so-common gotcha: Some languages/frameworks send nanosecond precision that gets silently truncated!
Postgres Types
Which of these is not ❌ a valid PostgreSQL type?
(Seriously, these are (mostly) real types.)
PostgreSQL has several geometric and text search types built-in, but tsrank
is not one of them.
The correct geometric and text search types include:
lseg
(line segment)txid
(transaction ID)bytea
(binary data)tsquery
(text search query)tsvector
(text search document)
Constraints
When is this grade constraint checked?
NOT VALID
constraints:
- Are checked at transaction commit
- Don’t validate existing rows
- Useful for large tables
Without NOT VALID
:
- Constraint is checked immediately
- All existing rows are validated
- Can be slow on large tables
Well done! You went deep on several areas of PostgreSQL! 🐘
I hope you learned something new, or at least got a score to gloat about! 🏆
Check out Part 2 for more Postgres fun! 🚀
Want more thrills in life? Check out my Quiz Collection for endless* fun!