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