Why can't I omit a property with a default value in a NOT NULL column when performing an INSERT-CodePudding

I have the following schema for a table currencies:

CREATE TABLE IF NOT EXISTS public.currencies
(
    currency_id integer NOT NULL DEFAULT nextval('currencies_currency_id_seq'::regclass),
    name text COLLATE pg_catalog."default" NOT NULL,
    abbreviation character varying(10) COLLATE pg_catalog."default" NOT NULL,
    base_value integer NOT NULL,
    prepend_symbol_flg boolean NOT NULL DEFAULT false,
    CONSTRAINT currencies_pkey PRIMARY KEY (currency_id)
)

Note that prepend_symbol_flg is NOT NULL and has a default value.

I have written a REST API with the following INSERT statement in node.js:

pool.query(
      `INSERT INTO currencies (
        name, abbreviation, base_value, prepend_symbol_flg
      ) VALUES ($1, $2, $3, $4) RETURNING *`,
      [name, abbreviation, base_value, prepend_symbol_flg]
)

When I send a POST to the endpoint with the following body:

{
    "name": "copper piece",
    "abbreviation": "cp",
    "base_value": 1
}

Postman gives the following error: null value in column "prepend_symbol_flg" of relation "currencies" violates not-null constraint.

I understand that this is because the body doesn't include a prepend_symbol_flg key-value pair, but isn't this what the default value is for? So that if a value isn't provided, the default value is used instead?

What do I need to do in order to get the default to be used when the user doesn't provide a value for this field?

CodePudding user response：

The default value is used when the column is not specified in the INSERT statement at all. It is not used when a NULL value is passed - which your query does. You'll need to do the defaulting in JS (prepend_symbol_flg ?? false), or create the query string dynamically depending on which fields are present in the JSON body.