Postgres错误："id“列中的空值-在插入操作期间
Postgres错误："id“列中的空值-在插入操作期间
提问于 2016-08-06 10:14:37
我使用postgresql和yii2框架。我收到了一条非常有趣的错误信息：
SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, 1, null, null, null, null, 1, Demo, , , , 1998-01-01, , , , 345345435453453, , , , , 1, , , f, f, f, f, 10, f, 1, f, f, f, null, null, null, 1470477479, 1470477479, null).但是我检查了Insert命令，那里没有"id“列！
INSERT INTO "advertiser" ("languages", "type", "name", "display_name", "title", "about", "birthday", "gender", "country_id", "county_id", "city_id", "city_part", "street", "house_number", "phone", "public_email", "public_url", "motto", "message", "im_facebook", "im_skype", "has_viber", "has_whatsapp", "has_sms_response", "visible_birthday", "is_checked", "status", "version", "user_id", "created_at", "updated_at") VALUES (NULL, 1, 'Demo', '', '', '', '1998-01-01', 1, NULL, NULL, NULL, '', '', '', '345345435453453', '', '', '', '', '', '', FALSE, FALSE, FALSE, FALSE, FALSE, 10, NULL, 1, 1470477479, 1470477479) RETURNING "id"所以我真的无法理解这个错误信息。我不认为Postgres或Yii试图插入一个空ID值或什么的。
顺便说一句，你可以找到这个结构
Table "public.advertiser"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------------+------------------------+---------------------------------+----------+--------------+-------------
id | integer | not null | plain | |
user_id | integer | | plain | |
country_id | integer | | plain | |
county_id | integer | | plain | |
city_id | integer | | plain | |
district_id | integer | | plain | |
type | smallint | | plain | |
name | character varying(255) | not null | extended | |
display_name | character varying(255) | default NULL::character varying | extended | |
title | character varying(255) | default NULL::character varying | extended | |
about | text | | extended | |
birthday | date | not null | plain | |
city_part | character varying(255) | default NULL::character varying | extended | |
street | character varying(255) | default NULL::character varying | extended | |
house_number | character varying(20) | default NULL::character varying | extended | |
phone | character varying(15) | not null | extended | |
public_email | character varying(255) | default NULL::character varying | extended | |
public_url | character varying(255) | default NULL::character varying | extended | |
motto | character varying(255) | default NULL::character varying | extended | |
message | text | | extended | |
gender | smallint | not null default 1 | plain | |
im_facebook | character varying(255) | default NULL::character varying | extended | |
im_skype | character varying(255) | default NULL::character varying | extended | |
has_viber | boolean | not null default false | plain | |
has_whatsapp | boolean | not null default false | plain | |
has_sms_response | boolean | not null default false | plain | |
visible_birthday | boolean | not null default false | plain | |
status | smallint | not null default 10 | plain | |
is_checked | boolean | not null default false | plain | |
geo_latitude | double precision | | plain | |
geo_longitude | double precision | | plain | |
languages | integer[] | | extended | |
created_at | integer | | plain | |
updated_at | integer | | plain | |
version | bigint | default 0 | plain | |
Indexes:
"advertiser_pkey" PRIMARY KEY, btree (id)你的建议是什么？我应该在哪里找这个问题？
回答 7
Stack Overflow用户
回答已采纳
发布于 2016-08-06 10:17:00
您没有为id插入一个值。因为您没有显式地设置它，所以隐式地给出了一个null值，这当然不是主键列的有效值。您可以通过将该列定义为serial而不是普通的旧integer来避免这种情况，并将所有的重担留给数据库。
Stack Overflow用户
发布于 2018-06-28 12:19:43
serial关键字是在解析时展开的，以后不能看到。
在Postgresql 10版本中，有以下备选方案：
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY它应该符合SQL标准，因此与Oracle兼容。
有关更多细节，请参见此博客。
Stack Overflow用户
发布于 2019-11-21 10:31:52
在将PostgreSQL10转储读入9.6数据库服务器后发生在我身上。之后，自动创建顺序ID的序列丢失了.
这可以这样显示(在psql中)：
SELECT column_name
, column_default
FROM information_schema.columns
WHERE table_name = 'django_migrations'
ORDER BY
ordinal_position;其中django_migrations是表名。它应该显示出这样的东西：
column_name | column_default
-------------+-----------------------------------------------
id | nextval('django_migrations_id_seq'::regclass)
[...]如果'column_default‘中的值为空，则序列将丢失。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38803122
复制相关文章
相似问题
腾讯云开发者
