Início / dba / Perguntas / 78732
Accepted
zetah
Asked: 2014-10-09 18:42:14 +0800 CST

Altere a coluna existente no PG para a chave primária de incremento automático

  • 772

Tenho um banco de dados em Postgresql, que foi migrado do SQL Server (somente dados).
No SQL Server, uma tabela desse banco de dados possui estas colunas:

measure_id
datum
measure

onde measure_idé chave primária auto-incremental, datumé datetime e measureé float.
Após migração no Postrgresql, measure_idé coluna do tipo bigint.

Como posso alterar essa coluna ( measure_id) para bigserial e atribuí-la como chave primária, agora que minha tabela está cheia de dados?

postgresql migration

2 respostas

  1. Best Answer

    Crie uma sequência e use-a como valor padrão para a coluna:

    create sequence measures_measure_id_seq
   owned by measures.measure_id;

alter table measures
   alter column measure_id set default nextval('measures_measure_id_seq');

commit;

    Isso essencialmente o que serialfaz.

    Consulte o manual para obter detalhes:
    http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

    A partir do Postgres 10, a maneira recomendada é usar colunas compatíveis com o padrão identity, em vez de serial (ou bigserial).

    alter table measures
   alter measure_id add generated always as identity;

    Em ambos os casos, você terá que ajustar a sequência para corresponder ao valor máximo atual na measure_idcoluna:

    select setval(pg_get_serial_sequence('measures', 'measure_id'), max(measure_id))
from measures;
    • 24

  2. Vejo que a questão já está marcada como resolvida, porém, conforme apontado nos comentários, está faltando o caso em que existem registros existentes que já possuem alguns valores definidos na coluna relevante.

    Vou listar alguns outros casos também.

    Estou fazendo isso para uma tabela chamada productsque está presente no publicesquema. O auto-incremento está sendo feito para a idcoluna desta tabela.

    1. Para uma coluna inexistente

    -- auto-increment constraint for a new column
 ALTER TABLE public.products
    ADD COLUMN id SERIAL PRIMARY KEY;

    2. Para uma coluna existente que não obteve valores na tabela

    -- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;

-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');

    3. Para uma coluna existente que já possui alguns valores na tabela

    -- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;

-- set the current value of the sequence to the max value from that column
-- (id column in this scenario)
SELECT SETVAL('public_products_id_seq', (select max(id) from public.products), false)

-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');

    Referências de documentação para CREATE SEQUENCE e SETVAL

    • 1

relate perguntas