Dan Levy

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! 🍀

1. 

Warmup: Functions

Which is NOT a built-in aggregate function in PostgreSQL?

SELECT
  MIN(grade) as lowest,
  MAX(grade) as highest,
  AVG(grade) as average,
  MEDIAN(grade) as middle
FROM grades;

MEDIAN is not built-in! You need:

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

Common built-in aggregates:

  • MIN, MAX, COUNT
  • AVG, SUM
  • ARRAY_AGG, STRING_AGG
  • Various statistical functions

2. 

Warmup: Type Casting

Which of these type conversions is invalid ❌?

PostgreSQL supports three casting syntaxes:

  1. ANSI SQL: CAST(expression AS type).
  2. PostgreSQL: expression::type.
  3. Typed literal: 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.

3. 

Constraints

How many NULL values are allowed here?

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255),
  UNIQUE(email)
);

UNIQUE constraints in PostgreSQL:

  • Allow multiple NULL values.
  • NULL ≠ NULL in uniqueness checks.

To prevent NULL values, add NOT NULL:

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255) NOT NULL,
  UNIQUE(email)
);

4. 

Date/Time

What does this return?

SELECT '2024-11-27'::date + interval '24 hours';

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.

5. 

Timestamps

What is the most accurate statement about timestamptz and timestamp?

They are both 8 bytes, but they do not store the same kind of value.

So what’s the difference? It’s in the input parsing.

timestamptz

  • Normalizes input to an absolute point in time.
  • Takes into account the server/connection TimeZone setting when parsing input without an explicit offset and when displaying output.

timestamp

  • Stores a date and time without timezone conversion.
  • Does not preserve or normalize timezone information.

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!

6. 

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 as VARCHAR(100))

7. 

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)

8. 

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)

9. 

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)

10. 

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.

This question concerns PostgreSQL’s built-in geometric types. Extensions such as PostGIS have their own geometry support; that does not make triangle a built-in PostgreSQL type.

The correct specialized types include:

  • box (rectangular box)
  • line (infinite line)
  • point (2D point)
  • circle (2D circle)
  • polygon (2D polygon)

11. 

Integer Arithmetic

What happens when calculating total possible student IDs?

SELECT 256 * 256 * 256 * 256;

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:

-- Use BIGINT
SELECT 256::bigint * 256 * 256 * 256;


-- Or numeric for arbitrary precision
SELECT 256::numeric * 256 * 256 * 256;

12. 

Date/Time

Which example has the fewest fractional-second digits beyond PostgreSQL’s six-digit timestamp precision?

PostgreSQL timestamps have microsecond (6 decimal places) precision.

  • Maximum: .123456 (6 digits)
  • Nanoseconds (9 digits) are rounded or truncated to the supported precision
  • Timezone offsets are accepted for timestamptz, but not required

Not-so-common gotcha: Some languages/frameworks send nanosecond precision, but PostgreSQL stores timestamps at microsecond precision.

13. 

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)
  • bytea (binary data)
  • tsquery (text search query)
  • tsvector (text search document)

14. 

Constraints

When is this grade constraint checked?

ALTER TABLE students
ADD CONSTRAINT valid_grade
CHECK (
  (grade >= 0 AND grade <= 100) OR
  grade IS NULL
) NOT VALID;

NOT VALID constraints:

  • Are checked immediately for new inserts and updates
  • Don’t validate existing rows
  • Can validate existing rows later with VALIDATE CONSTRAINT
  • Are 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!

Loading comments...