How to cast bigint to timestamp with time zone in postgres in an update

This answer assumes that the date__bigint column is storing a UNIX timestamp in seconds since the epoch. Here is one way to convert to a Postgres timestamp:

UPDATE your_table
SET date__timestamp = TIMESTAMP 'epoch' + date__bigint * INTERVAL '1 second'
WHERE foo = 1;

That is, we can add some number of seconds to the epoch timestamp to convert your value to a formal Postgres timestamp.

You can cast it to text and use TO_TIMESTAMP and the convert to timestamp at time zone

SELECT 
to_timestamp ( '20181102'::bigint::text,'YYYYMMDD')::timestamp at time zone 'UTC' 
at time zone 'PST' ;

update t
   set date__timestamp = TO_TIMESTAMP(date_bigint::text,'YYYYMMDD')::timestamp 
   at time zone 'UTC' at time zone 'PST'
where foo = 1;

Demo