Rails - SQLite3::SQLException:靠近“USING":语法错误 [英] Rails - SQLite3::SQLException: near "USING": syntax error

问题描述

我最近将我的应用程序部署到 Heroku，为此我不得不对我的一个表中的几列进行一些修改.具体来说，我做了以下事情:

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.我添加了 'boolean USING CAST(cancel AS boolean)' 部分，因为在尝试执行 heroku run rake db:migrate 时，它给出了这个错误:

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: 列cancel"无法自动转换为布尔类型

此问题已修复，并且在 Heroku 上一切正常.

This has been fixed and everything works fine on Heroku.

现在的问题是，当我尝试在本地运行 rake db:migrate 时，出现以下错误:

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)

我可以看到 boolean USING CAST(cancel AS boolean) 已添加到 cancel 列，但我不知道如何解决这个问题?

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?

推荐答案

如果我没猜错，您正在使用 SQLite 进行开发，但部署到 Heroku 上的 Postgres.

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

这就是的问题.解决方案是在本地也使用 Postgres 进行开发.最好用同版本.SQL 实现有很多不同之处，只要您使用基本 DML 命令以外的任何其他命令，您就会不断遇到障碍.

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.

在 SQLite 中没有像这样的 PostgreSQL DDL 命令:

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

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

ALTER TABLE 的 SQLite 实现非常有限.根据文档:

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

SQLite 支持有限的 ALTER TABLE 子集.更改表SQLite 中的命令允许用户重命名表或添加新表列到现有表.

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.

对于表架构的所有其他更改，SQLite 手册中有一个配方.

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

相关答案:

• 适用于 SQLite3 LIKE"的通用 Ruby 解决方案或 PostgreSQL ILIKE"?

这篇关于Rails - SQLite3::SQLException:靠近“USING":语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！