title: How to cast bigint to timestamp with time zone in postgres in an update
body: <p>Is there a way to cast a <code>BIGINT</code> to <code>TIMESTAMP</code> or <code>TIMESTAMP WITH TIME ZONE</code> in Postgres? I need to copy the data from a <code>BIGINT</code> column to the <code>TIMESTAMP</code> column.</p>
  
  <p>Here is what I have tried:</p>
  
  <pre><code>update table
  set date__timestamp = date__bigint::timestamp 
  where foo = 1;
  </code></pre>
  
  <blockquote>
    <p>ERROR: cannot cast type bigint to timestamp without time zone</p>
  </blockquote>
  
  <p>I changed the timestamp column to a with timezone column and tried this:</p>
  
  <pre><code>update table
  set date__timestamp = date__bigint::timestamp with time zone at time zone 'PST' 
  where foo = 1;
  </code></pre>
  
  <blockquote>
    <p>ERROR: cannot cast type bigint to timestamp with time zone</p>
  </blockquote>
  
  <pre><code>update table
  set date__timstamp = TO_CHAR(TO_TIMESTAMP(date__bi / 1000), 'DD/MM/YYYY HH24:MI:SS')
  where foo = 1;
  </code></pre>
  
  <blockquote>
    <p>ERROR: column "date__timestamp" is of type timestamp without time zone
    but expression is of type text Hint: You will need to rewrite or cast
    the expression.</p>
  </blockquote>
  
  <p>The data looks like this.</p>
  
  <pre><code>date_bigint: 20181102
  date_timestamp: 2018-11-02 17:00:00.000000
  </code></pre>
  
  <p>Do I need to pass default values to the casting?</p>