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

by

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:

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 scores
SET    job_id = nextval('scores_job_id_seq')
WHERE  job_id IS 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;

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:

More Related Contents:

  1. Can’t find the ‘libpq-fe.h header when trying to install pg gem
  2. ActiveRecord OR query Hash notation
  3. How to chain scope queries with OR instead of AND?
  4. Rails 5: ActiveRecord OR query
  5. How to handle Ruby on Rails error: “Please install the postgresql adapter: `gem install activerecord-postgresql-adapter'”
  6. Clean way to find ActiveRecord objects by id in the order specified
  7. ActiveRecord Find By Year, Day or Month on a Date field
  8. Heroku Postgres Error: PGError: ERROR: relation “organizations” does not exist (ActiveRecord::StatementInvalid)
  9. GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
  10. query , can not select column count
  11. Can I write PostgreSQL functions on Ruby on Rails?
  12. New data not persisting to Rails array column on Postgres
  13. Validate uniqueness of multiple columns
  14. Store the day of the week and time?
  15. PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects
  16. Changing a column type to longer strings in rails
  17. Rails extending ActiveRecord::Base
  18. Is there a way to get a collection of all the Models in your Rails app?
  19. Rails model without database
  20. Using Rails serialize to save hash to database
  21. Rails 3: Get Random Record
  22. Setting session timeout in Rails 3
  23. Rails find_or_create_by more than one attribute?
  24. Rails ActiveRecord: Find All Users Except Current User
  25. docker postgres with initial data is not persisted over commits
  26. Rails 4 nested attributes and has_many :through associaton in a form
  27. ActiveRecord serialize using JSON instead of YAML
  28. How to implement Active Record inheritance in Ruby on Rails?
  29. PostgreSQL: FATAL – Peer authentication failed for user (PG::ConnectionBad)
  30. Rails: Order with nulls last

Leave a Comment