CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
zhu chen
zhu chen

Reputation: 215

Rails - PG::DatatypeMismatch: ERROR

So it's a very beginner question, I am getting the following error. I know that I need to convert it over to string type. I have tried many kinds of conversion method, but yet to work. Hard coding this field as something like '2018-01-01' works. The param content that I am getting back is correct. It is the type that is creating this error. I even try to convert it over to a date type and tested the type and returned true, but still fail to succeed. Let me know if this makes sense.

PG::DatatypeMismatch: ERROR:  column "order_date" is of type date but expression is of type integer

My expresson

created_at = (params[:created_at])

Have tried

created_at = (params[:created_at]).to_date
created_at = (params[:created_at]).to_s

I am trying to get the params[:created_at] from the payload and use the following code to insert into psql. So I think I should go with the create action. Please let me know what I am doing wrong.

def create
  created_at = (params[:created_at])
  puts created_at
  sql = "insert into api.salesorder(site, order_date,sale_type,sales_rep,terms,customer_number) values('WHS',#{created_at},'CUST','HOUSE','PRE','123456')"
  results = ActiveRecord::Base.connection.execute(sql)
end

Upvotes: 1

Views: 442

Answers (2)

Mike Gorski
Mike Gorski

Reputation: 1228

If you still want to use direct SQL (which I advise against in Rails, since Rails does the SQL for you), you need to change it to wrap your date in single quotes.

sql = "insert into api.salesorder(site, order_date, sale_type,sales_rep, terms, customer_number) values('WHS','#{created_at}','CUST','HOUSE','PRE','123456')"

A better solution would be to leverage Rails to do the SQL work for you. Something like this:

# in app/models/salesorder.rb
class Salesorder < ActiveRecord::Base
end

# in app/controller/salesorder_controller.rb
class SalesorderController < ApplicationController
  def create
    @salesorder = Salesorder.new
    @salesorder.order_date = params[:created_at]
    @salesorder.site = 'WHS'
    @salesorder.sale_type = 'CUST'
    @salesorder.sales_rep = 'HOUSE'
    @salesorder.terms = 'PRE'
    @salesorder.customer_number = '123456'

    if @salesorder.save
      redirect_to @salesorder, notice: 'Salesorder was successfully created.'
    else
      render :new
    end
  end
end

Upvotes: 3

neymar sabin
neymar sabin

Reputation: 13

You could do something like created_at = Date.parse(params[:created_at]), but i don't think it's nescessary. Some more information might be helpful. Is your params an integer type?

Upvotes: 0

Related Questions