Como posso especificar a posição de uma nova coluna no PostgreSQL?

ALTER TABLE ADD COLUMNapenas adicionará a nova coluna no final, como a última. Para criar uma nova coluna em outra posição, você precisa recriar a tabela e copiar os dados da tabela antiga/atual nesta nova tabela.

Você precisará recriar a tabela se desejar uma determinada ordem. Basta fazer algo como:

alter table tablename rename to oldtable;
create table tablename (column defs go here);
insert into tablename (col1, col2, col3) select col2, col1, col3 from oldtable;

Crie índices conforme necessário, etc.

Se você quiser isso apenas para aparência, acho mais fácil manter uma visualização por cada tabela com a ordem desejada das colunas e selecionar a partir dela em vez da tabela.

create table my_table (
create view view_my_table as
  select id, name, created_date from my_table;

-- adding a new column
begin;
alter table my_table add column email varchar(255);
drop view view_my_table;
create view view_my_table as
  select id, name, email, created_date from my_table;
commit;

Para todos os outros propósitos (como inserir, união) é melhor sempre especificar a lista de colunas.

-- bad
insert into my_table values (...);
(select * from my_table)
  union all
(select * from my_table);

-- good
insert into my_table (id, name, email, created_date) values (...);
(select id, name, email, created_date from my_table)
  union all
(select id, name, email, created_date from my_table);