Reputation: 1761
I've been trying to convert the fedena project built on ruby on rails for my school from mySQL to postgreSQL for heroku deployment and ran into the following error:
Showing app/views/class_timings/index.html.erb where line #40 raised:
PG::Error: ERROR: syntax error at or near "."
LINE 1: SELECT `batches`.*,CONCAT(courses.code,'-',batches.name) as ...
^
: SELECT `batches`.*,CONCAT(courses.code,'-',batches.name) as course_full_name FROM "batches" INNER JOIN "courses" ON "courses".id = "batches".course_id WHERE ("batches"."is_deleted" = 'f' AND "batches"."is_active" = 't') ORDER BY course_full_name
Extracted source (around line #40):
37: <label ><%= t('select_a_batch') %>:</label>
38: <div class="text-input-bg">
39: <%= select :batch, :id,
40: @batches.map {|b| [b.full_name, b.id] },
41: {:prompt => "#{t('common')}"},
42: {:onchange => "#{remote_function(
43: :url => { :action => 'show' },
44: :with => "'batch_id='+value",
45: :before => "Element.show('loader')",
46: :success => "Element.hide('loader')"
47: )}"} %>
Obviously, I'm new to programming! Please help.
The controller:
class_timings_controller.rb
def index
@batches = Batch.active
@class_timings = ClassTiming.find(:all,:conditions => { :batch_id => nil,:is_deleted=>false}, :order =>'start_time ASC')
end
Upvotes: 1
Views: 1130
Reputation: 22972
Both the backtick quotes ` and the CONCAT
function are non-standard SQL.
If you really want to quote table-names you'll need to use double-quotes. There is a text-concatenation operator you'll want in Postgresql called ||
.
So you'll have something like:
SELECT "batches".*, (courses.code || '-' || "batches"."name") as course_full_name ...
Be consistent - if you are quoting tables names in some places, quote them everywhere.
This is going to be slow going until you understand the syntax of both databases at at least a basic level. Presumably there's not too much raw SQL since you're using rails though.
Tip: set aside a couple of hours and skim through the manuals of both systems. At least you'll know where to look to check details then.
Upvotes: 2