Choosing Column Types: Strings, Money, Dates, Booleans and Status

A column type is a promise about what the column can hold, and the wrong promise fails quietly: a total that is a fraction of a cent off, a name cut short, an appointment that moves by five and a half hours when the server changes country. This part makes each of those mistakes on purpose, on both engines, and shows the type that does not make them. Money, strings, dates, booleans, status values, phone numbers and ids, in that order.

Money is decimal, never float

A float is a binary fraction, and one tenth has no exact binary form, so the first line of the script prints what every language prints:

SELECT 0.1::float8 + 0.2::float8 AS float_sum,
       CASE WHEN 0.1::float8 + 0.2::float8 = 0.3::float8
            THEN 'equal' ELSE 'not equal' END AS float_sum_vs_0_3;

      float_sum      | float_sum_vs_0_3
---------------------+------------------
 0.30000000000000004 | not equal

An invoice with ten sachets at 0.10 each shows what that does to a total. The table has both column types side by side:

CREATE TABLE invoice_lines (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    description     text          NOT NULL,
    amount_float    float8        NOT NULL,
    amount_decimal  numeric(12,2) NOT NULL
);

 lines |    float_total     | decimal_total | float_total_is_1 | decimal_total_is_1
-------+--------------------+---------------+------------------+--------------------
    10 | 0.9999999999999999 |          1.00 | f                | t

SQL Server prints the same binary value as 0.99999999999999989, seventeen digits instead of the shortest form, and the same verdict. The decimal type is exact because it stores digits, not binary fractions: numeric(12,2) in PostgreSQL, DECIMAL(12,2) in SQL Server, twelve digits in all, two after the point. It rounds a third decimal (10.005 becomes 10.01) and refuses a value with more than ten digits before the point, which is the promise working. Leave SQL Server's MONEY type and PostgreSQL's money alone; both carry rounding and locale surprises that decimal does not.

Strings: the length is a rule

ClinicLive maps a patient's name to 200 characters. Section 2 inserts 201, and both engines refuse: SQL Server with Msg 2628, String or binary data would be truncated in table 'dd_p05.dbo.patients', column 'full_name', which names the table, the column and the first hundred characters of the value; PostgreSQL with value too long for type character varying(200), which names none of them. Then the trap on both: an explicit cast to the shorter type does not raise anything, it cuts the value to 200 and moves on.

PostgreSQL's idiom is text with no limit and, where a limit is a real rule, a named CHECK constraint, whose error names the rule instead of the type:

CREATE TABLE patients_text (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name  text NOT NULL
        CONSTRAINT ck_patients_text_full_name_length CHECK (char_length(full_name) <= 200)
);

The other string question is which alphabet fits. A PostgreSQL database is UTF-8, so Yuki Tanaka 田中由紀 is 16 characters and 24 bytes in any string column. SQL Server's VARCHAR follows the column's collation code page, and the same name came back as Yuki Tanaka ????; it survived only in NVARCHAR, or in a VARCHAR under a _UTF8 collation, and only when the literal carried the N prefix. That is why Part 1's SQL Server script says NVARCHAR and N'...' everywhere.

Dates: store the instant, convert on the way out

An appointment at 04:30 UTC is one moment. What the clinic shows depends on where the clinic is, and the database should convert, not guess. Section 3 stores two such moments, one in July and one in December, and asks for them in two cities:

SELECT id,
       starts_at                                AS stored,
       starts_at AT TIME ZONE 'Asia/Kolkata'    AS kolkata,
       starts_at AT TIME ZONE 'Europe/Madrid'   AS madrid
FROM appointments ORDER BY id;

 id |         stored         |       kolkata       |       madrid
----+------------------------+---------------------+---------------------
  1 | 2026-07-15 04:30:00+00 | 2026-07-15 10:00:00 | 2026-07-15 06:30:00
  2 | 2026-12-15 04:30:00+00 | 2026-12-15 10:00:00 | 2026-12-15 05:30:00

