duplicate rows based on a column value
You need a sufficiently large table to multiply the source table. In your example it is sufficient to do a self join:

select material, 1 
from (
    select x.material, x.count
         , row_number() over (partition by x.material) rn 
    from t as x 
    cross join t as y
) as u 
where rn &lt;= count 
order by material;

Gold               1
Gold               1
Rock               1
Rock               1
Rock               1
Silver             1
Wood               1
Wood               1


PostgreSQL also have a nifty table function generate_series that can be used:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn 
    from t 
    cross join generate_series(1,10)
) as u 
where rn &lt;= count
order by material;


instead of 10 you can determine:

select max(count) from t


and use that to avoid creating an unnecessary large table.

While educating myself I realized that generate_series can take a variable as argument:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn 
    from t 
    cross join ( SELECT  generate_series(1, cnt)
                 FROM ( SELECT MAX(COUNT) FROM t ) u (cnt)
               ) as v 
) as u 
where rn &lt;= count
order by material;


Using LATERAL means we can correlate cnt with count in the outmost table, and thereby remove the need for row_number():

select material, 1 
from (
    select t.material, t.count
    from t 
    cross join LATERAL ( SELECT  generate_series(1, cnt)
                         FROM ( 
                             SELECT COUNT 
                             FROM t as t1
                             WHERE t.material = t1.material 
                         ) u (cnt)
                       ) as v 
) as u 
order by material;


If you made it this far, go read @ypercubeᵀᴹ answer. It does'nt get much simpler or better than that. Please vote for that one.