title: How to cast only the part of a table using a single SQL command in PostgreSQL
body: <p>In a PostgreSQL table I have several information stored as text. It depends on the context described by a type column what type of information is stored. The application is prepared to get by only one command the Id's of the row.</p>
  
  <p>I got into trouble when i tried to compare the information (bigint stored as a string) with an external value (e.g. '9' > '11'). When I tried to cast the column, the datatbase return an error (not all values in the column are castable, e.g. datetime or normal text). Also when I try to cast only the result of a query command, I get a cast error.</p>
  
  <p>I get the table with the castable rows by this command:</p>
  
  <pre><code>SELECT information.id as id, item.information::bigint as item
  FROM information
  INNER JOIN item
  ON information.id = item.informationid
  WHERE information.type = 'task'
  </code></pre>
  
  <p>The resulting rows are showing up only text that is castable. When I throw it into another command it results in an error.</p>
  
  <pre><code>SELECT x.id FROM (
      SELECT information.id as id, item.information::bigint as item
      FROM information
      INNER JOIN item
      ON information.id = item.informationid
      WHERE information.type = 'task'
  ) AS x
  WHERE x.item &gt; '0'::bigint
  </code></pre>
  
  <p>Accroding to the error, the database tried to cast all rows in the table.</p>