Re: Fast AT ADD COLUMN with DEFAULTs

On 10/6/16 11:01 AM, Tom Lane wrote:
> Something based on missing_value/absent_value could work for me too.
> If we name it something involving "default", that definitely increases
> the possibility for confusion with the regular user-settable default.
>
> Also worth thinking about here is that the regular default expression
> affects what will be put into future inserted rows, whereas this thing
> affects the interpretation of past rows. So it's really quite a different
> animal. That's kind of leading me away from calling it creation_default.

There's actually another use case here that's potentially extremely
valuable for warehousing and other "big data": compact representation of
a default value.

The idea here is that if you have a specific value for a field that
makes up a very large portion of your data, you'd really like to be able
to represent that value *in each row* with something like a bit (such as
we currently do for NULLs).

What I'd expect to see in the real world (once users figure this hack
out) would be:

CREATE TABLE ...(
...
-- skip field_a so we can handle all it's common values
);

INSERT INTO
...
SELECT
...
WHERE field_a IS NOT DISTINCT FROM 'really common value'
;

ALTER TABLE
ADD field_a ... NOT NULL DEFAULT 'really common value'
;

-- load rest of the data

That would have the effect of storing all those really common values
with a single bit.

What we'd ultimately want is some kind of catalog versioning so that we
knew what was in place when each tuple was created; that would allow for
changing these things over time without forcing a full rewrite.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461