如何在 PostgreSQL 中向现有表添加自动递增主键? [英] How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

问题描述

我有一个包含现有数据的表格.有没有办法在不删除和重新创建表的情况下添加主键?

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

推荐答案

(更新 - 感谢评论的人)

假设您有一个名为 test1 的表，您想向其中添加一个自动递增的主键 id(代理项)列.在最新版本的 PostgreSQL 中，以下命令应该就足够了:

Suppose you have a table named test1, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL:

   ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

旧版本的 PostgreSQL

在旧版本的 PostgreSQL(8.x 之前?)中，您必须完成所有繁重的工作.以下命令序列应该可以解决问题:

Older Versions of PostgreSQL

In old versions of PostgreSQL (prior to 8.x?) you had to do all the dirty work. The following sequence of commands should do the trick:

  ALTER TABLE test1 ADD COLUMN id INTEGER;
  CREATE SEQUENCE test_id_seq OWNED BY test1.id;
  ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
  UPDATE test1 SET id = nextval('test_id_seq');

同样，在最新版本的 Postgres 中，这大致相当于上面的单个命令.

Again, in recent versions of Postgres this is roughly equivalent to the single command above.

这篇关于如何在 PostgreSQL 中向现有表添加自动递增主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！