Madrid moves an hour between July and December; the stored value did not move at all. That is timestamptz: an instant, stored as UTC, converted on display. SQL Server has no zone-aware conversion for DATETIME2, so the same query needs two hops, AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time', and it takes Windows zone names only; PostgreSQL takes IANA names only, and each engine rejects the other's spelling. The trap on both is the type that throws the offset away: '2026-07-15T10:00:00+05:30' cast to PostgreSQL's timestamp or to SQL Server's DATETIME2 is stored as 10:00, not 04:30, without a warning. SQL Server's type that keeps the offset is DATETIMEOFFSET. The convention ClinicLive follows is simpler than either: every stored time is UTC, and the app converts for the screen, which is the point of Npgsql refusing a local DateTime.

Booleans

PostgreSQL has a boolean: WHERE is_active is a complete condition, 'yes' is accepted as true, and a bare 5 is refused with column "is_active" is of type boolean but expression is of type integer. SQL Server has BIT, which is a number: 5 becomes 1, WHERE is_active fails with Msg 4145, and WHERE is_active = TRUE fails with Invalid column name 'TRUE' because T-SQL has no boolean literal. Write = 1 there and mean it.

Status: three ways to allow six values

ClinicLive stores an appointment's status as text with a CHECK constraint, and section 5 builds that plus the two alternatives. The CHECK keeps the list in the table definition:

CREATE TABLE appointments_check (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status  text NOT NULL
        CONSTRAINT ck_appointments_check_status
        CHECK (status IN ('Booked', 'CheckedIn', 'InProgress', 'Done', 'Cancelled', 'NoShow'))
);

Adding Rescheduled means dropping the constraint and adding it again with a longer list, on both engines. One finding from the run: SQL Server's default collation is case-insensitive, so 'booked' passed the CHECK and was stored in lower case; PostgreSQL refused it. A lookup table turns the values into rows, so adding one is an INSERT with no schema change, and the rows can carry facts, here a label for the screen and whether the status still holds the slot:

CREATE TABLE appointment_statuses (
    code        text PRIMARY KEY,
    label       text NOT NULL,
    holds_slot  boolean NOT NULL
);

The third way exists only in PostgreSQL. CREATE TYPE appointment_status AS ENUM (...) is a syntax error on SQL Server. An enum column refuses unknown values, adding one is ALTER TYPE ... ADD VALUE 'Rescheduled' AFTER 'Booked', and sorting follows the declared order rather than the alphabet, which the run showed as Booked, Rescheduled, Done, Cancelled. What it cannot do is forget a value: DROP VALUE answers dropping an enum value is not implemented. For a list that changes, the lookup table is the safest; for a short fixed list, the CHECK is the simplest and the one that works on both engines.

Phone numbers are strings, ids are bigint

Section 6 stores the same phone number as a number and as text:

INSERT INTO phone_test (as_bigint, as_text) VALUES (02055500101, '02055500101');

 as_bigint  |   as_text
------------+-------------
 2055500101 | 02055500101

The leading zero is gone, and '+00-20-5550-0101' into the number column is refused outright on both engines. A phone number is digits people dial, not a quantity; it is text with a length. Section 7 makes the last point with the id column. An int tops out at 2,147,483,647, and a table whose identity starts two below that accepts two rows and refuses the third: Arithmetic overflow error converting IDENTITY to data type int on SQL Server, nextval: reached maximum value of sequence on PostgreSQL. Two billion rows sounds far away until a chat log or an event table gets there, and widening the column then is a rewrite of the table and every foreign key that points at it. bigint costs four more bytes per row and is what Part 1 used without comment.

Frequently asked

What data type should I use for money in SQL?

A decimal with a fixed scale, such as decimal(12,2) or numeric(12,2). Float and double are binary and cannot hold 0.1 exactly, so ten lines of 0.10 sum to 0.9999999999999999. Avoid the engines' own money types; decimal is exact and portable.

Should I store dates in UTC or local time?

Store the instant in UTC and convert for display. In PostgreSQL use timestamptz, which stores UTC and converts with AT TIME ZONE. In SQL Server use DATETIME2 with a UTC convention, or DATETIMEOFFSET if the offset itself must be kept. Never store a local time without its zone.

Should I store a phone number as a number?

No. A phone number is text: it can start with a zero or a plus sign, contain separators, and is never added or multiplied. A numeric column drops the leading zero and refuses the plus sign. Use a string column with a length rule.

Next: Part 6, constraints, where two sessions try to book the same slot at the same time and only one of them wins.