Postgres error: null value in column "id" - during insert operation

Question

I use postgresql and yii2 framework. Well I got a very interesting error message:

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!

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.

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?

score 43 · Accepted Answer · answered Aug 06 '16 at 10:17

43

You aren't inserting a value for id. Since you don't explicitly set it, it's implicitly given a null value, which is, of course, not a valid value for a primary key column. You can avoid this entire situation by defining this column as serial instead of a plain old integer, and leave all the heavy lifting to the database.

answered Aug 06 '16 at 10:17

Mureinik

297,002
52
306
350

what if I have already the column as serial? What could it be? – kvothe838 May 05 '22 at 12:07
Or if your id column already has a sequence set, try inserting with the keyword DEFAULT instead of NULL. – Marty Nov 17 '22 at 11:16

score 11 · Answer 2 · answered Jun 28 '18 at 12:19

11

The serial keyword is expanded at parse time and cannot be seen afterward.

From the version Postgresql 10 there is the following alternative:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

It is supposed to conform to the SQL standard and thus be compatible with Oracle.

See this blog for more details.

answered Jun 28 '18 at 12:19

Stephane

11,836
25
112
175

what is the difference between "by default" and "always" ? – vikrant Aug 09 '20 at 10:34

Markus · Answer 3 · 2020-11-05T17:59:50.113

6

Happened to me after reading a PostgreSQL10 dump into 9.6 database server. After that, the sequences that are to auto-create sequential IDs were lost.

This can be shown like this (in psql):

SELECT  column_name
,       column_default
FROM    information_schema.columns
WHERE   table_name = 'django_migrations'
ORDER BY 
    ordinal_position;

where django_migrations is the table name. It should show something like this:

 column_name |                column_default                 
-------------+-----------------------------------------------
 id          | nextval('django_migrations_id_seq'::regclass)
[...]

If the value in 'column_default' is empty, then the sequence is lost.

edited Nov 05 '20 at 17:59

answered Nov 21 '19 at 10:31

Markus

2,412
29
28

how did you fix the issue? – Aldo Okware Sep 17 '20 at 20:42
I use a Mac, and there is Postgresql.app (www.postgresqlapp.com) which allows to install mutliple versions in parallel, so I can switch the DB server version on the fly. There should be a solution for Linux (and maybe even Windows), too – Markus Sep 18 '20 at 06:28

score 4 · Answer 4 · edited May 23 '17 at 10:31

4

Change your existing primary key to serial . Read this for changing it

Changing primary key int type to serial

edited May 23 '17 at 10:31

Community

1
1

answered Aug 06 '16 at 10:26

Vipin CP

3,642
3
33
55

score 4 · Answer 5 · answered Feb 15 '19 at 14:19

If you can't change to serial because reasons like client, management, db rights...

The database is probably using sequence.

Here are what to know about it : SELECT nextval('seq_nuu_filtreelement')

To read :

I did not manage to make pg_catalog.pg_get_serial_sequence('schema.table', 'id') work.

Thus I found the sequences in my database explorer and use the command :

SELECT nextval('seq_table_name')

score 1 · Answer 6 · answered May 20 '21 at 11:35

1

If for some reason, you cannot change your schema to change the type of your id column from whatever it is presently to serialize; then, you may insert the id along with the rest of your values like so:

(select max(id) + 1 from table)

answered May 20 '21 at 11:35

Asma Rahim Ali Jafri

1,173
2
15
22

Believe you mean SERIAL, not `serialize` – OneCricketeer Jun 25 '21 at 22:20
1

Unfortunately this isn't atomic. That is, two different requests trying to do this at the same time could come up with the same ID and then one will fail. – jwadsack May 06 '22 at 23:42
@jwadsack what workaround do you suggest? – Asma Rahim Ali Jafri May 07 '22 at 17:41
1

@asma-rahim-ali-jafri as @poutrathor stated, if you can't change the column type to `serial`, add a `sequence`, which is an atomic counter, and use `nextval()` to ensure that every insert gets a distinct row ID. – jwadsack May 08 '22 at 01:24
@jwadsack thank you for pointing out such a huge flaw and helping me learn something new!! – Asma Rahim Ali Jafri May 08 '22 at 10:54

score 1 · Answer 7 · answered Nov 17 '22 at 12:10

Or if your id column already has a sequence set, try inserting with the keyword DEFAULT instead of NULL.

In MySQL, NULL would use the default value for a column, but not so in Postgres.

Your example query would be changed to this:

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 (DEFAULT, 1, 'Demo', '', '', '', '1998-01-01', 1, NULL, NULL, NULL, '', '', '', '345345435453453', '', '', '', '', '', '', FALSE, FALSE, FALSE, FALSE, FALSE, 10, NULL, 1, 1470477479, 1470477479) RETURNING "id"

This answer shows using the DEFAULT keyword: insert DEFAULT values

Postgres error: null value in column "id" - during insert operation

7 Answers7

Linked