Why am I getting ActiveRecord :: StatementInvalid: PG :: SyntaxError: ERROR when using a boolean field in a rails app with Postgresql?
I am developing rails API and playing with Heroku. All environments use Postgres. I am getting the same error in rake db: setup and rake db: test: prepare (and "heroku run rake db: setup" for that matter). This problem originally came up when I first tried to push her towards Heroku. I have used mysql locally. Since you ported dev and test to postgres to avoid similar surprises in the future ...
$ rake db:test:prepare
rake aborted!
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "("
LINE 1: ...r, "borrower_id" integer, "lenderAccepted" boolean(1), "crea...
^
: CREATE TABLE "bookings" ("id" serial primary key, "pickup" timestamp, "hoursBooked" integer, "lender_id" integer, "borrower_id" integer, "lenderAccepted" boolean(1), "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, "equipment_id" integer)
Here is the migration:
class CreateBookings < ActiveRecord::Migration
def change
create_table :bookings do |t|
t.datetime :pickup
t.integer :hoursBooked
t.references :lender, index: true
t.references :borrower, index: true
t.boolean :lenderAccepted
t.timestamps null: false
end
add_foreign_key :bookings, :lenders
add_foreign_key :bookings, :borrowers
end
end
and here is my gemfile:
source 'https://rubygems.org'
gem 'rails', '4.2.0'
gem 'sass-rails', '5.0.0.beta1'
gem 'uglifier', '2.5.3'
gem 'coffee-rails', '4.1.0'
gem 'jquery-rails', '4.0.0.beta2'
gem 'turbolinks', '2.3.0'
gem 'jbuilder', '2.2.3'
gem 'devise', '3.4.1'
gem 'sdoc', '0.4.0', group: :doc
gem 'pg', '0.17.1'
gem 'bcrypt-ruby', '3.1.1.rc1', :require => 'bcrypt'
group :development, :test do
gem 'byebug', '3.4.0'
gem 'web-console', '2.0.0.beta2'
gem 'spring', '1.1.3'
end
group :production do
gem 'rails_12factor', '0.0.2'
end
group :test do
gem 'minitest-reporters', '1.0.5'
gem 'mini_backtrace', '0.1.3'
gem 'guard-minitest', '2.3.1'
end
Using:
- ruby 2.1.4p265 (version 2014-10-27 48166) [x86_64-linux]
- rails 4.2.0
- Postgres 9.3.5
Thanks for any help provided.
Bill
PS by adding the generated schema:
create_table "bookings", force: :cascade do |t|
t.datetime "pickup"
t.integer "hoursBooked", limit: 4
t.integer "lender_id", limit: 4
t.integer "borrower_id", limit: 4
t.boolean "lenderAccepted", limit: 1
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "equipment_id", limit: 4
end
add_index "bookings", ["borrower_id"], name: "index_bookings_on_borrower_id", using: :btree
add_index "bookings", ["equipment_id"], name: "index_bookings_on_equipment_id", using: :btree
add_index "bookings", ["lender_id"], name: "index_bookings_on_lender_id", using: :btree
source to share
Victory is too short! Thanks for your insightful comment!
The problem seems to be that the original db: migration done when using mysql is not acceptable for postgres. Commenting out fields from migrations and schema and then adding them back under the new migration, all the fixes ...
class AddBackBooleansMigration < ActiveRecord::Migration
def change
add_column :bookings, :lender_accepted, :boolean
# other similar boolean adds
end
end
New schema:
create_table "bookings", force: :cascade do |t|
...
t.boolean "lender_accepted"
end
and rake it until you do it.
$ rake db:migrate
== 20150105131615 AddBackBooleansMigration: migrating =========================
-- add_column(:bookings, :lender_accepted, :boolean)
-> 0.0333s
...
== 20150105131615 AddBackBooleansMigration: migrated (0.0370s) ================
source to share
This is a bug in Rails because AR + mysql mangles boolean fields. I made a patch for this: https://github.com/rails/rails/issues/19065
source to share