Choosing data types

conceptThe concept

Numbers: integer (4 bytes) and bigint (8 bytes) for counts and keys; numeric(p,s) for exact decimals such as money; double precision for measurements where tiny binary rounding is acceptable.

Text: text and varchar(n) are stored the same way; varchar(n) just adds a length check. Prefer text plus a CHECK constraint if you need a limit you may change later.

Time: timestamptz stores an absolute instant (internally UTC) and displays it in the session's TimeZone. timestamp without time zone is a wall-clock reading with no zone attached. For events that happened at a moment, use timestamptz.

Keys: GENERATED ALWAYS AS IDENTITY is the standard replacement for serial. UUIDs avoid central coordination; uuidv7() (PostgreSQL 18) is time-ordered, so new keys land at the end of the index instead of on random pages.

Casts: value::type is PostgreSQL shorthand for CAST(value AS type). A cast that cannot be represented fails with an error rather than guessing.

whyWhy it matters

Float money produces cents that do not add up. A timestamp column filled by servers in different zones produces orders that seem to arrive before they were placed. Switching a hot table's key from integer to bigint after it runs out at 2,147,483,647 is a painful table rewrite. All three are type decisions made on day one.

Use caseReal-world scenario

The shop uses numeric(10,2) for prices, timestamptz for every event time, jsonb for loosely structured product attributes and a text[] array for tags.

Try itExamples

Run and edit these in the interactive lesson.

SELECT 0.1::double precision + 0.2::double precision AS float_sum,
       0.1::numeric + 0.2::numeric AS numeric_sum,
       pg_typeof(0.1) AS literal_type;

SELECT now() AS server_now,
       now() AT TIME ZONE 'Asia/Kolkata' AS kolkata_wall_clock,
       timestamptz '2025-07-01 09:00 Europe/Berlin' AS berlin_meeting,
       pg_typeof(now() AT TIME ZONE 'Asia/Kolkata') AS result_type;
SHOW TimeZone;

SELECT '42'::int + 1 AS int_math,
       CAST('2025-01-31' AS date) + 1 AS next_day,
       interval '1 day' * 3 AS three_days,
       'abc'::varchar(2) AS explicit_cast_truncates;
SELECT 'forty-two'::int;

SELECT gen_random_uuid() AS random_uuid,
       uuidv7() AS time_ordered_uuid,
       pg_column_size(1::smallint) AS int2_bytes,
       pg_column_size(1::integer) AS int4_bytes,
       pg_column_size(1::bigint) AS int8_bytes,
       pg_column_size(1.50::numeric) AS numeric_bytes;

internalsUnder the hood

Each value has a storage size and alignment. Column order affects padding: an int4 followed by an int8 wastes 4 bytes per row. pg_column_size() shows the bytes a value takes. Values larger than about 2 kB are compressed and/or moved to a TOAST table automatically.

Avoid / preferDo this, not that

CREATE TABLE invoices (amount real);

CREATE TABLE invoices (amount numeric(12,2));

CREATE TABLE logins (at timestamp);

CREATE TABLE logins (at timestamptz);

PitfallsCommon mistakes

• Storing money in real or double precision. Use numeric or integer cents.
• Using timestamp (without time zone) for event times. Use timestamptz and convert for display.
• Using integer keys on tables that could pass two billion rows, or serial where GENERATED ... AS IDENTITY is clearer and harder to misuse.

LimitsWhen not to use it

Do not use char(n), and do not pick varchar(255) out of habit. In PostgreSQL text performs the same; add a CHECK on length only when the limit is a real business rule.

SecuritySecurity notes

Never store passwords in a text column, even encrypted. Store a slow salted hash (bcrypt, scrypt or Argon2) produced by the application or pgcrypto's crypt().

ChallengeTry it yourself

SELECT 0.1::float8 + 0.2::float8 = 0.3 AS float_equal,
       0.1::numeric + 0.2 = 0.3 AS numeric_equal,
       pg_input_is_valid('2025-13-01', 'date') AS valid_date;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Data types
• Type conversion