创建一个 PostgreSQL 序列到一个字段(它不是记录的 ID) [英] Creating a PostgreSQL sequence to a field (which is not the ID of the record)

问题描述

我正在开发一个 Ruby on Rails 应用程序.我们使用的是 PostgreSQL 数据库.

I am working on a Ruby on Rails app. We are using a PostgreSQL database.

有一个名为 scores 的表，其中包含以下列:

There is a table named scores with the following columns:

Column        | Type
--------------+-----------------------
id            | integer
value         | double precision
ran_at        | timestamp
active        | boolean
build_id      | bigint
metric_id     | integer
platform_id   | integer
mode_id       | integer
machine_id    | integer
higher_better | boolean
job_id        | integer
variation_id  | integer
step          | character varying(255)

我需要在job_id中添加一个sequence(注意:job没有模型).

I need to add a sequence to job_id (note: there is no model for job).

如何创建此序列?

推荐答案

使用 创建序列:

CREATE SEQUENCE scores_job_id_seq;  -- = default name for plain a serial

然后在scores.job_id中添加一列默认值:

Then add a column default to scores.job_id:

ALTER TABLE scores ALTER COLUMN job_id SET DEFAULT nextval('scores_job_id_seq');

如果你想将序列绑定到列(所以当列被删除时它被删除)，也运行:

If you want to bind the sequence to the column (so it is deleted when the column is deleted), also run:

ALTER SEQUENCE scores_job_id_seq OWNED BY scores.job_id;

所有这些都可以用伪数据类型替换 serial 列 job_id 以:

All of this can be replaced with using the pseudo data type serial for the column job_id to begin with:

• 在 Postgres 中安全、干净地重命名使用串行主键列的表?

如果您的表已经有行，您可能需要将 SEQUENCE 设置为下一个最高值并填充表中缺失的序列值:

If your table already has rows, you may want to set the SEQUENCE to the next highest value and fill in missing serial values in the table:

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 中有效地检查序列中已使用和未使用的值
• Postgres 手动更改序列
• 如何在不同步时重置 postgres 的主键序列?

唯一剩下的区别，serial 列也设置为 NOT NULL.你也可能想要也可能不想要:

The only remaining difference, a serial column is also set to NOT NULL. You may or may not want that, too:

ALTER TABLE scores ALTER COLUMN job_id SET NOT NULL;

但是你不能改变现有整数的类型:

ALTER TABLE scores ALTER job_id TYPE serial;

serial 不是实际的数据类型.这只是 CREATE TABLE 的符号方便功能.
在 Postgres 10 或更高版本中考虑 IDENTITY 列:

serial is not an actual data type. It's just a notational convenience feature for CREATE TABLE.
In Postgres 10 or later consider an IDENTITY column:

• 自增表列

这篇关于创建一个 PostgreSQL 序列到一个字段(它不是记录的 ID)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！