Exporting to Postgres with ODBC

deek

I'm working on setting up the export using ODBC. I tend to use Postgres as my DB of choice so I downloaded the ODBC drivers and updated the connection string. I was able to test the connection to the database but I ran into an issue.

When I clicked `Validate Table Setup` I get the error

CREATE TABLE encounter_table (encid CHAR(8), title VARCHAR(64), starttime TIMESTAMP, endtime TIMESTAMP, duration INT, damage BIGINT, encdps DOUBLE, zone VARCHAR(64), kills INT, deaths INT);
System.Data.Odbc.OdbcException (0x80131937): ERROR [42704] ERROR: type "double" does not exist;
Error while executing the query

So the Postgres DB doesn't know of a type called DOUBLE, in Postgres it would be a DOUBLE PRECISION.

I imagine there might be a couple more of these issues in other tables. I would love to help make this work. I don't see any source available so cannot submit a patch but please let me know what other information would be useful.

EQAditu

The original implementation was done for MySQL a very long time ago but I had to start somewhere.

In the database setup page there is a DataSource Compatibility Hacks section.  If you press the Reset button it will fill out some examples that I had to use to make it base-line compatible with a few different solutions.  You'll notice that some of them are already for PostgreSQL, but evidently it's not enough for you.

You'll want to make a new entry with something in the Connection String Match that matches your current connection string with PostgreSQL.  Then enter "DOUBLE" for Find and enter "DOUBLE PRECISION" for Replace.

You repeat these steps for anything PostgreSQL doesn't like.  If you need help with some more complex Regular Expressions, post again.

deek

Ah I didn't realize that was what the section was designed for. Thank you for the clear example. I will update this post when I recheck things.

I did temporarily use the MariaDB ODBC 3.0 Driver without any issues whatsoever (as to be expected)