Is there a way to set default values of columns that are NOT NULL in postgres?

Question

In PostgreSQL, when a column is marked as NOT NULL and no value is provided for it during insertion, an error is thrown. However, in MySQL, if no value is provided, the default value is set based on the column's type, such as '' for string values, 0 for integers, 0.00 for floats(scale: 2), etc. It's important to note that even in MySQL, this behavior occurs irrespective of whether a default value is explicitly mentioned.

Example:

ALTER TABLE errors ADD COLUMN user_id INT NOT NULL

In MySQL, it will insert 0 if no value is provided, whereas PostgreSQL will throw an error.

Is there a way or a database setting in PostgreSQL to achieve similar behavior, where default values are automatically set for columns marked as NOT NULL?

I am using TypeORM in nestjs and I don't want to provide default value for each column explicitly like this

@Column('int', { default: 0 })
user_id: number;

EDIT 1: I need something like this: STRICT_TRANS_TABLES

EDIT 2: I found how MySQL does it and just need it for postgres or would like to know if it is not possible in postgres. Implicit Default Handling in MYSQL

Answer 1

You can create an event trigger that fires when a new table is created and sets all the defaults that you need. Something like this:

CREATE OR REPLACE FUNCTION public.set_defaults()
    RETURNS EVENT_TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    _row    RECORD;
BEGIN
    FOR _row IN
        SELECT pg_class.relname AS table_name
            ,  nspname AS schema_name
            ,  column_name
            ,  data_type
        FROM pg_event_trigger_ddl_commands()
            JOIN pg_class ON pg_class.oid = pg_event_trigger_ddl_commands.objid
            JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
            JOIN information_schema.columns ON table_schema = nspname
                                            AND table_name = pg_class.relname
        WHERE   command_tag = 'CREATE TABLE'
        AND     is_nullable = 'NO'
        AND     column_default IS NULL
    LOOP
        -- Set default value based on data type
        IF _row.data_type IN ('integer', 'bigint', 'smallint') THEN
            EXECUTE FORMAT('ALTER TABLE %I.%I ALTER COLUMN %I SET DEFAULT 0;'
                , _row.schema_name
                , _row.table_name
                , _row.column_name);
        ELSIF _row.data_type IN('date', 'timestamp without time zone', 'timestamp with time zone') THEN
            EXECUTE FORMAT('ALTER TABLE %I.%I ALTER COLUMN %I SET DEFAULT NOW();'
                , _row.schema_name
                , _row.table_name
                , _row.column_name);
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER set_defaults ON ddl_command_end
EXECUTE FUNCTION public.set_defaults();

CREATE TABLE public.foo
(
    id INT       NOT NULL,
    ts TIMESTAMP NOT NULL,
    content text
);

 -- success, using all columns and DEFAULT:
INSERT INTO public.foo(id, ts, content)
VALUES(DEFAULT, DEFAULT, 'my content')
RETURNING *;

 -- success, only the columns that you need:
INSERT INTO public.foo(content)
VALUES('my content')
RETURNING *;

-- fail, using all columns and NULLs:
INSERT INTO public.foo(id, ts, content)
VALUES(null, null, 'my content')
RETURNING *;

This trigger fires on the ddl_command_end event, when the new table exists and can be altered. Check the data types and default values, to make sure they do what you want them to do. You can add additional elsif controls when needed.

You have to install this function and the event trigger in each database that needs this behavior. Usually just your development database, for test and production databases you (should) have a complete installation script.

As you can see in the tests, a NULL will still violate a NOT NULL constraint. Only the usage of DEFAULT or the absence of the column in the INSERT will result the usage of your default value. And of course it also works like this for UPDATE statements.

Answer 2

If the column is NOT NULL, it has to have a default value.

Why not adding a: DEFAULT negative number or zero

PL/pgSQL query:

ALTER TABLE errors ADD COLUMN user_id INT DEFAULT -1;

or

ALTER TABLE errors ADD COLUMN user_id INT DEFAULT 0;

or

ALTER TABLE errors ADD COLUMN user_id INT NOT NULL DEFAULT -1;

Example: db<>fiddle

For details check documentation: postgresql-16

Answer 3

Quoting the doc:

If there is no default for a column, then the default is null.

And unfortunately there's no setting that would change this behaviour globally, to a type-specific default of empty-but-not-null value. If that's what you need, you have to add an alter table..alter column..set default each time on db end: demo

ALTER TABLE errors 
  ALTER COLUMN user_id SET DEFAULT 0,
  ALTER COLUMN name SET DEFAULT '',
  ALTER COLUMN salary SET DEFAULT 0.0,
  ALTER COLUMN created_at SET DEFAULT '-infinity'::timestamptz,
  ALTER COLUMN duration SET DEFAULT '[-infinity,infinity)'::tstzrange;

Or each time in your column options, like you showed

@Column({type:'int', default: 0, nullable: false })
user_id: number;

In both cases, if you don't like typing it all out by hand, you should be able to set up your IDE and/or CI/CD pipelines to add that on the fly.

If you want to add it in bulk, you can iterate over information_schema.columns, spot the ones where is_nullable='NO' and check their column_default. You can loop over that and use dynamic SQL to add your specific empty-but-not-null value as the default. The script can be integrated as a post-step in your migrations, so that when you add new models/tables, they also get processed.

select * 
from information_schema.columns
where is_nullable='NO' 
  and column_default is null
  and table_name='errors';

Answer 4

You have to provide a column default explicitly if you don't want it to be NULL.