Postgres ODBC Connections on Windows

This is a living post. Check back for updates as I learn more.

Download the ODBC driver

You can download the ODBC driver for Postgres from the EnterpriseDB website. Choose the version that matches your Postgres installation and your Windows architecture (most modern desktops will do 64-bit).

Install the ODBC driver

Run the installer you downloaded and follow the prompts to install the ODBC driver. Make sure to select the appropriate options for your system. You may need to install additional components, such as the Visual C++ Redistributable, if prompted.

Configure the ODBC Data Source

1. Open the ODBC Data Source Administrator. You can find it by searching for “ODBC” in the Windows Start menu.
2. In the ODBC Data Source Administrator, go to the “System DSN” tab.
3. Click “Add” to create a new data source.
4. Select the “PostgreSQL Unicode” driver (or “PostgreSQL ANSI” if you prefer) and click “Finish.”
5. Fill in the required fields:
   1. Data Source Name (DSN): Give your data source a name that you will recognize.
   2. Server: Enter the hostname or IP address of your Postgres server
   3. Port: Enter the port number (default is 5432).
   4. Database: Enter the name of the database you want to connect to.
   5. User Name: Enter your Postgres username.
   6. Password: Enter your Postgres password.
   7. SSL Mode: Choose the appropriate SSL mode for your connection (e.g., “Prefer” or “Require” if your server supports SSL).
   8. Save Password: Check this box if you want to save your password for future connections.
   9. Test Connection: Click the “Test” button to verify that your connection settings are correct. If the test is successful, click “OK” to save the data source.
6. Your ODBC data source is now configured and ready to use.

7. Use the ODBC Data Source

8. You can now use the ODBC data source in applications that support ODBC connections, such as Microsoft Excel, Power BI, or any programming language that supports ODBC (e.g., Python, R, C#).
9. In your application, select the ODBC data source you created and provide any additional connection
10. parameters if needed.

11. Troubleshooting

12. If you encounter issues connecting to your Postgres database via ODBC, check the following

ALTER ROLE spatial_user IN DATABASE mydatabase SET search_path = "myschema,public";