Use CREATE SEQUENCE https://www.postgresql.org/docs/current/static/sql-createsequence.html:
CREATE SEQUENCE scores_job_id_seq; -- = default name for plain a serial
然后添加一列默认值scores.job_id
:
ALTER TABLE scores ALTER COLUMN job_id SET DEFAULT nextval('scores_job_id_seq');
如果你想bind列的序列(因此当删除列时它也会被删除),还运行:
ALTER SEQUENCE scores_job_id_seq OWNED BY scores.job_id;
所有这些都可以用伪数据类型来代替serial https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL对于列job_id
首先:
- 在 Postgres 中安全、干净地重命名使用串行主键列的表? https://stackoverflow.com/questions/14649682/safely-and-cleanly-rename-tables-that-use-serial-primary-key-columns-in-postgres/14651788#14651788
如果您的表已经有行,您可能需要设置SEQUENCE
到下一个最高值并填写表中缺少的序列值:
SELECT setval('scores_job_id_seq', COALESCE(max(job_id), 1)) FROM scores;
可选:
UPDATE scores
SET job_id = nextval('scores_job_id_seq')
WHERE job_id IS NULL;
- 如何在 PostgreSQL 中有效检查序列中已使用和未使用的值 https://stackoverflow.com/questions/32684374/how-to-check-a-sequence-efficiently-for-used-and-unused-values-in-postgresql/32686099#32686099
- Postgres 手动更改序列 https://stackoverflow.com/questions/8745051/postgres-manually-alter-sequence/8750984#8750984
- 当 postgres 不同步时如何重置主键序列? https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync/23390399#23390399
唯一剩下的区别是serial
列也设置为NOT NULL
。您可能也希望也可能不希望这样:
ALTER TABLE scores ALTER COLUMN job_id SET NOT NULL;
But you cannot只需改变现有的类型integer
:
ALTER TABLE scores ALTER job_id TYPE serial;
serial
不是实际的数据类型。这只是一个象征性的便利功能CREATE TABLE
.
在 Postgres 10 或更高版本中考虑IDENTITY
column:
- 自动递增表格列 https://stackoverflow.com/questions/9875223/auto-increment-table-column/9875517#9875517