Unable to insert null into a not null default current_timestamp record of postgresql enterprise

Contribute Bug

AI-generated summary

The user Hiệp_Nguyễn_ngoc reports a PG::NotNullViolation error during rake db:migrate in Discourse 3.2.0 (v3.3.3) when using PostgreSQL 15.3 (EnterpriseDB Advanced Server). The error occurs because the application attempts to insert a null value into columns like first_unread_at that are defined as NOT NULL with a DEFAULT CURRENT_TIMESTAMP. The user applied a workaround by altering the table to enforce NOT NULL and set DEFAULT NOW() for several columns, but many other fields remain problematic. sam notes that the default should already be set in the migration file, suggesting a potential issue with the migration execution or database compatibility.

1

Discourse higher 3.2.0 (v3.3.3,…).
I am using PostgreSQL 15.3 (EnterpriseDB Advanced Server 15.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit.
When I run rake db:migrate, I encounter this error:

PG::NotNullViolation: ERROR: null value in column "first_unread_at" of relation "user_stats" violates not-null constraint (PG::NotNullViolation)
DETAIL: Failing row contains (130, 0, 0, 0, 0, 0, 0, 2025-02-25 03:27:48.934913, null, null, 0, 0, 0, null, 0, 0, 0, null, 0, null, null, null, 0, 0)

I investigated and found out that the reason is the application is trying to insert a null value into a field that is not null with a default current_timestamp .
My solution:

BEGIN;
ALTER TABLE user_stats 
  ALTER COLUMN first_unread_at SET NOT NULL,
  ALTER COLUMN first_unread_at SET DEFAULT NOW();
COMMIT;

BEGIN;
ALTER TABLE user_stats 
  ALTER COLUMN first_unread_pm_at SET NOT NULL,
  ALTER COLUMN first_unread_pm_at SET DEFAULT NOW();
COMMIT;

BEGIN;
ALTER TABLE group_users 
  ALTER COLUMN first_unread_pm_at SET NOT NULL,
  ALTER COLUMN first_unread_pm_at SET DEFAULT NOW();
COMMIT;

But there are many other fields that can’t be inserted, I haven’t been able to find and fix them all using this method.

2

I am confused, we set the sedfault here: