Why am I getting error: null value in column when inserting into a table?

Question

I have two tables in postgresql, as follows:

User Table:

CREATE TABLE public.users
(
    user_id integer NOT NULL DEFAULT nextval('users_user_id_seq'::regclass),
    first_name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    last_name character varying(90) COLLATE pg_catalog."default" NOT NULL,
    email citext COLLATE pg_catalog."default" NOT NULL,
    user_password character varying(90) COLLATE pg_catalog."default" NOT NULL,
    business_status boolean NOT NULL,
    phone_number character varying(12) COLLATE pg_catalog."default" NOT NULL,
    bt_id integer,
    bs_id integer,
    is_active boolean NOT NULL,
    is_verified boolean NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (user_id),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT bs_id FOREIGN KEY (bs_id)
        REFERENCES public.business_step (bs_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT bt_id FOREIGN KEY (bt_id)
        REFERENCES public.business_type (bt_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)

Admin Table:

CREATE TABLE public.admin
(
    admin_id integer NOT NULL DEFAULT nextval('admin_admin_id_seq'::regclass),
    user_id integer NOT NULL,
    CONSTRAINT admin_pkey PRIMARY KEY (admin_id),
    CONSTRAINT user_id FOREIGN KEY (user_id)
        REFERENCES public.users (user_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

I am using Nodejs to implement a route in which I can insert a new administrator with the following code:

Code in Queries.js

const insertAdmin = (request, response) => {
    const { user_id } = request.body

    pool.query('INSERT INTO admin (user_id) VALUES ($1)', [user_id], (error, results) => {
        if (error) {
            throw error
        }
        response.status(201).send(`User added with ID: ${result.insertId}`)
    })
}

Code in index.js

app.post('/admins/insert', db.insertAdmin)

When inserting a new administrator using the following curl route:

curl --data "user_id =39" http://localhost:3000/users

I am getting the following error message:

error: null value in column "user_id" of relation "admin" violates not-null constraint at Parser.parseErrorMessage (C:\Users\ealfo\node-api-postgres\node_modules\pg-protocol\dist\parser.js:278:15) at Parser.handlePacket (C:\Users\ealfo\node-api-postgres\node_modules\pg-protocol\dist\parser.js:126:29) at Parser.parse (C:\Users\ealfo\node-api-postgres\node_modules\pg-protocol\dist\parser.js:39:38) at Socket. (C:\Users\ealfo\node-api-postgres\node_modules\pg-protocol\dist\index.js:10:42) at Socket.emit (events.js:315:20) at addChunk (_stream_readable.js:309:12) at readableAddChunk (_stream_readable.js:284:9) at Socket.Readable.push (_stream_readable.js:223:10) at TCP.onStreamRead (internal/stream_base_commons.js:188:23) { length: 265, severity: 'ERROR', code: '23502', detail: 'Failing row contains (12, null).', hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: 'public', table: 'admin', column: 'user_id', dataType: undefined, constraint: undefined, file: 'd:\pginstaller_13.auto\postgres.windows-x64\src\backend\executor\execmain.c', line: '1965', routine: 'ExecConstraints' }

Why does this error message appears? I am new at using NodeJS and PostgreSQL, I apologize if this question has been answered in another post. Nonetheless, I would like to learn more and learn from my mistakes. Thank you in advance.

That's the whole point of this post. I shouldn't be getting this error but its still giving me the error. — Noname

kg99 kg99 · Accepted Answer · 2020-11-19T11:52:17

You will have to add body-parser to your middleWare. Judging by the curl command you will need to parse the body from url.

Something like.

const bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true })); // to pass bodies from url

Full documentation of body-parser library can be found: https://github.com/expressjs/body-parser

Why am I getting error: null value in column when inserting into a table?

1 Answers