Talk:Converting MySQL to PostgreSQL

There is no DATETIME type in PostgreSQL. I have an impression MySQL and PostgreSQL types are replaced in the table.

Cf. for a difference between DATETIME and TIMESTAMP in MySQL.

There should be some info that you can retrieve data like "SHOW table" (MYSQL) in PostgreSQL too by using SQL statements or programming language interfaces, not just by tools.

Look for pg system tables and/or INFORMATION_SCHEMA.
 * information_schema is in MySQL 5 as well. I don't know if they're exactly the same in both but it might be worth mentioning.

Why not use MD5 instead of password for authentication?

Helper tool?
Isn't it possible to create one program which one could give the mysql dump and it converts it to a pg-insertable dump? Or is there already one I have greatly missed? (If so, please link to it ;) AFAIS that would be a bit of work (and maybe a little headache too), but no rocket-science. Am I wrong, or hasn't the effort simply been made yet?

I suggest mentioning this in the intro section (to clarify why one needs this book at all).

REPLACE and ON DUPLICATE KEY UPDATE replacements
Personally I feel that the provided alternative isn't the most appropriate one. It isn't very clear how to use it as is and there is a risk of a "duplicate key violation" when multiple transactions try to do the same. PostgreSQL themselves already provide a pretty good alternative and with a slight modification it can be made clear how people can apply it:

CREATE FUNCTION insertOrUpdate(key INT, data TEXT, [...]) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE table SET datacolumn = data WHERE keycolumn = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO table(keycolumn, datacolumn) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql;

Yet another alternative would be: CREATE RULE insertOrUpdate AS 	ON INSERT TO table WHERE EXISTS(SELECT 1 FROM table WHERE keycolumn = NEW.key) DO INSTEAD (UPDATE table SET datacolumn = NEW.data WHERE keycolumn = NEW.key);

These alternatives are technically only an alternative for ON DUPLICATE KEY UPDATE, just like the current alternative, not REPLACE. I found the changes a bit too drastic to just apply so I'd like to hear your opinions first. Prodoc (talk) 21:11, 8 September 2010 (UTC)

Using_pgloader
Are there meant to be three forward slashes in the code example under Using pgloader?

pgloader mysql://user@localhost/dbname postgresql:///dbname

--212.237.130.90 (discuss) 10:43, 11 November 2018 (UTC)