CODEWITHSUNDEEP
rubypostgresqlpg
Dwayne Skyers
Dwayne Skyers

Reputation: 39

PG gem returning columns in random order

Is it normal for the latest version of the 'pg' gem to return the results of a db query columns in a random order? I would expect that the 'pg' gem would behave in the same manner as the mysql2 gem, returning the data in the order it is presented when querying the database directly via the command line. Maybe there is a better gem out there that I should be using. This example of my results below are a result of useing the same query, "select * from books"

First execution:

"The Shining","9","7808","4156"
"Dune","15","4513","1866"
"2001: A Space Odyssey","15","4267","2001"
"The Cat in the Hat","2","1608","1809"
"Bartholomew and the Oobleck","2","1590","1809"

Second execution:

"4156","The Shining","9","7808"
"1866","Dune","15","4513"
"2001","2001: A Space Odyssey","15","4267"
"1809","The Cat in the Hat","2","1608"
"1809","Bartholomew and the Oobleck","2","1590"

Third execution:

"9","The Shining","7808","4156"
"15","Dune","4513","1866"
"15","2001: A Space Odyssey","4267","2001"
"2","The Cat in the Hat","1608","1809"
"2","Bartholomew and the Oobleck","1590","1809"

Code to return results:

confrom = PG::Connection.new(:user => <myuser>, :password => <mypw>, :port => 5432, :host => <myhost>, :dbname => 'booktown')
results = confrom.exec("select * from books")
results.each do |row|
  row.each_value do |x|
    puts x
  end
end

Upvotes: 1

Views: 439

Answers (1)

mu is too short
mu is too short

Reputation: 434755

I'm guessing that you're using Ruby 1.8.X where Hashes don't have any defined order. I tried similar code with pg and Ruby 1.9.3 and everything came out in the same order each time.

In here:

results.each do |row|
  #...
end

the row will be a simple Hash. So when you row.each_value, you can't depend on your x values coming out in any particular order in Ruby 1.8; in 1.9 you should get your x values in insertion order.

But all of that really doesn't matter anyway:

  1. You should avoid select * ... except when you're manually poking around inside the database; you should always say select c1, c2, ... so that you know exactly what you're getting from the database and what order everything will come out in. If someone adds a column or if a column changes position, then all your code will break.
  2. You have a Hash in row so you can access the results by name and this is easier and less error prone than mucking about with positions.

Upvotes: 5

Related Questions