ODBC v Libpq: C library for PostgreSQL

Question

I am going to be using a C library to connect and use a PostgreSQL database, I was wondering what are the pros and cons to ODBC and Libpq. From what I can tell, libpq seems to be faster but I was not able to get any clear answers or benchmarks.

Also, is there any other library that might be better then ODBC/Libpq.

Answer 1

ODBC is generic mostly MS Windows database access interface. Libpq is native PostgreSQL client interface. If you don't need generic interface, don't use ODBC. It is old school unfriendly designed library with high complexity. There is not any advantage against libpq.

Answer 2

ODBC is useful if you want a standard adapter that speaks a similar API for different databases. I personally think it's an awful API, but it's widely understood and well documented.

libpq talks more directly to PostgreSQL. You can get better performance with it, but probably not enough more that it'll make any difference for most apps, which spend time on query execution, network latency, etc, not in the client library.

Newer versions of psqlODBC are built on libpq and serve as an ODBC wrapper for libpq.

There's also libdbi, which offers a less ghastly API than ODBC.

For completeness, there's also the server-backend SPI, which can be used by user-defined functions written in C and loaded into the PostgreSQL server. It's not useful outside server extensions and functions.

Oh, and there's ecpg. Don't use ecpg. It's a super-legacy language-integrated-SQL tool that exists mainly for easier porting from certain other database engines. Don't use ecpg. Really.

For C++ there's the QtSQL interface (unusually for Qt, it's awful and painfully limited, don't use it) and libpq++ (OK but largely unmaintained).

Personally I write libpq code directly, but that's because I'm working on code that usually goes into PostgreSQL its self. If you can't imagine ever wanting to target anything except PostgreSQL you might want to write libpq code; otherwise probably use ODBC with psqlODBC.