创建一个 PostgreSQL 序列到一个字段（它不是记录的 ID）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24657693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a PostgreSQL sequence to a field (which is not the ID of the record)
提问by Tanvir
I am working on a Ruby on Rails app. We are using a PostgreSQL database.
我正在开发一个 Ruby on Rails 应用程序。我们正在使用 PostgreSQL 数据库。
There is a table named scoreswith the following columns:
有一个以scores以下列命名的表：
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)
I need to add a sequenceto job_id(note: there is no model for job).
我需要添加一个序列到job_id（注意：没有模型job）。
How do I create this sequence?
我如何创建这个序列？
回答by Erwin Brandstetter
Use CREATE SEQUENCE:
CREATE SEQUENCE scores_job_id_seq; -- = default name for plain a serial
Then add a column default to scores.job_id:
然后添加一列默认值scores.job_id：
ALTER TABLE scores ALTER COLUMN job_id SET DEFAULT nextval('scores_job_id_seq');
If you want to bindthe 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;
All of this can be replaced with using the pseudo data type serialfor the column job_idto begin with:
所有这一切都可以使用伪数据类型被替换serial为列job_id开始与：
If your table already has rows, you may want to set the SEQUENCEto the next highest value and fill in missing serial values in the table:
如果您的表已有行，您可能希望将 设置SEQUENCE为下一个最高值并填写表中缺失的序列值：
SELECT setval('scores_job_id_seq', COALESCE(max(job_id), 1)) FROM scores;
Optionally:
可选：
UPDATE scores
SET job_id = nextval('scores_job_id_seq')
WHERE job_id IS NULL;
- How to check a sequence efficiently for used and unused values in PostgreSQL
- Postgres manually alter sequence
- How to reset postgres' primary key sequence when it falls out of sync?
The only remaining difference, a serialcolumn is also set to NOT NULL. You may or may not want that, too:
唯一剩下的区别，一serial列也设置为NOT NULL。你也可能想要也可能不想要：
ALTER TABLE scores ALTER COLUMN job_id SET NOT NULL;
Butyou cannotjust alter the type of an existing integer:
但是你不能只改变现有的类型integer：
ALTER TABLE scores ALTER job_id TYPE serial;serialis not an actual data type. It's just a notational convenience feature for CREATE TABLE.
In Postgres 10 or later consider an IDENTITYcolumn:
serial不是实际的数据类型。这只是CREATE TABLE.
在 Postgres 10 或更高版本中，考虑一IDENTITY列：
回答by Tanvir
So I figured out how to do this using ActiveRecord migrations on Ruby on Rails. I basically used Erwin's commands and help from this pageand put them in the migration files. These are the steps:
所以我想出了如何在 Ruby on Rails 上使用 ActiveRecord 迁移来做到这一点。我基本上使用了 Erwin 的命令和来自此页面的帮助并将它们放在迁移文件中。这些是步骤：
1. In the terminal, type:
1. 在终端中，输入：
rails g migration CreateJobIdSequence
rails g migration AddJobIdSequenceToScores
2. Edit the migration files as follows:
2. 编辑迁移文件如下：
20140709181616_create_job_id_sequence.rb:
20140709181616_create_job_id_sequence.rb：
class CreateJobIdSequence < ActiveRecord::Migration
def up
execute <<-SQL
CREATE SEQUENCE job_id_seq;
SQL
end
def down
execute <<-SQL
DROP SEQUENCE job_id_seq;
SQL
end
end
20140709182313_add_job_id_sequence_to_scores.rb:
20140709182313_add_job_id_sequence_to_scores.rb：
class AddJobIdSequenceToScores < ActiveRecord::Migration
def up
execute <<-SQL
ALTER SEQUENCE job_id_seq OWNED BY scores.job_id;
ALTER TABLE scores ALTER COLUMN job_id SET DEFAULT nextval('job_id_seq');
SQL
end
def down
execute <<-SQL
ALTER SEQUENCE job_id_seq OWNED BY NONE;
ALTER TABLE scores ALTER COLUMN job_id SET NOT NULL;
SQL
end
end
3. Migrate the database. In the terminal type:
3. 迁移数据库。在终端类型中：
rake db:migrate
