I’m seeking guidance on how to use PostgreSQL to update multiple rows in a specific column. I’m particularly interested in methods that offer optimal performance. Any help would be greatly appreciated

I created a ref table here DBFiddle

My goal is to update the ‘status’ column of all records to ‘INACTIVE’. Currently, I’m working with a small table, but in a real-world scenario, I would need to apply this to approximately 10,000 records.

I attempted to use a particular method (which I’ll detail below), but I was unable to get it to function correctly.

insert (id, status) values (...), (...) on conflict update

and an example I was trying

INSERT INTO questionnaires (id, status)
        VALUES(190, 'ACTIVE'), (191, 'ACTIVE') ON CONFLICT (id)
        DO
        UPDATE
        SET
            status = EXCLUDED.status
        WHERE
            questionnaires.status <> EXCLUDED.status;

I consistently encounter a ‘not-null violation’ error for the other columns. I’ve read online that the method I’m trying to use should be more optimized and
performance-friendly, allowing me to update all records at once instead of one by one. However, I’m struggling to implement it without errors.

One approach I’ve considered is to first select all the relevant records, then pass all the data, along with the updates I want to make, to the insert operation. This might help me avoid the null issue.

However, I suspect there might be a more efficient way to accomplish this. Any suggestions or insights would be greatly appreciated.