Type System - Relational - Showcases

Numeric Types

PostgreSQL stores exact and approximate numbers. Choose based on precision — numeric is exact, real/double are not.

Label
Svelte Framework	`5`	`42`	`1500000`	`0.00`	`22.5`	`37.7749`
PostgreSQL Database	`5`	`100`	`9200000`	`9999.99`	`-40`	`45.4215`
Drizzle ORM	`4`	`0`	`350000`	`150.50`	`98.6`	`52.52`
Bun Runtime	`4`	`7`	`420000`	`NULL`	`NULL`	`35.6762`
Inactive Feature	`1`	`0`	`12`	`0.01`	`0`	`0`
Edge Case: Max Int	`5`	`2147483647`	`0`	`99999999.99`	`-273.15`	`90`
Edge Case: Min Values	`1`	`0`	`0`	`0.00`	`-273.15`	`-90`

Text / String Types

text is the workhorse — no performance penalty vs varchar. Use varchar(n) only when you need a constraint. char(n) is space-padded and rarely useful.


Svelte Framework	SVLT-001	`US`
PostgreSQL Database	PGSQ-002	`CA`
Drizzle ORM	DRZL-003	`DE`
Bun Runtime	BUNR-004	`JP`
Inactive Feature	INAC-005	`NULL`
Edge Case: Max Int	MAXI-006	`GB`
Edge Case: Min Values	MINV-007	`AU`

Date & Time Types

Always use timestamptz for wall-clock times. timestamp without timezone is rarely correct. interval stores durations PostgreSQL can do arithmetic with.

Description
Project kickoff	`2024-01-15`	`09:00:00`	`"2024-01-15T09:00:00.000Z"`	`02:00:00`
Sprint 1 retrospective	`2024-01-29`	`14:30:00`	`"2024-01-29T14:30:00.000Z"`	`01:30:00`
Database migration window	`2024-03-01`	`02:00:00`	`"2024-03-01T02:00:00.000Z"`	`04:00:00`
Year-long subscription	`2024-01-01`	`00:00:00`	`"2024-01-01T00:00:00.000Z"`	`1 year`
Interval arithmetic demo	`2024-06-15`	`12:00:00`	`"2024-06-15T12:00:00.000Z"`	`2 years 3 mons 4 days 05:00:00`

Boolean

PostgreSQL accepts many literals: true, 'yes', 'on', '1'. Always store as boolean, never as integer or string.

Svelte Framework true

PostgreSQL Database true

Drizzle ORM true

Bun Runtime true

Inactive Feature false

Edge Case: Max Int true

Edge Case: Min Values true

UUID

128-bit universally unique identifiers. gen_random_uuid() generates v4 UUIDs natively (PG 13+, no extension needed). Use for external-facing IDs — keep serial for internal references.

Svelte Framework e47921b4-1196-47d1-9cf4-c808f3646f1d

PostgreSQL Database 3d3f6d5a-3c31-4fea-8966-14d66501513f

Drizzle ORM 3c3c292f-eb4f-4834-841f-2ac7dd80960b

Bun Runtime dc9cd82b-15c1-4e1b-bbb0-ba3e199a5ef0

Inactive Feature 202aeb3c-f586-4831-ae80-6853269bb7e2

Edge Case: Max Int 42815ad3-aac2-4279-bd17-6f9458a812bb

Edge Case: Min Values 8eb4527b-c0c2-4f43-b800-acd9f1f9055c

JSON Types

Always use jsonb. It's parsed binary — indexable with GIN, queryable with @> containment and ->> path extraction. json stores verbatim text (preserving whitespace and duplicate keys).

Getting Started Guide

documentation beginnersetup

{
  "tags": [
    "beginner",
    "setup"
  ],
  "nested": {
    "level": 1,
    "description": "Entry-level guide"
  },
  "category": "documentation"
}

settings (jsonb)

{
  "theme": "light",
  "fontSize": 14
}

API Reference

documentation apireferenceadvanced

{
  "tags": [
    "api",
    "reference",
    "advanced"
  ],
  "nested": {
    "level": 3,
    "description": "Complete API docs"
  },
  "category": "documentation"
}

settings (jsonb)

{
  "theme": "dark",
  "fontSize": 12,
  "showLineNumbers": true
}

Deployment Checklist

operations deploymentdevops

