如何在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?

推荐答案

）

(Updated - Thanks to the people who commented)

假设您有一个名为 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:

  CREATE SEQUENCE test_id_seq;
  ALTER TABLE test1 ADD COLUMN id INTEGER;
  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屋！