CREATE TABLE and Data Types

Choose the right column type — not everything is text or integer

Every column you define is a promise. text says "arbitrary strings". numeric(4,2) says "up to 99.99, two decimal places, exact". timestamptz says "a moment in time, timezone-aware".\n\nPostgreSQL has over 40 built-in types — most tables use fewer than 10 of them. This lab is about learning which 10 are right for almost every situation and why the obvious choices (varchar, float, timestamp) are usually wrong.\n\nThe breweries table in beer_db is your canvas. Get the types right and every future INSERT will validate itself.

Why Type Choice Is a Promise

A column type is not just storage — it is a constraint the database enforces on every future INSERT and UPDATE. Get it wrong and you either lose precision, waste space, or create bugs that only surface under production load.

The Four Most Common Type Mistakes

  VARCHAR(255)    → use text instead; VARCHAR is no faster and the 255 limit
                    is arbitrary — it will bite you when a name is 256 chars long.

  FLOAT / REAL    → never for money or ABV; floats cannot represent 0.1 exactly.
                    Use numeric(p,s) for any value where exactness matters.

  TIMESTAMP       → always use timestamptz; it stores the UTC offset so a value
                    recorded in Prague reads correctly for a user in Tokyo.

  SERIAL / INT PK → use uuid with gen_random_uuid(); integer PKs leak row counts
                    to the outside world and clash when merging datasets.

The Right Types for a Brewery Catalog

CREATE TABLE breweries (
  id           uuid        NOT NULL DEFAULT gen_random_uuid(),
  name         text        NOT NULL,
  city         text        NOT NULL,
  country      text        NOT NULL DEFAULT 'Czech Republic',
  founded_year integer,
  location     point,
  created_at   timestamptz NOT NULL DEFAULT now()
);

Notice:

uuid for the PK — portable, non-guessable, merge-safe
text for all string columns — no arbitrary length cap
integer for year — whole numbers within int range
point for geographic coordinates — a PostgreSQL geometric type
timestamptz for audit timestamps — always timezone-aware

numeric(precision, scale)

An exact decimal type. precision is the total number of significant digits; scale is the number of digits after the decimal point. numeric(4,2) stores values from -99.99 to 99.99 exactly — no floating-point approximation.

timestamptz vs timestamp

timestamptz (timestamp with time zone) stores a UTC offset alongside the value. timestamp (without time zone) stores the local clock face with no offset information. Use timestamptz for any real-world event; timestamp only for things like "office hours" where timezone is irrelevant.

UUID primary key

A universally unique identifier: 128 bits, generated randomly, virtually guaranteed never to collide across any two systems anywhere. Unlike serial integers, UUIDs do not reveal the number of rows in the table and can be generated client-side before INSERT.

🏛️ Connect to beer_db

Connect to the beer_db database as postgres.

psql -h localhost -U postgres -d beer_db

beer_db=#

🔍 Inspect the Existing breweries Table

Run \d breweries to see the full table definition. Pay attention to the column types — they illustrate the type choices this lab is about.

\d breweries

📊 See What numeric Precision Looks Like

Run \d beers to see a table with numeric(4,2) for ABV and numeric(3,1) in the ratings table. These are the precision choices that prevent float rounding errors.

\d beers

abv | numeric(4,2) | not null |

🧪 Insert a Brewery and Watch the UUID Generate

Insert a brewery without providing an id — let gen_random_uuid() do it. Use RETURNING * to see the generated UUID immediately.

INSERT INTO breweries (name, city) VALUES ('Test Brewery', 'Prague') RETURNING *;

⚠️ Reproduce the Float Precision Bug

Run SELECT 0.1::float8 + 0.2::float8; and compare the result to SELECT 0.1::numeric + 0.2::numeric;. This is the rounding error that breaks ABV comparisons.

SELECT 0.1::float8 + 0.2::float8;

SELECT 0.1::numeric + 0.2::numeric;

?column? ------------------------ 0.30000000000000004 (1 row) ?column? ---------- 0.3 (1 row)

Lab complete! You know why the breweries table is designed the way it is:\n\n\n uuid PK : ✅ opaque, non-sequential, auto-generated\n text for strings : ✅ no arbitrary length cap\n numeric for ABV : ✅ exact — no float rounding errors\n timestamptz audit : ✅ timezone-aware, always UTC-consistent\n

Enable JavaScript to run the live terminal and track your progress.