Postgresql odbc driver linux

How To Install odbc-postgresql on Ubuntu 20.04

In this tutorial we learn how to install odbc-postgresql on Ubuntu 20.04.

What is odbc-postgresql

This package provides a driver that allows ODBC-enabled applications to access PostgreSQL databases. ODBC is an abstraction layer that allows applications written for that layer to access databases in a manner that is relatively independent of the particular database management system.

You need to install this package if you want to use an application that provides database access through ODBC and you want that application to access a PostgreSQL database. This package would need to be installed on the same machine as that client application; the PostgreSQL database server can be on a different machine and does not need any additional software to accept ODBC clients.

If you want to write software that can access a database through the ODBC abstraction layer, you need to install the unixODBC driver manager development package unixodbc-dev, and possibly additional packages for language bindings. This driver package is only used at run time. Description-md5: ca9991df6514508e654fa440ff1560cd

There are three ways to install odbc-postgresql on Ubuntu 20.04. We can use apt-get , apt and aptitude . In the following sections we will describe each method. You can choose one of them.

Install odbc-postgresql Using apt-get

Update apt database with apt-get using the following command.

After updating apt database, We can install odbc-postgresql using apt-get by running the following command:

sudo apt-get -y install odbc-postgresql 

Install odbc-postgresql Using apt

Update apt database with apt using the following command.

After updating apt database, We can install odbc-postgresql using apt by running the following command:

sudo apt -y install odbc-postgresql 

Install odbc-postgresql Using aptitude

If you want to follow this method, you might need to install aptitude first since aptitude is usually not installed by default on Ubuntu. Update apt database with aptitude using the following command.

After updating apt database, We can install odbc-postgresql using aptitude by running the following command:

sudo aptitude -y install odbc-postgresql 

How To Uninstall odbc-postgresql on Ubuntu 20.04

To uninstall only the odbc-postgresql package we can use the following command:

sudo apt-get remove odbc-postgresql 

Uninstall odbc-postgresql And Its Dependencies

To uninstall odbc-postgresql and its dependencies that are no longer needed by Ubuntu 20.04, we can use the command below:

sudo apt-get -y autoremove odbc-postgresql 

Remove odbc-postgresql Configurations and Data

To remove odbc-postgresql configuration and data from Ubuntu 20.04 we can use the following command:

sudo apt-get -y purge odbc-postgresql 

Источник

Installing the ODBC drivers for PostgreSQL

This article is part of a series that includes SQLite, MariaDB, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
The postgresql drivers can be installed from the platform’s default package repositories using the following steps as root. As the postgresql service was missing on this test environment, let’s install it too:

A systemd service has been created and started, check it:

# systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Mon 2021-08-23 19:04:20 CEST; 1min 15s ago Main PID: 10528 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 4659) Memory: 0B CPU: 0 CGroup: /system.slice/postgresql.service

# ps -ef | grep postgres postgres 682 1 0 Oct16 ? 00:00:02 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf postgres 807 682 0 Oct16 ? 00:00:00 postgres: 13/main: checkpointer postgres 808 682 0 Oct16 ? 00:00:04 postgres: 13/main: background writer postgres 809 682 0 Oct16 ? 00:00:04 postgres: 13/main: walwriter postgres 810 682 0 Oct16 ? 00:00:01 postgres: 13/main: autovacuum launcher postgres 811 682 0 Oct16 ? 00:00:01 postgres: 13/main: stats collector postgres 812 682 0 Oct16 ? 00:00:00 postgres: 13/main: logical replication launcher

The processes run under the postgres account.
Install the ODBC drivers:

# apt install odbc-postgresql

# odbcinst -q -d . [PostgreSQL ANSI] [PostgreSQL Unicode]

Two drivers have been installed, one for the ANSI character encoding and one for Unicode; check the Unicode one:

# odbcinst -q -d -n 'PostgreSQL Unicode' [PostgreSQL Unicode] Description=PostgreSQL ODBC driver (Unicode version) Driver=psqlodbcw.so Setup=libodbcpsqlS.so Debug=0 CommLog=1 UsageCount=1

Verify that the default native administration tool, psql, is there:

# psql -V # psql (PostgreSQL) 13.3 (Debian 13.3-1)

Switch to the postgres account and create the database:

# su – postgres $ createdb sampledb

Launch psql and create the tables for postgresql using the scripts here:
As those are small files, copying and pasting their content into psql will do just fine.
Also, populate the tables for postgresql using the data here and test:

