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

Question

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

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)

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

How do I create this sequence?

Demurrage

Answer 1

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');

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. See:

• Safely rename tables using serial primary key columns

If your table already has rows, you may want to set the SEQUENCE to current maximum value:

SELECT setval('scores_job_id_seq', COALESCE(max(job_id), 1)) FROM scores;

The only remaining difference: a serial column is also set to NOT NULL. You may want that, too. If any null values are present, update with serial values first:

UPDATE scores
SET    job_id = nextval('scores_job_id_seq')
WHERE  job_id IS NULL;

Finally:

ALTER TABLE scores ALTER COLUMN job_id SET NOT NULL;

Related:

• 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?

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 instead:

• Auto increment table column

Heyerdahl

can you do this using ActiveRecord migration? – Demurrage

@taa have you tried search it? https://mcmap.net/q/28931/-how-to-add-sequences-to-a-migration-and-use-them-in-a-model/1499698 https://mcmap.net/q/28932/-ruby-on-rails-postgresql-usage-of-custom-sequences/1499698 – Anticipatory

Answer 2

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 CreateJobIdSequence
rails 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
  end
end

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:

rake db:migrate

Demurrage

in case you want a string as job_id, just use to_char(nextval('job_id_seq'), 'JB000000') instead of nextval('job_id_seq') – Coquillage

To rollback I had to use ALTER TABLE scores ALTER COLUMN job_id DROP DEFAULT; not SET NOT NULL – Arelus