A Missing Link in Postgres 11: Fast Column Creation with Defaults
Hmm, something I&#x27;m not understanding from the description of the implementation is how it handles this sequence of operations:<p>1.) Start with a nullable column with some existing rows with null values.<p>2.) Make the column required with default=100. Postgres now marks atthasmissing as true and attmissingval as 100. Rows will be lazy-populated to 100, but aren&#x27;t immediately modified.<p>3.) Make the column nullable again.<p>4.) Add some new null values.<p>5.) Make the column required with default=200.<p>At this point, the right behavior is that all originally-null rows from step #1 have value 100 and all rows added in step #4 have value 200, but that&#x27;s not representable with the atthasmissing&#x2F;attmissingval system. I guess maybe in steps 3 or 5 it needs to just bail out and do a full table rewrite?<p>I was thinking through this problem a while ago and one idea I had was that you could version your schema and have each row remember what the schema version was when the row was created. Then you could lazy-populate any null value by looking up the next default value for the column chronologically in schema history. But I guess maybe that&#x27;s complex enough or would hurt common-case perf enough that Postgres realistically isn&#x27;t ever going to implement it.