PostgreSQL中无法通过CREATE TYPE实现、需底层C语言自定义的类型示例咨询

Great question! You're right that PostgreSQL's CREATE TYPE (for enums, composite types, ranges, etc.) covers most common use cases, but there are scenarios where only a C-language user-defined type (UDT) can meet the requirements. These are cases where you need full control over storage, performance, operator behavior, or integration with PostgreSQL's core indexing/execution engine. Here are concrete examples:

• Geospatial/Geometric Types (like PostGIS's geometry/geography)
  The built-in box or point types are simple, but real-world geospatial data needs far more: compact binary storage (using Well-Known Binary, WKB), support for spatial reference systems (SRIDs), complex spatial operations (intersection, distance calculation), and integration with specialized indexes like GIST. A CREATE TYPE composite type could simulate this with separate columns for coordinates, SRID, etc., but it would be inefficient (no compact storage) and couldn't support the optimized spatial operators or indexing that PostgreSQL's core engine expects. PostGIS's types are entirely implemented in C to handle all these low-level requirements.

• Full-Text Search Types (like tsvector/tsquery)
  PostgreSQL's built-in full-text types aren't just simple strings or lists. tsvector stores preprocessed tokens with positional information, and tsquery represents search expressions with boolean logic. To support fast indexing (via GIN/GIST) and efficient matching, these types rely on custom storage formats and low-level operator implementations that CREATE TYPE can't provide. You couldn't replicate the performance or functionality of tsvector using a composite type or enum—you need C-level control over how the data is stored, indexed, and compared.

• High-Performance Numeric Types with Custom Logic
  While PostgreSQL has numeric, int, and float types, if you need specialized numeric behavior (e.g., custom rounding rules for financial applications, arbitrary-precision numbers with non-standard arithmetic, or fixed-point types with strict overflow handling), a C UDT is the only way. A composite type would require slow PL/pgSQL functions for operations, whereas a C implementation can leverage CPU-level optimizations and integrate directly with PostgreSQL's numeric execution engine. For example, some financial systems use custom decimal types implemented in C to avoid floating-point errors and enforce strict business rules.

• Custom Semi-Structured Data Types
  While jsonb is powerful, if you need a specialized semi-structured format (e.g., a compact binary format for log data, or a schema-enforced type with custom validation rules), CREATE TYPE can't cut it. A C UDT lets you define custom input/output functions to parse/serialize your format, validate data at insertion time, and even optimize storage with compression. For example, if you have a proprietary log format that's more efficient than JSON, a C type can handle parsing and storage directly, avoiding the overhead of converting to/from JSON.

• Types Requiring Custom Comparison/Ordering Logic
  Suppose you want a "fuzzy string" type where equality is based on edit distance (e.g., "hello" equals "hallo"), or a date type that uses a custom calendar system. CREATE TYPE can't override the default comparison operators (=, <, >) for a type at the core level. With a C UDT, you can define custom comparison functions that PostgreSQL uses for sorting, indexing, and conditional checks. This is essential for types where the default value comparison doesn't align with your business logic.

• Types Integrating with External C Systems
  If you need a type that directly maps to a C struct used by an external application (e.g., a type representing a network packet header, or a data structure from a legacy system), a C UDT is the only way to ensure seamless, zero-copy integration. A CREATE TYPE composite type would require converting between PostgreSQL's internal representation and the external struct, adding overhead and potential errors.

In short, use a C UDT when you need:

• Full control over data storage format (compactness, compression, binary layout)
• High-performance operators and functions that integrate with PostgreSQL's core engine
• Support for specialized indexes (GIN, GIST, SP-GiST)
• Custom input/output parsing/validation
• Deep integration with external C code or system-level data structures

内容的提问来源于stack exchange，提问作者David542