ruby-on-rails - ruby rails : SQLite3 to PostgreSQL null date

Question

我在开发中使用的是 SQLite3 数据库，现在已经开始使用 heroku 来托管我的应用程序。 Heroku 只支持我在 GEM 文件中修改过的 PostgreSQL

group :development, :test do
 gem 'sqlite3'
end
group :production do
  gem 'pg'
end

现在，一切正常，除了当我查询日期时，我得到这个错误

    ActionView::Template::Error (PG::Error: ERROR:  invalid input syntax for type date: ""
2012-09-12T10:03:03+00:00 app[web.1]: 
2012-09-12T10:03:03+00:00 app[web.1]: LINE 1: ...-12' AND '2012-09-12' OR DATE(start_date) BETWEEN '' AND '')...
2012-09-12T10:03:03+00:00 app[web.1]:                                                              ^
2012-09-12T10:03:03+00:00 app[web.1]:     137: <% if @project_search.total_entries > 0 %>
2012-09-12T10:03:03+00:00 app[web.1]:     136: 
2012-09-12T10:03:03+00:00 app[web.1]: : SELECT COUNT(*) FROM "projects"  WHERE (client LIKE '%%' AND industry LIKE '%%' AND role LIKE '%%' AND tech LIKE '%%' AND business_div LIKE '%%' AND project_owner LIKE '%%' AND exception_pm LIKE '%%' AND status LIKE '%%' AND (DATE(start_date) BETWEEN '2011-09-12' AND '2012-09-12' OR DATE(start_date) BETWEEN '' AND '') AND keywords LIKE '%%')):
2012-09-12T10:03:03+00:00 app[web.1]:     138: <% if @search_performed %>
2012-09-12T10:03:03+00:00 app[web.1]:     140: <style>
2012-09-12T10:03:03+00:00 app[web.1]:     139:

这是我的 project.rb，因为我认为这是我需要更改的地方:

class Project < ActiveRecord::Base
  attr_accessible :edited_first_name, :edited_last_name, :first_name, :last_name, :business_div, :client, :customer_benifits, :edited_date, :end_date, :entry_date, :exception_pm, :financials, :industry, :keywords, :lessons_learned, :project_name, :project_owner, :role, :start_date, :status, :summary, :tech

#validates_presence_of :business_div, :client, :customer_benifits, :end_date, :exception_pm, :financials, :industry, :keywords, :lessons_learned, :project_name, :project_owner, :role, :start_date, :status, :summary, :tech



def self.search(search_client, search_industry, search_role, search_tech, search_business_div, search_project_owner, search_exception_pm, search_status, search_start_date_dd, search_start_date_A, search_start_date_B,  search_keywords) 
  return scoped unless search_client.present? || search_industry.present? || search_role.present? || search_tech.present? || search_business_div.present? || search_project_owner.present? || search_exception_pm.present? || search_status.present? || search_start_date_dd.present?  || search_start_date_A.present?  || search_start_date_B.present?  || search_keywords.present?


todays_date = DateTime.now.to_date

if !search_start_date_A.blank? or !search_start_date_B.blank?
    search_start_date_A = Date.parse(search_start_date_A).strftime("%Y-%m-%d")
    search_start_date_B = Date.parse(search_start_date_B).strftime("%Y-%m-%d")
    todays_date = ""
    end

if search_start_date_dd.blank?
    todays_date = ""
end


if (search_start_date_A.blank? or search_start_date_B.blank?) and search_start_date_dd.blank?

where(['client LIKE ? AND industry LIKE ? AND role LIKE ? AND tech LIKE ? AND business_div LIKE ? AND project_owner LIKE ? AND exception_pm LIKE ? AND status LIKE ? AND keywords LIKE ?', 
      "%#{search_client}%", "%#{search_industry}%" , "%#{search_role}%" , "%#{search_tech}%" , "%#{search_business_div}%" , 
      "%#{search_project_owner}%" , "%#{search_exception_pm}%" , "%#{search_status}%", 
       "%#{search_keywords}%"
    ])

else

where(['client LIKE ? AND industry LIKE ? AND role LIKE ? AND tech LIKE ? AND business_div LIKE ? AND project_owner LIKE ? AND exception_pm LIKE ? AND status LIKE ? AND (DATE(start_date) BETWEEN ? AND ? OR DATE(start_date) BETWEEN ? AND ?) AND keywords LIKE ?', 
      "%#{search_client}%", "%#{search_industry}%" , "%#{search_role}%" , "%#{search_tech}%" , "%#{search_business_div}%" , 
      "%#{search_project_owner}%" , "%#{search_exception_pm}%" , "%#{search_status}%", 
      search_start_date_dd, todays_date , search_start_date_A, search_start_date_B, "%#{search_keywords}%"
    ])
end
end


def self.paginated_for_index(projects_per_page, current_page)
    paginate(:per_page => projects_per_page, :page => current_page)
  end

end

似乎 SQLite3 可以将 '' 处理为空日期，但 PG 不能

有没有人对此有一些解决方案？

我是 Rails 的新手，所以请在回答时记住这一点。提前致谢。

Answer 1

NULL 和 ''(空字符串)是不同的东西。一些数据库，例如 MySQL 和显然是 SQLite，允许您将它们视为同一事物，但它们不是。 '' 不是有效日期。 PostgreSQL 也不接受另一种常见的 MySQL 主义，0000-00-00；请注意这一点，以防您习惯将其用作占位符或“无效”值。

您需要修复您的应用程序，以便它为空日期发送 NULL。我希望将日期设置为应用程序级别的 null/nil 值(Ruby 似乎使用 nil 作为它的 null 值)而不是 "" 就可以了.