Numeric & Text Types

numeric vs float, text vs varchar, collation basics. Practical PostgreSQL patterns for production queries.

Recipe

SELECT 0.1::float8 + 0.2::float8 AS float_sum,
       0.1::numeric + 0.2::numeric AS numeric_sum;
 
SELECT 'café' COLLATE "en_US" < 'cafe' COLLATE "en_US";

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

CREATE TABLE app.products (
  sku text NOT NULL PRIMARY KEY,
  price numeric(10,2) NOT NULL,
  description text NOT NULL
);

What this demonstrates:

• Runnable SQL against sample schemas
• Idiomatic PostgreSQL syntax
• Patterns safe for EXPLAIN review

Deep Dive

How It Works

• PostgreSQL parses SQL into a query tree.
• The planner estimates costs using statistics.
• Executor returns rows to the client protocol.

Notes

• Prefer readable SQL; optimizers rewrite internally.
• Test with production-like row counts.

Gotchas

• SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
• NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
• Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
• Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
• Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

FAQs

Related

• Data Types Basics - overview

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.