MySQL to Postgres conversion

Question

Does anyone know what could be causing this error? I'm trying to convert a MySQL site to Postgres so I can host on Heroku. I'm new to database syntax, and this problem has been bugging me for days.

PG::Error: ERROR:  syntax error at or near "ON"
LINE 1: ...tores ("key", "value") VALUES ('traffic:hits', 0) ON DUPLICA...
                                                             ^

Here's the github page for the site I'm trying to convert. https://github.com/jcs/lobsters

This is the query. I added the backslash double quotes in replace of `.

if Rails.env == "test"
  Keystore.connection.execute("INSERT OR IGNORE INTO " <<
    "#{Keystore.table_name} (\"key\", \"value\") VALUES " <<
    "(#{q(key)}, 0)")
  Keystore.connection.execute("UPDATE #{Keystore.table_name} " <<
    "SET \"value\" = \"value\" + #{q(amount)} WHERE \"key\" = #{q(key)}")
else
  Keystore.connection.execute("INSERT INTO #{Keystore.table_name} (" +
    "\"key\", \"value\") VALUES (#{q(key)}, #{q(amount)}) ON DUPLICATE KEY " +
    "UPDATE \"value\" = \"value\" + #{q(amount)}")
end

possible duplicate of [Insert, on duplicate update (postgresql)](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql) — deceze, Sep 14 '12 at 15:39
Well, clearly the problem is a syntax error at or near the word "ON" in some query. Could you please provide the query in your question? — Gordon Linoff, Sep 14 '12 at 15:41
It would be very useful to know *how* you got that error in the first place. — Ryan Bigg, Sep 14 '12 at 15:46

score 2 · Answer 1 · edited May 23 '17 at 12:14

2

Postgres' INSERT doesn't support MySQL's variant INSERT ... ON DUPLICATE KEY UPDATE.

For alternatives see the answers to this question.

edited May 23 '17 at 12:14

Community

1
1

answered Sep 14 '12 at 15:56

Milen A. Radev

60,241
22
105
110

score 0 · Answer 2 · edited May 23 '17 at 10:26

I was working on this exact code last night, here's an initial take at how I fixed it, following this answer :

def self.put(key, value)
key_column = Keystore.connection.quote_column_name("key")
value_column = Keystore.connection.quote_column_name("value")

if Keystore.connection.adapter_name == "SQLite"
  Keystore.connection.execute("INSERT OR REPLACE INTO " <<
    "#{Keystore.table_name} (#{key_column}, #{value_column}) VALUES " <<
    "(#{q(key)}, #{q(value)})")

elsif Keystore.connection.adapter_name == "PostgreSQL"
  Keystore.connection.execute("UPDATE #{Keystore.table_name} " +
    "SET #{value_column} =#{q(value)} WHERE #{key_column} =#{q(key)}")
  Keystore.connection.execute("INSERT INTO #{Keystore.table_name} (#{key_column}, #{value_column}) " +
    "SELECT #{q(key)}, #{q(value)} " +
    "WHERE NOT EXISTS (SELECT 1 FROM #{Keystore.table_name} WHERE #{key_column} = #{q(key)}) "
    )

elsif Keystore.connection.adapter_name == "MySQL" || Keystore.connection.adapter_name == "Mysql2"
  Keystore.connection.execute("INSERT INTO #{Keystore.table_name} (" +
    "#{key_column}, #{value_column}) VALUES (#{q(key)}, #{q(value)}) ON DUPLICATE KEY " +
    "UPDATE #{value_column} = #{q(value)}")

else
  raise "Error: keystore requires db-specific put method."

end

true
end

There's a number of things to be fixed in the lobsters codebase beyond just this for postgres compatability - found mysql specific things in other controller files. I'm currently working on them at my own lobsters fork at https://github.com/seltzered/journaltalk - postgres fixes should be commited on there in the coming day or two.

MySQL to Postgres conversion

2 Answers2