BrevFeed
Sign in

8. Choosing column types

📖 Reading · 4 min
💡 Every code box below is live — edit it and hit Run.

Every column has a type that decides what it can hold. Picking good types keeps bad data out and makes queries fast. Here are the ones you'll use almost every day.

The everyday types

Type Holds Example
INTEGER whole numbers 42, -7
BIGINT very large whole numbers order ids, counters
DECIMAL(p,s) exact decimals (money!) DECIMAL(6,2) → 1234.56
DOUBLE approximate decimals measurements
VARCHAR / TEXT text names, descriptions
BOOLEAN true / false is_active
DATE a calendar date 2024-03-15
TIMESTAMP date and time 2024-03-15 09:30:00

Use DECIMAL for money, never DOUBLE. DOUBLE is approximate, so 0.1 + 0.2 won't be exactly 0.3 — fine for a sensor reading, a disaster for a bank balance. DECIMAL(p,s) stores exactly: p total digits, s after the point.

Values are typed too

You can build and convert typed values in a plain SELECT, no table needed:

SELECT
  DECIMAL '19.99'          AS price,
  DATE '2024-03-15'        AS a_date,
  42::VARCHAR              AS num_as_text,
  (10 / 4.0)               AS division;

Try changing 4.0 to 4 — integer-by-integer division truncates, so 10 / 4 is 2, while 10 / 4.0 is 2.5. Types quietly change results.

Types you'll meet in Postgres

Postgres adds handy types beyond the standard set — worth knowing by name:

  • SERIAL / BIGSERIAL — an auto-incrementing integer id (the next lesson's tables use manual ids to stay engine-neutral, but in real Postgres you'd reach for SERIAL).
  • UUID — a globally-unique id.
  • JSONB — structured JSON you can query (its own module later).
  • TIMESTAMPTZ — a timestamp that remembers its time zone.

Try it

Return one row with two columns: the text '12.5' converted to a DECIMAL aliased as as_number, and the number 100 converted to VARCHAR aliased as as_text.

SELECT
  -- cast '12.5' to DECIMAL as as_number,
  -- cast 100 to VARCHAR as as_text

SELECT
  '12.5'::DECIMAL AS as_number,
  100::VARCHAR    AS as_text;
← Previous Next →