Rails - SQLite3::SQLException: near "USING": syntax error

Question

I've recently deployed my app to Heroku and in doing so I had to make some amends to a couple of columns in one of my tables. Specifically, I did the following:

class ChangeCancelColumnOrders < ActiveRecord::Migration
  def change
    change_column :orders, :cancel, 'boolean USING CAST(cancel AS boolean)'
  end
end

I.E. I added the 'boolean USING CAST(cancel AS boolean)' part because when trying to do a heroku run rake db:migrate it was giving this error:

PG::DatatypeMismatch: ERROR: column "cancel" cannot be cast automatically to type boolean

This has been fixed and everything works fine on Heroku.

Now the problem is that when I attempt to run rake db:migrate locally, I get the following error:

SQLite3::SQLException: near "USING": syntax error:
CREATE TABLE "orders" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "created_at" datetime, "updated_at" datetime, "first_name" varchar(255), "last_name" varchar(255), "email" varchar(255), "address_1" varchar(255), "address_2" varchar(255), "city" varchar(255), "postal_code" varchar(255), "country_code" varchar(255), "shipped_date" date, "total_price" integer, "shipped" boolean DEFAULT 'f', "cancel" boolean USING CAST(cancel AS boolean) DEFAULT 'f', "cancel_date" date)

I can see that boolean USING CAST(cancel AS boolean) has been added to the cancel column but I don't know how to go about resolving this?

This is the perfect example why you want your development and production database to be the same flavor. Since you cannot change the fact that heroku uses PG, then you should change your development database to PG too. — JTG, Jul 28 '14 at 15:46
@james what did you end up doing to fix this? I'm having the same issue. — Tony Tambe, Oct 12 '14 at 04:26

score 1 · Accepted Answer · edited May 23 '17 at 12:20

If I got that correctly, you are developing with SQLite, but deploying to Postgres on Heroku.

This is the problem. The solution is to develop with Postgres locally as well. Best with the same version. There are numerous differences in the SQL implementation and you will keep running into obstacles as soon as you use anything else than basic DML commands.

There is nothing equivalent in SQLite like this PostgreSQL DDL command:

ALTER TABLE orders ALTER cancel TYPE boolean USING CAST(cancel AS boolean);

The SQLite implementation of ALTER TABLE is very limited. Per documentation:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table.

For all other changes to the table schema there is a recipe in the SQLite manual.

Rails - SQLite3::SQLException: near "USING": syntax error

1 Answers1