CLiki: Pg

Pg is a socket-level interface to the PostgreSQL object-relational Database. Pg implements the client part of the frontend/backend protocol, so does not require interfacing with the libpq library. SQL types are converted to the equivalent Common Lisp types where possible. Supports large objects (BLOBs).

Postmodern is a newer and much-much cleaner library. Its cl-postgres asdf system implements the same functionality as Pg -- Attila Lendvai

The only non portable code is the use of SOCKET-CONNECT. Versions are provided for CMU Common Lisp (CMUCL), SBCL, OpenMCL, CLISP, LispWorks and Allegro Common Lisp (ACL). Corman CL doesn't support binary I/O on socket streams, so it doesn't work there.

Pg is available under the GNU LGPL licence from www.chez.com/emarsden/downloads. There are also Emacs Lisp and scsh versions of the library.

This is a snapshot of the CVS repository at common-lisp.net/project/pg and it is not GPG signed.

Here is the last released version, but it's quite old:

http://www.chez.com/emarsden/downloads/pg-dot-lisp-0.19.tar.gz

Sample code:

(with-pg-connection (conn "testdb" "login" :host "dbhost" :password "secret")
   (with-pg-transaction conn
      (when (member "test_date" (pg-tables conn) :test #'string=)
         (pg-exec conn "DROP TABLE test_date"))
      (pg-exec conn "CREATE TABLE test_date(a timestamp, b abstime, c time, d date)")
      (pg-exec conn "INSERT INTO test_date VALUES "
                    "(current_timestamp, 'now', 'now', 'now')")))

Stargreen Box Office uses Pg for its database glue layer; it's respectably fast, simple enough to see what it's doing, and impervious to PostgreSQL shared library versioning issues (because it doesn't use them). I'm very happy with it -- Daniel Barlow

You must set unix_socket_directory in your postgresql.conf to /var/run/postgresql
pg doesn't use the default path for the unix domain socket. See Socket Path.