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) — deceze
– deceze ♦, Commented Sep 14, 2012 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
– Gordon Linoff, Commented Sep 14, 2012 at 15:41
It would be very useful to know how you got that error in the first place. — Ryan Bigg
– Ryan Bigg, Commented Sep 14, 2012 at 15:46

Community · Accepted Answer · 2017-05-23 12:14:19Z

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, 2017 at 12:14

CommunityBot

11 silver badge

answered Sep 14, 2012 at 15:56

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:26:55Z

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.

Collectives™ on Stack Overflow

MySQL to Postgres conversion

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related