Updating column from another table - PostgreSQL 9.2 There are two tables:


ja_jobs
junk.test_test1


I need to update the time_job column from junk.test_test1 to ja_jobs.

Here is what I'm doing now:

UPDATE ja_jobs t2
SET time_job = t1.time_job
FROM junk.test_test1 t1
WHERE t2.id = t1.id
AND t2.time_job IS DISTINCT FROM t1.time_job;


But I'm getting empty/null updates.. 

Why is that happening and how can I solve the problem?

UPDATE 1:

When I run the query above, I get:

UPDATE 2202


So I check if the data has been changed:

select * FROM public.ja_jobs WHERE id = 14574527


And the time_job field is null - So no changes have been made


14574527 = Is an ID from a random ROW that has the time_job field null


UPDATE 2:

select count(*) FROM junk.test_test1 a
JOIN ja_jobs as b ON a.id = b.id
WHERE b.clientid = 23856 AND deleted = false


I get: 770 - So it seems that there are data to be updated.. But no update is happening...

QUERY:

UPDATE public.ja_jobs t2 SET time_job = t1.time_job FROM junk.test_test1 t1 WHERE t2.id = 14571727 AND t2.time_job IS DISTINCT FROM t1.time_job;

select id,clientid,time_job FROM public.ja_jobs WHERE id = 14571727




select id,clientid,time_job from junk.test_test1 WHERE id = 14571727




AS you can see, the test_test1.time_job is not null. That data should be updated into the public.ja_jobs.time_job column - But it's not

UPDATE 3:

Triggers:
    "b_iu_timestamps_from_visits" BEFORE INSERT OR UPDATE ON "ja_jobs" FOR EACH ROW EXECUTE PROCEDURE "tf_ja_jobs_timestamps_from_visits"()
    "ct_a_iu_jobs_same_account_only" AFTER INSERT OR UPDATE ON "ja_jobs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "dm"."tf_jobs_same_account_only"()
    "ct_a_u_jobs_block_immutable_columns_changes" AFTER UPDATE ON "ja_jobs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "dm"."tf_jobs_block_immutable_columns_changes"()
    "update_modified_date" BEFORE UPDATE ON "ja_jobs" FOR EACH ROW EXECUTE PROCEDURE "update_lastmodified_column"()
The statement looked perfectly fine in the first place. After excluding other options (like tables referenced without schema), it seems like the problem is caused by the 4 UPDATE triggers on the table (or one of them).

You should inspect the trigger code and understand what they are doing and which one of them is causing this behaviour. 

After that, it's really a business decision. If the triggers are not supposed to do this, you could decide to modify/correct the trigger code.

If they are doing what they are supposed to do, you might decide to not run the updates or - if these changes have to done absolutely - run the updates by disabling temporarily the triggers, bypassing the business rules.

You could disable one by one with:

ALTER TABLE public.ja_job_users 
    DISABLE TRIGGER trigger_name ;


or all of them with:

ALTER TABLE public.ja_job_users 
    DISABLE TRIGGER USER ;


which disables all user defined triggers (but not FK triggers), then run the wanted statements and then re-enable them (with the same syntax: ... ENABLE TRIGGER ...;. 

But I would strongly suggest to first inspect what these triggers do. It might be possible that only one is the root cause and the others are crucial and have to be kept enabled.

If the data are critical (can you afford to lose them?), I would also strongly suggest you take a backup now, before making any changes and do the actions manually, and inside a transaction. So, you can BEGIN; a transaction, run the updates, checking the data that have changed, and only when you are absolutely sure then COMMIT; the transaction.

Then take a backup immediately after the changes as well. This will allow later - and in your own time - to check thoroughly any side effect that this operation may have done by comparing the two backups.