Обсуждение: Behaviour adding a column with and without a default (prior to PG11)
Hi, The documentation (https://www.postgresql.org/docs/10/ddl-alter.html) clearly states that adding a column with a defaultrequires updating all the rows in the table, to store the new column value (this is prior to PG11, obviously). I can understand why this behaviour was required when adding a not null column, but for a nullable column, it also seemsreasonable to me that existing rows would not be updated and the default added to apply to new rows only. Obviously the desired effect of adding the column default without rewriting all the rows in the table can be achieved byadding the nullable column first with no default, then changing the default, but out of curiosity; was there a specifictechnical reason for the behaviour described above, or was it a conscious design choice? Many thanks, Joe. [Bamboo Limited | 1st Floor | Grenville House | Nelson Gate | Southampton | SO15 1GX ::: www.bambooloans.com] This email message is intended only for the addressee(s) and contains information that may be confidential and/or copyright.If you are not the intended recipient please notify the sender by reply email and immediately delete this email.Use, disclosure or reproduction of this email by anyone other than the intended recipient(s) is strictly prohibited.Although, all emails are scanned for viruses, no representation is made that this email or any attachments arefree of viruses. Virus scanning is recommended and is the responsibility of the recipient. Help protect our environment by only printing this email if absolutely necessary.
Joe Horsnell <Joe@bambooloans.com> writes: > The documentation (https://www.postgresql.org/docs/10/ddl-alter.html) clearly states that adding a column with a defaultrequires updating all the rows in the table, to store the new column value (this is prior to PG11, obviously). > Obviously the desired effect of adding the column default without rewriting all the rows in the table can be achieved byadding the nullable column first with no default, then changing the default, but out of curiosity; was there a specifictechnical reason for the behaviour described above, or was it a conscious design choice? We read the SQL spec as requiring this behavior. regards, tom lane
Thanks for the quick response Tom.
Cheers,
Joe.
On 30/09/2019, 22:19, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Joe Horsnell <Joe@bambooloans.com> writes:
> The documentation (https://www.postgresql.org/docs/10/ddl-alter.html) clearly states that adding a column with a
defaultrequires updating all the rows in the table, to store the new column value (this is prior to PG11, obviously).
> Obviously the desired effect of adding the column default without rewriting all the rows in the table can be
achievedby adding the nullable column first with no default, then changing the default, but out of curiosity; was there
aspecific technical reason for the behaviour described above, or was it a conscious design choice?
We read the SQL spec as requiring this behavior.
regards, tom lane
[Bamboo Limited | 1st Floor | Grenville House | Nelson Gate | Southampton | SO15 1GX ::: www.bambooloans.com]
This email message is intended only for the addressee(s) and contains information that may be confidential and/or
copyright.If you are not the intended recipient please notify the sender by reply email and immediately delete this
email.Use, disclosure or reproduction of this email by anyone other than the intended recipient(s) is strictly
prohibited.Although, all emails are scanned for viruses, no representation is made that this email or any attachments
arefree of viruses. Virus scanning is recommended and is the responsibility of the recipient.
Help protect our environment by only printing this email if absolutely necessary.
Hi Tom, Just thinking about this further, there are other areas where Postgres (correctly, IMO) deviates from the SQL spec and clarifiesthat in the docs. For example, https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL states that for a NUMERICwith no precision specified, "The SQL standard requires a default scale of 0, i.e., coercion to integer precision.We find this a bit useless.", so instead Postgres treats that as arbitrary precision (up to the implementationlimit). Here, the difference in behaviour (ie of adding a nullable column with no default and then changing the default vs addinga nullable with a default) is due to adherence to the spec, but there is no clarifying comment in the docs saying so.Would you consider a documentation patch to clarify this point? Thanks again, Joe. PS. Apologies for the annoying disclaimer on my initial email, I tried to stop it! On 30/09/2019, 22:19, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: Joe Horsnell <Joe@bambooloans.com> writes: > The documentation (https://www.postgresql.org/docs/10/ddl-alter.html) clearly states that adding a column with a defaultrequires updating all the rows in the table, to store the new column value (this is prior to PG11, obviously). > Obviously the desired effect of adding the column default without rewriting all the rows in the table can be achievedby adding the nullable column first with no default, then changing the default, but out of curiosity; was there aspecific technical reason for the behaviour described above, or was it a conscious design choice? We read the SQL spec as requiring this behavior. regards, tom lane [Bamboo Limited | 1st Floor | Grenville House | Nelson Gate | Southampton | SO15 1GX ::: www.bambooloans.com] This email message is intended only for the addressee(s) and contains information that may be confidential and/or copyright.If you are not the intended recipient please notify the sender by reply email and immediately delete this email.Use, disclosure or reproduction of this email by anyone other than the intended recipient(s) is strictly prohibited.Although, all emails are scanned for viruses, no representation is made that this email or any attachments arefree of viruses. Virus scanning is recommended and is the responsibility of the recipient. Help protect our environment by only printing this email if absolutely necessary.