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?
In PostgreSQL, create a sequence is used to create a new sequence generator. The sequence name is must be distinct from any other name of the sequence, table, view or foreign table in PostgreSQL. If we have given a schema name at the time of sequence creation, then the sequence will be created with the specified schema.
By assigning the SERIAL pseudo-type to the id column, PostgreSQL performs the following: First, create a sequence object and set the next value generated by the sequence as the default value for the column. Second, add a NOT NULL constraint to the id column because a sequence always generates an integer, which is a non-null value.
Serial in PostgreSQL indicates that the value for the column is generated by consulting the sequence. Serial in PostgreSQL will create a new sequence object and set the column’s default value to the next value produced by the sequences. The sequence always produces a non-null value; it will add the not null constraints to the column.
If you want to manually call nextval () you can use pg_get_serial_sequence () to obtain the sequence name: Note that the use of serial is discouraged in modern Postgres versions in favor of identity columns. It's vexing that serial is discouraged in favor of identity, but serial has much better documentation.
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
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With