postgresql PG::NotNullViolation：错误：“id”列中的空值违反了非空约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24298171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint
提问by user938363
Here is the error when saving a record into postgres database on rails 3.2.12 & pg 9.3:
这是在 rails 3.2.12 & pg 9.3 上将记录保存到 postgres 数据库时的错误：
ActiveRecord::StatementInvalid (PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint
: INSERT INTO "sw_module_infox_module_infos" ("about_controller", "about_init", "about_log", "about_misc_def", "about_model", "about_onboard_data", "about_subaction", "about_view", "about_workflow", "active", "api_spec", "category_id", "created_at", "last_updated_by_id", "module_desp", "name", "submit_date", "submitted_by_id", "updated_at", "version", "wf_state") VALUES (, , , , , , , , , , , , , , , , , , , , ) RETURNING "id"):
activerecord (3.2.12) lib/active_record/connection_adapters/postgresql_adapter.rb:1166:in `get_last_result'
activerecord (3.2.12) lib/active_record/connection_adapters/postgresql_adapter.rb:1166:in `exec_cache'
The table was working fine until now (saved about 50 records before the error today). After opening the pg table in pgadmin. we found the idon the table is integer. We also found Idon other table is serial. It seems that the id should be serialso it can auto-increment. If it is, then how to convert a id column to serialfrom integer? If it is not, then how to fix this problem?
到目前为止，该表工作正常（在今天出现错误之前保存了大约 50 条记录）。在 pgadmin 中打开 pg 表后。我们发现id桌子上的是integer。我们还在Id其他桌子上发现了serial. 似乎 id 应该是serial这样它可以自动增加。如果是，那么如何将 id 列转换为serialfrom integer？如果不是，那么如何解决这个问题？
回答by pozs
The data types
smallserial,serialandbigserialare not true types, but merely a notational convenience for creating unique identifier columns (similar to theAUTO_INCREMENTproperty supported by some other databases).
的数据类型
smallserial，serial并且bigserial不是真正的类型，而仅仅是用于创建的唯一标识符列（类似于一个标记方便AUTO_INCREMENT通过一些其它数据库支持的属性）。
If your column is an integer, you cannot convertit to serial, but you can mimic, what PostgreSQL would have done, just like you created your table with a serial:
如果您的列是integer，则无法将其转换为serial，但您可以模仿 PostgreSQL 所做的事情，就像您使用序列号创建表一样：
CREATE SEQUENCE tablename_colname_seq;
ALTER TABLE tablename ALTER COLUMN colname SET DEFAULT nextval('tablename_colname_seq'::regclass);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
