Foreign key BIGSERIAL NULL Postgres

Question

I can not insert a record in the POSTGRES database, I want the foreign key to be null.

My table:

CREATE TABLE sad_avaliado (
    id                BIGSERIAL NOT NULL,
    tenant_id         INT8 NOT NULL,
    funcionario_id    BIGSERIAL NOT NULL,
    epoca_id          BIGSERIAL NOT NULL,
    cca_id            BIGSERIAL,
    avaliador_id      BIGSERIAL NOT NULL,
    apagado           boolean NOT NULL,
    PRIMARY KEY (id)
 );
alter table sad_avaliado add constraint sad_funcionario_fkey foreign key (funcionario_id) references sad_funcionario;
alter table sad_avaliado add constraint sad_epoca_fkey foreign key (epoca_id) references sad_epoca;
alter table sad_avaliado add constraint sad_cca_fkey foreign key (cca_id) references sad_cca;
alter table sad_avaliado add constraint sad_avaliador_fkey foreign key (avaliador_id) references sad_avaliador;

My SQL Insert:

INSERT INTO public.sad_avaliado(
id, tenant_id, funcionario_id, epoca_id, cca_id, avaliador_id, apagado)
VALUES (1, 1, 1, 1, null, 1, false);

My Error:

ERROR:  null value in column "cca_id" violates not-null constraint

Answer 1

The foreign key references for BIGSERIAL should use BIGINT:

CREATE TABLE sad_avaliado (
    id                BIGSERIAL NOT NULL,
    tenant_id         INT8 NOT NULL,
    funcionario_id    BIGINT NOT NULL,
    epoca_id          BIGINT NOT NULL,
    cca_id            BIGINT,
    avaliador_id      BIGINT NOT NULL,
    apagado           boolean NOT NULL,
    PRIMARY KEY (id)
 );

I think this is the one exception to the notion that foreign key references should have the same type as the primary key. I mean, the underlying type is the same, but BIGSERIAL is used to specify that it is auto-incrementing (other databases use a separate keyword such as auto_increment or identity).