Create PostgreSQL Sequence for Ruby on Rails Apps on Heroku

I was attempting to add a record via my rails app on Heroku and got the following:

ERROR: null value in column "id" violates not-null constraint

Since as the date of this writing Heroku uses PostgreSQL, here are the steps I took.

My Postgres server version is PostgreSQL 9.1.7 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit. I also am using an Ubuntu Linux desktop running Rails 3.2.11.

Now to the steps I took to solve the problem.

In your rails app db/migrate folder create the following class and name it 201301081552_create_table_name_sequence.rb. Substitute the time signature in the name with your date and time and “table_name” with the name of the table you to which you are adding the sequence:

	class CreateTableNameSequence < ActiveRecord::Migration
	def up
	#create sequence for categories table
	execute <<-SQL
	CREATE SEQUENCE table_name_id_seq
	INCREMENT 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 25
	CACHE 1;
	SQL
	end
	def down
	DROP SEQUENCE table_name_id_seq;
	end
	end

view raw gistfile1.rb hosted with ❤ by GitHub

Then in your rails app db/migrate folder create the following class and name it 201301081552_add_sequence_to_table_name.rb. Again, substitute the time signature in the name with your date and time and “table_name” with your name:

	class AddSequenceToTableName < ActiveRecord::Migration
	def up
	#add sequence to
	execute <<-SQL
	ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('table_name_id_seq'::regclass);
	SQL
	end
	def down
	execute <<-SQL
	ALTER TABLE table_name ALTER COLUMN id SET NOT NULL;
	SQL
	end
	end

view raw gistfile1.rb hosted with ❤ by GitHub

Next, commit and push them to your github repository. After that push them to Heroku. If you have the Heroku toolbelt installed you can issue the following:

sudo heroku run rake db:migrate

This should run the database migration that will create the sequence as well as add it to the id column.