Migrating Access Databases to PostgreSQL
MDB Viewer supports exporting tables to SQL dumps that are compatible with PostgreSQL. To upload the data to a server you must execute the SQL dump with a separate application, for example using pgAdmin. pgAdmin is a free PostgreSQL client application.
For general information about SQL files, see Exporting as SQL.
Migrating Step by Step (using pgAdmin)
In MDB Viewer:
- Click the "Export SQL" toolbar button and select the PostgreSQL format
- Choose whether to export the table structure, data, or both.
In pgAdmin:
- Connect to the PostgresSQL server and select the target database
- Click the "SQL" toolbar button
- Click the "Open File" toolbar button and select the SQL file create with MDB Viewer
- Click the "Execute Query" button
After the query has finished, your tables will show up in the "public" schema.
Access Types vs. PostgreSQL Types
PostgreSQL supports similar column types as MS Access, but they use different names.
| Access Type | PostgreSQL Type |
|---|---|
| Boolean | BOOLEAN |
| Byte | INTEGER |
| Integer | INTEGER |
| Long Integer | INTEGER |
| GUID | TEXT |
| Decimal | DOUBLE |
| Money | DOUBLE |
| Float | DOUBLE |
| Double | DOUBLE |
| Date/Time | TIMESTAMP |
| Text | TEXT |
| Memo | TEXT |
| Ole | BYTEA |
To prevent compatibility issues, strings are quoted using escape string constants. (see Section 4.1.2.2 of the PostgreSQL documentation)