How to fix ERROR: 23502: null value in column violates not-null constraint in PostgreSQL

ERROR: 23502: null value in column "user_id" of relation "orders" violates not-null constraint
DETAIL: Failing row contains (null, 100.00, 2024-01-15).

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

INSERT INTO orders (order_total, order_date)
VALUES (99.99, '2024-01-15');
-- Fails if user_id is NOT NULL

INSERT INTO orders (user_id, order_total, order_date)
VALUES (42, 99.99, '2024-01-15');

INSERT INTO orders (user_id, order_total, order_date) VALUES
(42, 99.99, '2024-01-15'),
(43, 150.00, '2024-01-15'),
(44, 75.50, '2024-01-15');

UPDATE orders
SET user_id = NULL
WHERE order_id = 123;
-- Fails because user_id is NOT NULL

UPDATE orders
SET user_id = 1  -- system default user
WHERE order_id = 123;

UPDATE orders
SET notes = NULL,  -- notes is nullable
    updated_at = NOW()
WHERE order_id = 123;

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'orders' AND is_nullable = 'YES';

INSERT INTO users (id, username, email)
VALUES (NULL, 'alice', '[email protected]');
-- Fails even though SERIAL auto-generates id

INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');
-- id is auto-generated as next sequence value

INSERT INTO users (id, username, email)
VALUES (100, 'alice', '[email protected]');
-- Explicitly provide non-NULL id

SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'id';

const order = await prisma.orders.create({
  data: {
    userId: 42,        // Required - NOT NULL
    orderTotal: 99.99,
    orderDate: new Date(),
  },
});
// Prisma's type system enforces userId is required

order = Order(
    user_id=42,        # Required
    order_total=99.99,
    order_date=datetime.now()
)
session.add(order)
session.commit()  # Validates before commit

order = Order.new(
  user_id: 42,       # Required
  order_total: 99.99,
  order_date: Time.now
)
order.save!  # Raises if validation fails

function validateOrder(data: any) {
  if (!data.userId) {
    throw new Error('user_id is required');
  }
  if (!data.orderTotal) {
    throw new Error('order_total is required');
  }
  return true;
}

validateOrder(orderData);
const result = await insertOrder(orderData);

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  order_total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (user_id, order_total)
VALUES (42, 99.99);
-- status defaults to 'pending'
-- created_at defaults to current timestamp

INSERT INTO archive (user_id, amount)
SELECT u.id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'inactive';
-- Fails if u.id is NULL (unlikely) or o.amount is NULL

INSERT INTO archive (user_id, amount)
SELECT u.id, COALESCE(o.amount, 0)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'inactive'
  AND u.id IS NOT NULL;

INSERT INTO archive (user_id, amount)
SELECT u.id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'inactive'
  AND o.amount IS NOT NULL;

COALESCE(column, default_value)
COALESCE(column1, column2, 'fallback')

ALTER TABLE orders
ALTER COLUMN user_id SET NOT NULL;
-- Fails if any rows have user_id = NULL

-- Step 1: Check how many NULLs exist
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

-- Step 2: Update NULLs to a valid value
UPDATE orders
SET user_id = 1  -- default/fallback user id
WHERE user_id IS NULL;

-- Step 3: Verify all rows now have values
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

-- Step 4: Now add the constraint
ALTER TABLE orders
ALTER COLUMN user_id SET NOT NULL;

-- Add column with temporary DEFAULT
ALTER TABLE orders
ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending';

-- PostgreSQL applies DEFAULT to existing rows
-- No need for UPDATE if DEFAULT is sufficient

-- Later, you can remove DEFAULT if desired
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;