{
  "tags": [
    "deployment",
    "devops"
  ],
  "nested": {
    "level": 2,
    "description": "Pre-deploy verification"
  },
  "category": "operations"
}

settings (jsonb)

{
  "autoSave": true
}

Draft: Security Audit

security auditcompliance

{
  "tags": [
    "audit",
    "compliance"
  ],
  "nested": {
    "level": 4,
    "description": "Annual security review"
  },
  "category": "security"
}

Arrays

PostgreSQL arrays are powerful for tags, labels, and ordered lists. GIN indexes enable fast @> containment queries. Use junction tables instead when elements need foreign keys.

Name
Frontend Stack	`[ 95, 88, 72, 100, 91 ]`	sveltetypescriptunocssbits-ui
Backend Stack	`[ 90, 85, 95, 78 ]`	postgresqldrizzlesveltekitbun
Empty Collection	`[]`	`[]`
Overlapping Tags	`[ 100, 100, 100 ]`	sveltepostgresqlshared-tag
Single Element	`[ 42 ]`	solo

Range Types

One of PostgreSQL's most underappreciated features. A single tstzrange replaces separate start/end columns, with native overlap (&&), containment (@>), and adjacency (-|-) operators. GiST indexes make them fast.

Resource				Priority
Conference Room A	`[1,3)`	`["2024-03-15 09:00:00+00","2024-03-15 11:00:00+00")`	`[2024-03-15,2024-03-16)`	8
Conference Room A	`[1,3)`	`["2024-03-15 13:00:00+00","2024-03-15 15:00:00+00")`	`[2024-03-15,2024-03-16)`	5
Server Rack B	`[10,20)`	`["2024-03-01 00:00:00+00","2024-04-01 00:00:00+00")`	`[2024-03-01,2024-04-01)`	10
Parking Spot 42	`NULL`	`["2024-03-15 08:00:00+00","2024-03-15 18:00:00+00")`	`[2024-03-15,2024-03-16)`	3
Meeting Room (overlapping)	`[5,8)`	`["2024-03-15 10:00:00+00","2024-03-15 12:00:00+00")`	`[2024-03-15,2024-03-17)`	7
Empty Range Demo	`empty`	`empty`	`empty`	1

Network & Geometry Types

inet for host addresses, cidr for networks, macaddr for hardware. All support containment operators (<<, >>). point for simple 2D coordinates — use PostGIS for serious GIS work.

Device
Web Server (Primary)	`192.168.1.10`	`192.168.1.0/24`	`08:00:2b:01:02:03`	`(37.7749, -122.4194)`
Web Server (Secondary)	`192.168.1.11`	`192.168.1.0/24`	`08:00:2b:01:02:04`	`(37.775, -122.4195)`
Database Server	`10.0.0.5`	`10.0.0.0/8`	`a4:83:e7:2d:6b:01`	`(45.4215, -75.6972)`
IPv6 Gateway	`::1`	`::1/128`	`NULL`	`NULL`
Load Balancer	`172.16.0.1`	`172.16.0.0/12`	`de:ad:be:ef:ca:fe`	`(52.52, 13.405)`
Edge Node (Tokyo)	`203.0.113.42`	`203.0.113.0/24`	`00:1a:2b:3c:4d:5e`	`(35.6762, 139.6503)`
Monitoring Agent	`192.168.1.100`	`192.168.1.0/24`	`08:00:2b:ff:ff:ff`	`(37.7749, -122.4194)`

Enums & Audit Log

pgEnum creates real PostgreSQL ENUM types — 4-byte storage, type-safe at DB level. Caveat: adding values requires ALTER TYPE, can't remove without recreation. Use for stable sets only.

			Description	Actor
`1`	create	info	Created specimen: Svelte Framework	`seed-script`
`2`	create	info	Created specimen: PostgreSQL Database	`seed-script`
`3`	create	info	Created specimen: Drizzle ORM	`seed-script`
`4`	update	info	Updated rating from 4 to 5	`admin`
`5`	update	info	Updated price from 100.00 to 150.50	`admin`
`6`	delete	warning	Purged expired cache entries	`system`
`7`	login	info	User logged in from 192.168.1.10	`user-001`
`8`	export	info	Exported 42 records as CSV	`user-001`
`9`	restore	warning	Restored soft-deleted document	`admin`
`10`	update	error	Failed to update: constraint violation	`system`

Types are read-only. See Mutability for live write operations.