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

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.

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)
);

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.

They both are the same size (8 bytes) and store the same UTC-based date & time!

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

timestamptz

• let’s you convert dates between timezones.

• Takes into account the server/connection timezone setting. (See TZ environment variable.)

timestamp

• Stores the date & time without timezone information.
• Useful for explicitly storing standardized dates, either in UTC or a specific timezone.

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

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)

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)

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)

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)

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;

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!

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)

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