CODEWITHSUNDEEP
ruby-on-railspostgresqlherokubooleancreate-table
Bill Noto
Bill Noto

Reputation: 541

Why am I receiving an ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR when using a boolean field in a rails app with Postgresql?

I'm developing a rails API and playing around with Heroku. All environments are using Postgres. I get the same error in rake db:setup and rake db:test:prepare (and 'heroku run rake db:setup' for that matter). This problem originally cropped up when I first tried pushing to Heroku. I was using mysql locally. Since have migrated dev and test to postgres to avoid surprises like these 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

Am using:

Thanks for any help offered.

Bill

PS, 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

Upvotes: 3

Views: 1436

Answers (2)

court3nay
court3nay

Reputation: 2365

It's a bug in Rails, due to the way AR+mysql fakes out boolean fields. I have made a patch for this :) https://github.com/rails/rails/issues/19065

Upvotes: 4

Bill Noto
Bill Noto

Reputation: 541

Victory is mu is too short's! Thanks for your insightful comment!

It seems the problem is that the original db:migration run when I was using mysql was unacceptable to postgres. Commenting out the fields from the migrations and the schema and then adding them back in under a new migration fixes all...

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 till you make it.

$ rake db:migrate
== 20150105131615 AddBackBooleansMigration: migrating =========================
-- add_column(:bookings, :lender_accepted, :boolean)
   -> 0.0333s
...
== 20150105131615 AddBackBooleansMigration: migrated (0.0370s) ================

Upvotes: 1

Related Questions