SQL Postgres 错误：“id”列中的空值 - 在插入操作期间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38803122/
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
Postgres error: null value in column "id" - during insert operation
提问by Dabagab
I use postgresql and yii2 framework. Well I got a very interesting error message:
我使用 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).
But I checked my Insert command, and there is not "id" column there!
但是我检查了我的插入命令，那里没有“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"
So I really cannot understand this error message. I do not find that the Postgres or Yii try to insert a null ID value or what.
所以我真的无法理解这个错误信息。我没有发现 Postgres 或 Yii 尝试插入空 ID 值或什么。
By the way here you can find the structure
顺便说一下，你可以在这里找到结构
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)
What is your advice? Where should I looking for the problem?
你的建议是什么？我应该在哪里寻找问题？
回答by Mureinik
You aren't inserting a value for id. Since you don't explicitly set it, it's implicitly given a nullvalue, which is, of course, not a valid value for a primary key column. You can avoid this entire situation by defining this column as serialinstead of a plain old integer, and leave all the heavy lifting to the database.
您没有为 插入值id。由于您没有显式设置它，它被隐式地赋予了一个null值，当然，这不是主键列的有效值。您可以通过将此列定义为serial而不是普通的 old来避免整个情况integer，并将所有繁重的工作留给数据库。
回答by Stephane
The serialkeyword is expanded at parse time and cannot be seen afterward.
该serial关键字在分析时扩大，此后不能看到。
From the version Postgresql 10there is the following alternative:
从版本Postgresql 10有以下替代方案：
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
It is supposed to conform to the SQL standard and thus be compatible with Oracle.
它应该符合 SQL 标准，因此与 Oracle 兼容。
See this blogfor more details.
有关更多详细信息，请参阅此博客。
回答by Markus
Happened to me after reading a PostgreSQL10 dump into 9.6 data base server. After that, the sequences that are to auto create sequential IDs where lost.
在将 PostgreSQL10 转储读取到 9.6 数据库服务器后发生在我身上。之后，将自动创建序列 ID 的序列丢失。
This can be shown like this (in psql):
这可以像这样显示（在 psql 中）：
SELECT column_name
, column_default
FROM information_schema.columns
WHERE table_name = 'django_migrations'
ORDER BY
ordinal_position;
where django_migrationsis the table name. It should show something like this:
django_migrations表名在哪里。它应该显示如下内容：
column_name | column_default
-------------+-----------------------------------------------
id | nextval('django_migrations_id_seq'::regclass)
[...]
If the value in 'column_default' is empty, then the sequence is lost.
如果 'column_default' 中的值为空，则序列丢失。
回答by Poutrathor
If you can't change to serialbecause reasons like client, management, db rights...
如果serial由于客户、管理、数据库权限等原因而无法更改为...
The database is probably using sequence.
数据库可能正在使用sequence.
Here are what to know about it : SELECT nextval('seq_nuu_filtreelement')
以下是有关它的信息： SELECT nextval('seq_nuu_filtreelement')
To read :
阅读 ：
- http://www.neilconway.org/docs/sequences/
- https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
- http://www.neilconway.org/docs/sequences/
- https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
I did not manage to make pg_catalog.pg_get_serial_sequence('schema.table', 'id')work.
我没能完成pg_catalog.pg_get_serial_sequence('schema.table', 'id')工作。
Thus I found the sequences in my database explorer and use the command :
因此，我在我的数据库资源管理器中找到了序列并使用命令：
SELECT nextval('seq_table_name')
SELECT nextval('seq_table_name')
回答by vipin cp
Change your existing primary key to serial. Read this for changing it
将您现有的主键更改为serial. 阅读本文以更改它
