How to fix Null value not allowed in PostgreSQL

ERROR: null value in column "username" violates not-null constraint

\d your_table_name

SELECT column_name, is_nullable, data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name'
ORDER BY ordinal_position;

-- Wrong - missing "username" and "email"
INSERT INTO users (id, created_at)
VALUES (1, NOW());

-- Correct - all NOT NULL columns included
INSERT INTO users (id, username, email, created_at)
VALUES (1, 'john_doe', '[email protected]', NOW());

-- Before (may fail if column contains NULL)
UPDATE users
SET username = COALESCE(username, 'unknown')
WHERE id = 1;

-- Or use CASE for conditional logic
UPDATE users
SET username = CASE
  WHEN username IS NULL THEN 'default_user'
  ELSE username
END
WHERE id = 1;

-- Correct - provides default for existing rows
ALTER TABLE users
ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'active';

-- Update any NULL values first if migrating
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Then add the constraint
ALTER TABLE users
ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL);

// Wrong - undefined gets treated as NULL
const insertUser = async (user: any) => {
  const result = await db.query(
    'INSERT INTO users (id, username, email) VALUES ($1, $2, $3)',
    [user.id, user.username, user.email] // email might be undefined
  );
};

// Correct - validate before inserting
const insertUser = async (user: User) => {
  if (!user.email) throw new Error('Email is required');
  const result = await db.query(
    'INSERT INTO users (id, username, email) VALUES ($1, $2, $3)',
    [user.id, user.username, user.email]
  );
};