Postgresql – COUNT with set DIFFERENCE and UNION

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
    -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct  -- simpler
FROM   patients;

• Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.

• Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.

• Cast to any numeric type that supports fractional digits, like @a_horse already explained.
  Since you want to round() to two fractional digits I suggest numeric (which is the same as decimal in Postgres).
  It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
  Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric automatically because of the decimal point (100.0).

• It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
  In this case, the first multiplication (count(refinst) * 100) can be computed with cheap and exact integer arithmetic. Only then we cast to numeric and divide by the next integer (which we do not cast additionally).

• NULLIF(count(*), 0) prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.

Rounded to two fractional digits:

SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM   patients;

They are the same. Check the documentation (where they are mentioned together):

--column-inserts
--attribute-inserts
Dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents.