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

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?

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