Hey Mike Meinz is right that there is no any QUERY to insert the column into a specific position, it will ALWAYS be added to the End on your column set.
and we should not be limited by the index of the column in SQL :)
But , here is a trick to achieve what you are asking for
test=*# create table TABLE1_temp as select COL1, null::int as COL2, COL3 from TABLE1;
test=*# drop table TABLE1;
test=*# alter table TABLE1_temp rename to TABLE1;
test=# select * from TABLE1;
Hope it's useful !