ALTER TABLE & ALTER TYPES

2022-06-18 02:03:13

摘要：主要的内容是alter, alter table, alter type, length, postgresql 9.2, rewrite, reindex

下面有三个补丁供大家使用，它们是由 Robert Hass 提交的。

2月7日的补丁

添加一个 numeric 类型 typmode coercisiions 的转换函数
这将会使得 alter table操作可以跳过表和索引重建时字段更改为不受约束的数字，或者当这个比例不变时
精度不降低.

noah misch, 提供了语法上的修改和一个OID修改

补丁二

添加了一个 varbit 类型的 typmod coercisions 转换函数
这将会使得 alter table 操作可以跳过表和索引重建时这个新类型 varbit 不受约束，或者允许位减少

最后的补丁

添加一个 various temporal typmod coercisions 的转换函数
可以使得 alter table 在一些情况下跳过表和索引重建

接下来通过具体的例子，我们来查看它的优点。

numeric(x, y) 到 numeric(x+, y) -- 具体的例子是从numeric(8,3) 到 numeric(10, 3)
任意 numeric 到 numeric 没有指定精度和没有指定比例/范围如下面的语句
```
alter table z alter column i type numeric;
```

varbit(x) 到 varbit(x+) -- 具体例子从 varbit(8) 到 varbit(10)
任意的 varbit 字段没有长度限制

alter table z alter column i type varbit;

time(x) 到 time(x+) --- 具体例子 time(2) 到 time(3)
任意时间字段到时间字段，没有精度限制

alter table z alter column i type time;

timez(x) 到 timez(x+) -- 具体例子是 timez(2) 到 timez(3)
任意 timetz 到 timetz 字段，精度不限制

alter table z alter column i type timetz;

timestamp(x) 到 timestamp(x+) -- 具体的例子是 timestamp(2) 到 timestamp(3)
任意的 timestamp 到timestamp 没有精度要求

alter table z alter column i type timestamp;

interval(x) 到interval(x+) -- 具体的例子就是从 interval(2) 到 interval(3)
任意的interval 到interval 字段，没有精度限制

alter table z alter column i type interval;

具体的内容参看下面的信息

从 "INTERVAL YEAR" 到

“INTERVAL YEAR”
"INTERVAL DAY"
"INTERVAL HOUR"
"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL YEAR TO MONTH"
"INTERVAL DAY TO HOUR"
"INTERVAL DAT TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL MONTH" 到

"INTERVAL YEAR"
"INTERVAL MONTH"
"INTERVAL DAY"
"INTERVAL HOUR"
"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL YEAR TO MONTH"
"INTERVAL DAY TO HOUR"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL DAY" 到

"INTERVAL DAY"
"INTERVAL HOUR"
"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO HOUR"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL MINUTE" 到

"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL SECOND" 到

"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL YEAR TO MONTH " 到

“INTERVAL YEAR”
"INTERVAL DAY"
"INTERVAL HOUR"
"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL YEAR TO MONTH"
"INTERVAL DAY TO HOUR"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL DAY TO HOUR" 到

“INTERVAL HOUR”
"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO HOUR"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL DAY TO MINUTE" 到

"INTYERVAL MINUTE "
"INTERVAL SECOND"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL DAY TO SECOND" 到

"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL HOUR TO MINUTE" 到

"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO MINUTE"
"INTERVAL    DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL HOUR TO SECOND" 到

"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

从 "INTERVAL MINUTE TO SECOND"

"INTERVAL MINUTE"
"INTERVAL SECOND"
"INTERVAL DAY TO MINUTE"
"INTERVAL DAY TO SECOND"
"INTERVAL HOUR TO MINUTE"
"INTERVAL HOUR TO SECOND"
"INTERVAL MINUTE TO SECOND"

这些数据类型的大部分是非常奇特的，但是他们确实非常有用，而且使用这种转换能力，不需要重写表和索引是一件非常好
的事情，在这里感谢诺亚和罗伯特

码农公寓

相关文章