PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
  3. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search