PostgreSQL ODBC connection from Ubuntu ¶

Install the basic config tools for the UNIX ODBC:

sudo apt-get install unixodbc unixodbc-dev

Install ODBC drivers for PostgreSQL:

sudo apt-get install odbc-postgresql

Configure the PostgreSQL and ODBC driver.

File /etc/odbcinst.ini:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

File /etc/odbc.ini:

[Gallery]
Driver = PostgreSQL Unicode
Description = ODBC connection to Gallery via PostgreSQL Unicode
Trace = No
#Trace = Yes
#TraceFile = /tmp/sql.log
Servername = localhost
Port = 5432
#Protocol = 8.4
Database = gallery
UserName = ****
Password = ****
#ReadOnly = No

Test the ODBC to PostgreSQL connection by running the isql command, which reads the /etc/odbc.ini file:

$ isql -v gallery <username> <password>

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL> select count(*) from users_user;
+---------------------+
| count               |
+---------------------+
| 3                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched