CODEWITHSUNDEEP
ruby-on-railspostgresqlsyntax-error
Ryan.lay
Ryan.lay

Reputation: 1761

PG syntax error

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

Answers (1)

Richard Huxton
Richard Huxton

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

Related Questions