是否可以定义一个不为null且没有默认值且在更新时没有特殊行为的timestamp列? [英] Is it possible to define a timestamp column that is not null and has no default and no special behavior on update?

问题描述

当我创建带有时间戳列的表时，该列在更新CURRENT_TIMESTAMP时被神奇地定义为NOT NULL DEFAULT CURRENT_TIMESTAMP.到目前为止，我不知道这有多么怪异，我想将其更改为没有默认值，也没有特殊的更新时"行为.

When I create a table with a timestamp column, that column is magically defined as NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP. Ignoring how weird this is so far, I would like to change it to have no default and no special "on update" behavior.

我发现，如果我将列更改为NULL，则默认设置神奇地设置为NULL，而更新时"神奇地消失了.很好，但是我希望该列不为空.当我将其改回时，默认设置和更新时"(设置为CURRENT_TIMESTAMP)都会重新出现.

I found that if I change the column to be NULL, the default is magically set to NULL and the "on update" magically disappears. This is great, however I would like the column to be NOT NULL. When I change it back, both the default and "on update" (set to CURRENT_TIMESTAMP) magically reappear.

我知道我可以使用datetime代替时间戳，但是我对时间戳感兴趣，因为它可以识别时区(在Postgres中好像是带时区的时间戳").

I know I could use datetime instead of timestamp, but I'm interested in timestamp because it is timezone-aware (seems to be like "timestamp with time zone" in Postgres).

推荐答案

时间戳列是一种特殊情况.参见此处:默认情况下，TIMESTAMP列不为空，不能包含NULL值，分配NULL会分配当前时间戳.

Timestamp columns are a special case. See here: By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp.

有关更多详细信息，请阅读数据类型默认值值.

For more detailed information read up on Data Type Default Values.

特别是在不以严格模式运行时，这种情况适用.如果以严格模式运行，则插入NULL会引发错误.

Specifically that situation applies when not running in strict mode. If running in strict mode, inserting a NULL will throw an error.

这应该照顾它:

ALTER TABLE tableName ALTER COLUMN columnName DROP DEFAULT;

如果这行不通，那么这样做应该为您保留默认设置(轻松覆盖)，但是删除ON UPDATE:

If that doesn't work, doing this is supposed to leave you with the default (easily overwritten) but remove the ON UPDATE:

ALTER TABLE tableName CHANGE columnName columnName NOT NULL DEFAULT CURRENT_TIMESTAMP;

请注意重复的列名.

这篇关于是否可以定义一个不为null且没有默认值且在更新时没有特殊行为的timestamp列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！