Creating a PostgreSQL sequence to a field (which is not the ID of the record)

Use CREATE SEQUENCE:

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

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;

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

• Safely and cleanly rename tables that use serial primary key columns in Postgres?

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;

Optionally:

UPDATE scoresSET    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 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;

But you cannot just alter the type of an existing integer:

ALTER TABLE scores ALTER job_id TYPE serial;

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:

• Auto increment table column

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 page and put them in the migration files. These are the steps:

1.In the terminal, type:

rails g migration CreateJobIdSequencerails g migration AddJobIdSequenceToScores

2.Edit the migration files as follows:

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  endend

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  endend

3.Migrate the database. In the terminal type:

rake db:migrate