$ psql sampledb sampledb=# SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN'); Output: country_name | country_id | country_id | street_address | city --------------------------+------------+------------+-------------------------------+----------- United States of America | US | US | 2014 Jabberwocky Rd | Southlake United States of America | US | US | 2011 Interiors Blvd | South San Francisco United States of America | US | US | 2004 Charade Rd | Seattle United Kingdom | UK | UK | 8204 Arthur St | London United Kingdom | UK | UK | Magdalen Centre, The Oxford | Oxford | | | Science Park | China | CN | | | (6 rows)

To test ODBC, first edit ~/.odbc.ini and add the postgresql database details:

$ vi ~/.odbc.ini [mypostgresqldb] Description=My Postgresql sample database Driver=PostgreSQL Unicode Database=sampledb

Verify that the edition was successful:

$ odbcinst -q -s -n mypostgresqldb [mypostgresqldb] Description=My Postgresql sample database Driver=PostgreSQL Unicode Database=sampledb

Next, launch isql against that database:

$ export LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu/odbc:$LD_LIBRARY_PATH $ isql -v mypostgresqldb +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> help +------------------+-------------+-------------+------------+---------+ | TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | REMARKS | +------------------+-------------+-------------+------------+---------+ | sampledb | public | countries | TABLE | | | sampledb | public | departments | TABLE | | | sampledb | public | dependents | TABLE | | | sampledb | public | employees | TABLE | | | sampledb | public | jobs | TABLE | | | sampledb | public | locations | TABLE | | | sampledb | public | regions | TABLE | | +------------------+-------------+-------------+------------+---------+ SQLRowCount returns 7 7 rows fetched SQL> quit

The ODBC connectivity to postgresql is confirmed for the postgres account; this is fine for that user but we want the database to be usable by the debian test account too. To this effect, use the following steps from with psql:

sampledb=# CREATE ROLE debian superuser; CREATE ROLE sampledb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO debian; GRANT sampledb=# ALTER ROLE debian with login; ALTER ROLE sampledb=# q

Back as root, become debian and test the accessibility of the sampledb:

# su - debian $ psql sampledb sampledb=# select count(*) from employees; count ------- 40 (1 row)

$ isql -v mypostgresqldb +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN') +---------------------------+-------------+-----------+-----------------------+----------------------+ | country_name | country_id | country_id| street_address | city | +---------------------------+-------------+-----------+-----------------------+----------------------+ | United States of America | US | US | 2014 Jabberwocky Rd | Southlake | | United States of America | US | US | 2011 Interiors Blvd | South San Francisco | | United States of America | US | US | 2004 Charade Rd | Seattle | | United Kingdom | UK | UK | 8204 Arthur St | London | | China | CN | | | | +---------------------------+-------------+-----------+-----------------------+----------------------+ SQLRowCount returns 6 6 rows fetched

Note that the order of the result set may differ in other data sources; the SQL standard does not define the result set’s order and the ORDER BY clause should be used to enforce one if needed.
Finally, let’s verify the ODBC connectivity through pyodbc:

$ python3 import pyodbc Python 3.9.2 (default, Feb 28 2021, 17:03:44) [GCC 10.2.1 20210110] on linux Type "help", "copyright", "credits" or "license" for more information. import pyodbc # connect directly using the DRIVER definition, no DSN; cnxn = pyodbc.connect('DRIVER=;Direct=True;Database=sampledb;String Types= Unicode') # using the DSN is OK too: # cnxn = pyodbc.connect('DSN=mypostgresqldb') cursor = cnxn.cursor() cursor.execute("""SELECT . c.country_name, . c.country_id, . l.country_id, . l.street_address, . l.city . FROM . countries c . LEFT JOIN locations l ON l.country_id = c.country_id . WHERE . c.country_id IN ('US', 'UK', 'CN')""") row = cursor.fetchone() while row: . print (row) . row = cursor.fetchone() . Output: ('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake') ('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco') ('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle') ('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London') ('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford') ('China', 'CN', None, None, None)

Note that we used the PostgreSQL Unicode driver, not the ANSI one because the latter gives the error below:

pyodbc.Error: ('07006', '[07006] Received an unsupported type from Postgres. (14) (SQLGetData)')

postgreslq databases can now be used locally by any ODBC client application, e.g. any python program with the pyodbc module, or a desktop application such as LibreOffice. In the case of python, many native modules for postgresql are available but they require ad hoc function calls whereas ODBC lets one use the same statements with any database target, which simplifies the maintenance of ODBC applications.
Instructions for the other data sources can be accessed through the following links:
SQLite
HSQLDB
MariaDB
Firebird
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel

Источник