PostgreSQL: Not null violation: 7 ERROR: null value in column "id" violates not-null constraint

Question

doctrine

     /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var string $entity
     *
     * @ORM\Column(name="entity", type="string", length=255)
     */
    protected $entity;

    /**
     * @var string $alias
     *
     * @ORM\Column(name="alias", type="string", length=255)
     */
    protected $alias;

acme_search_item table

acme_test=# \d acme_search_item;
                             Table "public.acme_search_item"
   Column   |              Type              | Collation | Nullable |         Default
------------+--------------------------------+-----------+----------+-------------------------
 id         | integer                        |           | not null |
 entity     | character varying(255)         |           | not null |
 alias      | character varying(255)         |           | not null |
 record_id  | integer                        |           |          |
 title      | character varying(255)         |           |          | NULL::character varying
 weight     | numeric(21,8)                  |           | not null | '1'::numeric
 changed    | boolean                        |           | not null |
 created_at | timestamp(0) without time zone |           | not null |
 updated_at | timestamp(0) without time zone |           | not null |
Indexes:
    "acme_search_item_pkey" PRIMARY KEY, btree (id)
    "idx_entity" UNIQUE, btree (entity, record_id)
    "idx_alias" btree (alias)
    "idx_entities" btree (entity)
Referenced by:
    TABLE "acme_search_index_datetime" CONSTRAINT "fk_651ddb126f525e" FOREIGN KEY (item_id) REFERENCES acme_search_item(id) ON DELETE CASCADE
    TABLE "acme_search_index_text" CONSTRAINT "fk_67665f0c126f525e" FOREIGN KEY (item_id) REFERENCES acme_search_item(id)
    TABLE "acme_search_index_integer" CONSTRAINT "fk_c52b2786126f525e" FOREIGN KEY (item_id) REFERENCES acme_search_item(id) ON DELETE CASCADE
    TABLE "acme_search_index_decimal" CONSTRAINT "fk_c5d93f1e126f525e" FOREIGN KEY (item_id) REFERENCES acme_search_item(id) ON DELETE CASCADE

acme_search_item_id_seq

acme_test=# \d acme_search_item_id_seq
                Sequence "public.acme_search_item_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

SQL

acme_test=# INSERT INTO acme_search_item (id,entity,ALIAS,record_id,title,weight,changed,created_at,updated_at) VALUES (DEFAULT,'Pintushi\\Bundle\\OrganizationBundle\\Entity\\Organization','acme_organization','1','tt','1','0','2019-03-18 17:15:57','2019-03-18 17:15:57');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, Acme\\Bundle\\OrganizationBundle\\Entity\\Organization, acme_organization, 1, tt, 1.00000000, f, 2019-03-18 17:15:57, 2019-03-18 17:15:57).

acme_test=# INSERT INTO acme_search_item (id,entity,ALIAS,record_id,title,weight,changed,created_at,updated_at) VALUES (1,'Acme\\Bundle\\OrganizationBundle\\Entity\\Organization','acme_organization','1','tt','1','0','2019-03-18 17:15:57','2019-03-18 17:15:57');
INSERT 0 1

Edit

acme_test=# INSERT INTO acme_search_item (entity,ALIAS,record_id,title,weight,changed,created_at,updated_at) VALUES ('Acme\\Bundle\\OrganizationBundle\\Entity\\Organization','acme_organization','1','tt','1','0','2019-03-18 17:15:57','2019-03-18 17:15:57');;
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, Acme\\Bundle\\OrganizationBundle\\Entity\\Organization, acme_organization, 1, tt, 1.00000000, f, 2019-03-18 17:15:57, 2019-03-18 17:15:57).

Question

I use the DEFAULT key word, it is supposed be next id, however it is wrong as you can see. I am using PostgreSQL 10.4. I fount out somehow there is no extval('*_id_seq'::regclass) defined. I use doctrine to define the database structure above as the document identifier-generation-strategies says.

Answer 1

Make sure your id column has a default:

ALTER TABLE acme_search_item
    ALTER COLUMN id SET DEFAULT nextval('acme_search_item_id_seq');

You can view current defaults in the information schema tables:

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