CODEWITHSUNDEEP
postgresqlruby-on-rails-4bigdecimal
nulltek
nulltek

Reputation: 3337

What postgres data type should I use for sql that returns a big decimal in Rails

I have an app where I'm trying to generate a dollar amount into a new AR record called Metric. I've written the following simple class method to generate a new metric method:

  def self.last_year
    m = Metric.new
    m.name = Figaro.env.company_name
    m.metric_type = "Last Year"
    m.amount = ActiveRecord::Base.connection.execute("select sum(dst_amt::DECIMAL) from distributions where dst_code like 'COL%' and dst_posted_date::DATE BETWEEN (select (date_trunc('month', current_date)) - INTERVAL '13 Month') and (select (date_trunc('month', current_date)) - INTERVAL '366 Day')")
    m.save
  end

This basically works and creates the Metric record, however the amount when I output it in the console after running this method is:

<BigDecimal:10173c470,'0.0',9(18)>

When I run the SQL in Postico or Navicat raw the number that results is: 1043.88. So my question(s) are:

1.) Based off of this query and raw result, what datatype in postgres should I be using for the amount field? I tried decimal but it writes the output as a BigDecimal and I can't read it, I've also tried float, but when I run the class method it gives me a NoMethodError to_f on the class.

2.) Is this the best way to run the SQL from within the class? My SQL is correct when I run it via Navicat or Postico, but in the class I output a big decimal.

My ultimate goal is to generate metric records on multiple servers then somehow get them over to a target server maybe via a 2nd dbase connection or simply export to csv, scp over, and import via csv on the target server. Really this should be an API type of scenario but I don't have that sort of time and need to just get something working today hopefully.

If my code and/or question is not clear, please let me know and I'll edit.

Update So when I try to break this method down in the console I run this:

a = ActiveRecord::Base.connection.execute("select sum(dst_amt::DECIMAL) from distributions where dst_code like 'COL%' and dst_posted_date::DATE BETWEEN (select (date_trunc('month', current_date)) - INTERVAL '13 Month') and (select (date_trunc('month', current_date)) - INTERVAL '366 Day')")

Which gives me: => #<PG::Result:0x0000010d84c958 status=PGRES_TUPLES_OK ntuples=1 nfields=1 cmd_tuples=1>

Then from the console I call a[0] I get the hash value of => {"sum"=>"1101.88"}

So I guess my question has expanded. Am I even calling the SQL properly to populate the amount field in my method? And if so, how do I parse this output to return a decimal of 1101.88 (or whatever the result is) to the amount column which is a decimal type field?

Update again

Here is my refactored method

  def self.last_year_mtd
    m = Metric.new
    m.name = Figaro.env.company_name
    m.metric_type = "Last Year MTD"
    sql = ActiveRecord::Base.connection.execute("select sum(dst_amt::DECIMAL) from distributions where dst_code like 'COL%' and dst_posted_date::DATE BETWEEN (select (date_trunc('month', current_date)) - INTERVAL '13 Month') and (select (date_trunc('month', current_date)) - INTERVAL '366 Day')")
    total = sql[0]["sum"].to_s
    m.amount = total
    m.save
  end

So when I run this from the console it creates a new Metric record. So if I create two records I need to do arithmetic on it. So I tried this out.

Ran last_year_mtd from the console and a similar method which has a different query and generates a different big decimal.

Then I assign a = Metric.first and b = Metric.last to get two different objects.

Then I do the arithmetic: 2.1.1 :012 > (b.amount - a.amount).to_s => "1396.4"

This looks to be working. Calling .to_s on the arithmetic seems to have done the trick.

I'm sure this method could be cleaner, so if anyone has ideas on a refactor I'd greatly appreciate it.

Upvotes: 0

Views: 867

Answers (1)

David Aldridge
David Aldridge

Reputation: 52356

Use a decimal.

If you need to display the value then use to_s to convert it to a string, or to_f to convert it to float. However some decimals cannot be accurately represented as a float, and if all you want to do is look at the value then a string representation is appropriate.

Upvotes: 1

Related Questions