Good enough ten-second sum types for Postgres

Evan Silberman

I’m working on something where I wasn’t collecting timestamps before and now I am.

begin;

create type epoch as enum ('zero', 'one');

alter table responses
    add column filled_at timestamp with time zone,
    add column epoch epoch not null default 'zero',
    add constraint epoch_timestamp
    check ((epoch = 'zero' and filled_at is     null) or
           (epoch = 'one'  and filled_at is not null));

-- (all the old rows get epoch = 'zero' and null timestamp)

alter table responses 
    alter column epoch set default 'one',
    alter column filled_at set default now();

commit;

Not easily extensible, not normalized, probably illegal, basically adequate, much better than picking an arbitrary date as a sentinel for “a long time ago” or leaving the default now() off of the timestamp column.