Dan Levy's Avatar DanLevy.net

Quiz: Deep Postgres: Pt. 1

Does SQL make you squeal?

Quiz: Deep Postgres: Pt. 1

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. 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.

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.
  • NULLNULL 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’s the diff between timestamptz and timestamp?

timestamp stores date/time without timezone information. timestamptz stores date/time with timezone information.

  • timestamp is timezone agnostic
  • timestamptz 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!

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.

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)

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 is the smallest timestamp literal to exceed the maximum time precision in Postgres?

CREATE TABLE class_sessions (
id INT GENERATED BY DEFAULT AS IDENTITY,
start_time timestamptz,
end_time timestamptz
);

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!

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)
  • txid (transaction ID)
  • 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 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

Quiz Score:

Congrats! Quiz completed.

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!

Edit on GitHubGitHub