PostgreSQL Data Types

Choosing the right PostgreSQL data type is one of the most important schema design decisions you can make. The correct type enforces data integrity at the database level, enables efficient indexing, and prevents entire classes of application bugs before they reach production.

Syntax

Column data types are declared in CREATE TABLE or added via ALTER TABLE:

CREATE TABLE table_name (
  column_name data_type [constraint],
  ...
);

Practical Example

The following table models IoT sensor readings and demonstrates a realistic mix of built-in types:

CREATE TABLE sensor_readings (
  reading_id  BIGSERIAL PRIMARY KEY,
  device_uuid UUID        NOT NULL DEFAULT gen_random_uuid(),
  is_active   BOOLEAN     NOT NULL DEFAULT TRUE,
  label       VARCHAR(100),
  notes       TEXT,
  temperature NUMERIC(6, 2),
  voltage     DOUBLE PRECISION,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  tags        TEXT[],
  payload     JSONB
);

Insert a sample row and retrieve it:

INSERT INTO sensor_readings (label, temperature, voltage, tags, payload)
VALUES (
  'Zone-A Rack 3',
  22.75,
  3.3,
  ARRAY['production', 'zone-a'],
  '{"firmware": "v3.0", "threshold": 25.0}'
)
RETURNING reading_id, device_uuid, recorded_at;

Inspect the table structure:

\d sensor_readings

Type Reference

Testing with Vela

When refactoring a schema to fix type choices — for example, migrating a VARCHAR column that was wrongly used for monetary values to NUMERIC — Vela database branching lets you apply the migration on a copy of production data and validate query results before touching the live database. Create a branch, run the ALTER TABLE … ALTER COLUMN … TYPE NUMERIC USING migration, confirm no precision is lost, then promote the change to production with confidence.

Production Tips

• Use TIMESTAMPTZ instead of TIMESTAMP for event-time columns to eliminate time-zone ambiguity.
• Never use REAL or DOUBLE PRECISION for money — use NUMERIC(p,s) for exact arithmetic.
• Prefer TEXT over VARCHAR(n) unless you have a real business constraint on length; a CHECK constraint is more explicit than a hidden VARCHAR limit.
• Use UUID primary keys when rows are created by distributed clients or when sequential IDs must not be guessable from URLs.
• Use JSONB over JSON so you can create GIN indexes and use containment operators like @>.
• Use BIGSERIAL or BIGINT GENERATED ALWAYS AS IDENTITY for high-volume tables to avoid exhausting the INTEGER range (~2 billion rows).