The most straightforward way to hook up Excel or most other Windows programs to a PostgreSQL database is ODBC.
ODBC lets you connect to a local or remote Postgres.
To do this, you need to get an ODBC driver for Postgres. But which one? In most cases, you’re going to want the free official ODBC driver from Postgres. Its documentation is so poor that I overlooked it for a day before figuring out that it’s the real deal. The driver’s homepage makes it look like the last version was in 2005, but in fact there has been releases in October 2010 (and by the time you’re reading this, probably more recent ones).
Get the Windows installer here: http://www.postgresql.org/ftp/odbc/versions/msi/
Make sure you install the right version. Even if you have 64 bit Windows you’ll need the 32 bit ODBC driver if the program that’s connecting to the database (like Excel) is 32 bit. For Excel specifically, File > Help says whether it’s 32 or 64 bit.
The official installation and configuration FAQ: http://psqlodbc.projects.postgresql.org/faq.html
Set up a DSN
Once you’ve installed the ODBC driver you need to make it available to programs like Excel.
You do this via your computer’s “Data Sources (ODBC)” panel. There’s a 32 bit and a 64 bit version, you gotta make sure you’re looking in the right one: http://support.microsoft.com/kb/942976
Once it’s open, click either the User DSN or System DSN tab:
- User DSN: is available just for the current user.
- If you’re just using it within Excel or Access, this is the one you want.
- System DSN: is available for all users and services on the machine.
Click ‘Add…’ and select a PostgreSQL driver. There’s two to choose from:
- PostgreSQL Unicode: use this if your database was set up with the UTF-8 character set.
- PostgreSQL ANSI: use this if your database was set up with a LATIN character set.
Fill in your server and database details.
- Data Source: make a friendly name of the data source that you’ll later use when looking up this DSN in Excel or other programs.
- Database: use ‘postgres’ to connect to all databases, or type the name of a specific database.
- Server: domain name or IP address of server
- User Name: postgres database username
- Password: postgres database password
Go Forth And Use ODBC
Now go to the program in which you need to connect to Postgres and use whatever ODBC connection UI it provides.