Search

Peripheral Links

Header And Logo

• Home
• About
• Downloads
• Documentation
• Community
• Developers
• Support

Porting from FileMaker Pro to PostgreSQL

1. Aspects of a FileMaker database, and it's relationship to PostgreSQL
2. Field Definitions
3. Data export from FileMaker and import to PostgreSQL
4. FileMaker and ODBC

1. FileMaker databases

FileMaker Pro is a database originally developed on the Macintosh by Claris, a subsidiary of Apple Computer.  Around 1998, Claris split, some components going with Apple (like ClarisWorks, which became AppleWorks) and FileMaker became it's own company.  FileMaker Pro is now the only regularly used cross-platform (PC and MAC) database system.  In general, it is more user friendly than Microsoft Access.  ODBC and SQL have come relatively late to FileMaker, thus the DBMS is not as standards-compliant as other are.

FileMaker files are the equivalent of individual PostgreSQL tables. FileMaker is a relational DBMS, but the relationships are between different files in FileMaker Pro. Often, what is considered a single database is several or many individual FileMaker files. So when converting from FileMaker to PostgreSQL, take each file one at a time, and recreate them as PostgreSQL tables.

FileMaker Pro databases, in a generalized description, have 4 basic components:

• User Interface (UI)
  These are the text boxes, buttons, menus, layouts, etc. available allowing you to enter, display and manipulate data, as well as change the format of reporting of data.
  
  
• Field Definitions
  The data definitions on individual fields you create.
  
  
• The Data
  The data itself.
  
  
• Scripts
• Scripts written in FileMaker for added functionality.

"Field Definition" components and the FileMaker data however, can be converted to PostgreSQL tables fairly easily.

2. Field Definitions

In order to convert a database from FileMaker to PostgreSQL, the most common method will be to manually create each table, and make decisions about field type definitions.

There are several different data types in FileMaker; text, number, date, time, container, calculation, summary and global.

Date and time definitions have their obvious counterparts in PostgreSQL.

Text is just like "text" in PostgreSQL, basically unlimited in size.  There are no equivalents in FileMaker to char and varchar data types.  If you have small text fields, for things like Zip Codes, Phone numbers and the like, you might want to use the varchar or char data types instead of text.

In FileMaker, the number type does not have a precision - it can be an integer, or a float.  What distinguishes them in a FileMaker database is the formatting of the output, not the storing of the number - the number is stored as floating point.  So when porting to PostgreSQL, determine what fields can be integers and what fields should be floating point.

The other data types provide for very specific problems in terms of converting the database from FileMaker to PostgreSQL.  Calculation and Summary field types in FileMaker will have to be ported either by using functions within PostgreSQL, or by including these calculations in the programming interaction between PostgreSQL and the front-end.

3. Data export from FileMaker and import into PostgreSQL

Exporting data from FileMaker is quite easy.  There are a number of formats available, and many provide important options that might make life a little easier.

The easiest method is probably getting data as delimited text, then creating a script or program to get that information into the PostgreSQL database.  If you'd like a copy of the script I use, email me and I'll send you the latest version.

As well, hand editing this file to make it correspond to INSERT statements is another possibility, although time consuming, if there are many records.

4. FileMaker Pro and ODBC

FileMaker Pro is ODBC compliant, so it's possible to use FileMaker Pro to get data from a PostgreSQL database in this way.

Michelle Murrain, Ph.D.